Excel 2010 Select IE Dropdown

**Jman12** · 12-14-2012, 09:25 PM

Hey all,

I'm working on a macro that can go to a webpage and login in (got that). But then I need it to select the name of a site on a dropdown box and hit submit. Then I need it to copy an entire table to a cell in my spreadsheet (it will always be 2 columns wide).

Any help would be much appreciated!

Here is the code for the drop down menu:

<form name="form1" action="">
         		    <table class="stdTable" style="width:80%">
                       <tr>
                          <td >&nbsp;&nbsp;&nbsp;&nbsp;Site:</td>
                          <td>
                             <select id="site" class="stdDropDown">
                                <option value="Select Site">Select Site</option>    <<<CHANGE THIS OPTION
                             </select>
                          </td>
                          <td >&nbsp;&nbsp;&nbsp;&nbsp;View:</td>
                          <td>
                             <select id="view" class="stdDropDown">
                                <option value="AD">All Departments</option>
                                <option value="CP">Call Processing Departments</option>
                                <option value="NC">Non Call Processing</option>
                                <option value="LO">Logged Out</option>
								
                             </select>
                          </td>
                          <td >&nbsp;&nbsp;&nbsp;&nbsp;Sort:</td>
                          <td>
                             <select id="sort" class="stdDropDown">
                                <option value="DC">Department Code</option>
                                 <option value="ES">By Employees Ascending</option>
                                <option value="ED">By Employees Descending</option>
                            </select>
                          </td>
						  <td>
                             <input TYPE="button" CLASS="button1" VALUE="SUBMIT" onClick="checkFields2();">&nbsp;&nbsp;   <<<<PRESS THIS BUTTON
						  </td>
 
                       </tr>
 
                    </table>
              </form>

And here is the code for the table that it prints out:

      </div>
      <div id="statusDiv" style="position:center; display:block;">
      </div>
      <div id="displayDiv" style="position:center; display:block;">
      </div>

**Norie** · 12-14-2012, 09:28 PM

Can you post the VBA code?

**Jman12** · 12-14-2012, 09:30 PM

Not much so far, I'm stuck on this point...

Sub PullData()
' Comment out the below line to stop pop-up box!
' MsgBox ("Be patient, the script will run after you click ok.")
' Reference MS Internet Controls

' Declare variables
    Dim IE As InternetExplorer
    Set IE = New InternetExplorer
    
' Navigate to correct page
    With IE
        .Visible = True
        .Navigate "website here"
    End With
' Wait for page to load - allows server to fail
    Application.Wait (Now + TimeValue("00:00:03"))
' Fill in the search form
    With IE
        .Document.form1   <<<stuck here
    End With

    With IE
        '.Quit
    End With
    
    Set IE = Nothing
End Sub

EDIT:
Might be important to mention that the table output one the button is pressed will always be a different size

**Norie** · 12-14-2012, 09:33 PM

Kind of hard to help without a valid URL.

A couple of things about the code though.

You only need one With.

There is no form1, but document does have a forms collection and you can refer to the first form using forms(0).

**Jman12** · 12-14-2012, 09:35 PM

Originally Posted by Norie

Kind of hard to help without a valid URL.

I would but it is a company website, so its confidential blah blah blah

How do I refer to the first form and then alter the value of the first option? Sorry I don't know much about forms at all

**Norie** · 12-14-2012, 09:49 PM

I already suggested how to refer to the first form.

To refer to the dropdown you could use the form's elements collection.

Perhaps something like this.

Dim doc As Object
Dim frm As Object
Dim cbo As Object

' code to navigate to page

      Set doc = IE.document
 
      Set frm = doc.forms(0)

      Set cbo = frm.Items("site")

      cbo.selectedIndex = 1 ' select 2nd item in dropdown

      frm.Submit

That code is not guaranteed to work at all - the cbo lines I'm a bit unsure about.

**Jman12** · 12-14-2012, 10:11 PM

Originally Posted by Norie

That code is not guaranteed to work at all - the cbo lines I'm a bit unsure about.

So far your always right. Code works great until you reach the line

Set cbo = frm.Items("site")

Anyone know how to reference the portion of the frame?

**Norie** · 12-14-2012, 10:16 PM

What do you mean the 'portion of the frame'?

**Jman12** · 12-14-2012, 10:22 PM

Sorry, don't know enough to not even sound stupid >.>

I mean how to change that particular combobox. frm.Items("site") came out with an error:

"Run-time error '438': Object doesn't support this property or method"

**Norie** · 12-14-2012, 10:36 PM

That was the one line I was pretty sure wasn't right.

You can try this instead,

Set cbo = frm("site")

or this

Set cbo = frm.all("site")

Not 100% sure about those either.

**Jman12** · 12-14-2012, 10:54 PM

Thanks so much for your help so far!!

This works up until submitting now, it just refreshes the page?

      Set doc = IE.Document
      Set frm = doc.forms(0)
      Set cbo = frm("site")
      'select 45th item in dropdown
      cbo.selectedIndex = 44
      frm.submit   <<< refreshes page?

**Norie** · 12-14-2012, 10:59 PM

Submit doesn't always work.

Try clicking the button.

set btn = frm.getElementsByClassName("button1")(0)

btn.click

**Jman12** · 12-14-2012, 11:04 PM

Set btn = frm.getElementsByClassName("button1")(0)

That now pops the same 438 error. I made sure to Dim As Object. Any other suggestions for me?

**Norie** · 12-14-2012, 11:19 PM

This is the sort of thing made harder by not having the URL.

Here's something else to try but I'm not too confident about it, as it's similar to getElementsByClassName which should work.


Set btn = frm.getElementsByTagName("INPUT")(0)

btn.click

PS I've just created a small HTML file with the code I posted.

When I run the code I posted in post #12 I get no errors.

**Jman12** · 12-17-2012, 02:52 PM

First time back since the weekend. Thank you so much!!! It works great now, absolutely perfect so far! One last step...

Need it to copy the table that results, which is encoded as:

      </div>
      <div id="statusDiv" style="position:center; display:block;">
      </div>
      <div id="displayDiv" style="position:center; display:block;">
      </div>

Any wonderful tips on that Norie?

PS. IDK how to say that you enough, even this part so far saves me like 30m a day so far.

**Jman12** · 12-20-2012, 09:41 PM

Tried very hard to get any farther. The best I can do is this:

Sub PullData()
' Reference MS Internet Controls

' Declare 
    Dim IE As InternetExplorer
    Set IE = New InternetExplorer
    Dim doc As Object
    Dim frm As Object
    Dim cbo As Object
    Dim btn As Object
    Dim div1 As Object
    Dim div2 As Object
    
' Navigate to correct page
    With IE
        .Visible = True
        .Navigate "validurl"

' Wait for page to load
        ' Below is better version of wait script, but errors out for no reason...
        ' Do While IE.ReadyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
        While IE.ReadyState <> READYSTATE_COMPLETE
            Application.Wait (Now + TimeValue("00:00:01"))
        Wend
        
' Fill in the search form
        Set doc = IE.Document
        Set frm = doc.forms(0)
        Set cbo = frm("site")
        Set btn = frm.getElementsByTagName("INPUT")(0)
        Set div1 = IE.Document.getElementById("statusDiv")
        Set div2 = IE.Document.getElementById("displayDiv")
        'select 45th item in dropdown
        cbo.selectedIndex = 44
        btn.click
        ' copy the resulting table
        While IE.ReadyState <> READYSTATE_COMPLETE
            Application.Wait (Now + TimeValue("00:00:01"))
        Wend
        'div1.Copy
        Sheets(3).Select
        Range("CA2").End(xlToLeft).Offset(0, 1).Value = div1
        'MsgBox (div1)
        
      
    End With
    Set IE = Nothing
End Sub

So far all this gets me is a Value of "[object]", and I don't know how to make it anything else. I need it to copy/paste the entire 2 tables that result from this page. One is called "statusDiv", the other is "displayDiv"

**Norie** · 12-20-2012, 09:47 PM

Is this all the code there is?

      </div>
      <div id="statusDiv" style="position:center; display:block;">
      </div>
      <div id="displayDiv" style="position:center; display:block;">
      </div>

DIV elements are used as containers so I would have expected there to be code for what's contain within the DIVs.

**Jman12** · 12-20-2012, 09:49 PM

Yes this is it

I'll paste nearly the entire source code:

<!--<body class="bodyBG" id="Body1" style="text-align:center;" -->
    <script type="text/javascript" src="../jscripts/tooltip.js"></script>
    <script type="text/javascript" src="../jscripts/tipcntr.js"></script>
    
    <div ID="HeadSection" style="width:80%; text-align:center;">
    <h1 class="mainTitle">Department Summary Page</h1>
    </div> 
 
    <div id="linkDiv" style="width:80%; text-align:center;">
        <I><a href="../htm/lmgttool.html">Labor Management Toolkit</a></I>
        <br><br>
    </div>
 
      <div style="text-align:center;">
            <form name="form1" action="">
         		    <table class="stdTable" style="width:80%">
                       <tr>
                          <td >&nbsp;&nbsp;&nbsp;&nbsp;Site:</td>
                          <td>
                             <select id="site" class="stdDropDown">
                                <option value="Select Site">Select Site</option>
                             </select>
                          </td>
                          <td >&nbsp;&nbsp;&nbsp;&nbsp;View:</td>
                          <td>
                             <select id="view" class="stdDropDown">
                                <option value="AD">All Departments</option>
                                <option value="CP">Call Processing Departments</option>
                                <option value="NC">Non Call Processing</option>
                                <option value="LO">Logged Out</option>
								
                             </select>
                          </td>
                          <td >&nbsp;&nbsp;&nbsp;&nbsp;Sort:</td>
                          <td>
                             <select id="sort" class="stdDropDown">
                                <option value="DC">Department Code</option>
                                 <option value="ES">By Employees Ascending</option>
                                <option value="ED">By Employees Descending</option>
                            </select>
                          </td>
						  <td>
                             <input TYPE="button" CLASS="button1" VALUE="SUBMIT" onClick="checkFields2();">&nbsp;&nbsp;
						  </td>
 
                       </tr>
 
                    </table>
              </form>
                    <br><br>
		 </div>
         <div style="text-align:center; color:gray; font-size: 75%;">
     	 <span style="font-weight:bold;">Attention!</span>  The information contained on this page is not connected with payroll.
		 All payroll information will come from the payroll department only.
             <br><br>
      </div>
      <div id="statusDiv" style="position:center; display:block;">
      </div>
      <div id="displayDiv" style="position:center; display:block;">
      </div>
 
   </body>

PS. This is after I select the site and have all the data I want copied visible.

**Norie** · 12-20-2012, 10:05 PM

How are you viewing/copying the source code?

**Jman12** · 12-20-2012, 10:26 PM

I was just right clicking and viewing source... But going into Developer Tools in IE I get more data... sorry about that....

Here's as much data as I can pull:

<div id="displayDiv" style="display: block;" 
<DIV style="DISPLAY: block" id=displayDiv><TABLE style="WIDTH: 30%" cellSpacing=0>
<THEAD>
<TR class=headerRow>
<TD id=headcent vAlign=top width="15%">Department</TD>                            <<<<<<<<NEED THIS ENTIRE COLUMN COPIED 
<TD id=headcent vAlign=top width="15%">Employees</TD></TR></THEAD>                <<<<<<<<THEN THIS ENTIRE COLUMN AFTER THAT
<TBODY>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('0129');" href="VALIDURL"><FONT color=blue>0129</FONT></A></CENTER></TD>
<TD>
<CENTER>28</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('0130');" href="VALIDURL"><FONT color=blue>0130</FONT></A></CENTER></TD>
<TD>
<CENTER>16</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('0134');" href="VALIDURL"><FONT color=blue>0134</FONT></A></CENTER></TD>
<TD>
<CENTER>25</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('1222');" href="VALIDURL"><FONT color=blue>1222</FONT></A></CENTER></TD>
<TD>
<CENTER>3</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('1272');" href="VALIDURL"><FONT color=blue>1272</FONT></A></CENTER></TD>
<TD>
<CENTER>2</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('1639');" href="VALIDURL"><FONT color=blue>1639</FONT></A></CENTER></TD>
<TD>
<CENTER>6</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('2557');" href="VALIDURL"><FONT color=blue>2557</FONT></A></CENTER></TD>
<TD>
<CENTER>1</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('2900');" href="VALIDURL"><FONT color=blue>2900</FONT></A></CENTER></TD>
<TD>
<CENTER>4</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('3900');" href="VALIDURL"><FONT color=blue>3900</FONT></A></CENTER></TD>
<TD>
<CENTER>3</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('3901');" href="VALIDURL"><FONT color=blue>3901</FONT></A></CENTER></TD>
<TD>
<CENTER>1</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('3930');" href="VALIDURL"><FONT color=blue>3930</FONT></A></CENTER></TD>
<TD>
<CENTER>9</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('6710');" href="VALIDURL"><FONT color=blue>6710</FONT></A></CENTER></TD>
<TD>
<CENTER>1</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('7220');" href="VALIDURL"><FONT color=blue>7220</FONT></A></CENTER></TD>
<TD>
<CENTER>7</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('7223');" href="VALIDURL"><FONT color=blue>7223</FONT></A></CENTER></TD>
<TD>
<CENTER>2</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('9995');" href="VALIDURL"><FONT color=blue>Restroom</FONT></A></CENTER></TD>
<TD>
<CENTER>4</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('9997');" href="VALIDURL"><FONT color=blue>Lunch</FONT></A></CENTER></TD>
<TD>
<CENTER>13</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('9998');" href="VALIDURL"><FONT color=blue>Break</FONT></A></CENTER></TD>
<TD>
<CENTER>9</CENTER></TD></TR>
<TR class=bodyRows>
<TD>
<CENTER><A onclick="getDeptDetail('9999');" href="VALIDURL"><FONT color=blue>LoggedOut</FONT></A></CENTER></TD>
<TD>
<CENTER>1750</CENTER></TD></TR></TBODY></TABLE><BR></DIV>

Also copying the rows instead of columns would work fine, columns would just be easier for me. But either way ^-^

**Norie** · 12-20-2012, 10:49 PM

It's probably easier to get the entire table rather than specific columns.

I've got some code for that, I'll try and post it tomorrow.

**Jman12** · 12-20-2012, 10:49 PM

Thank you so much!

**Jman12** · 12-27-2012, 07:00 PM

Originally Posted by Norie

It's probably easier to get the entire table rather than specific columns.

I've got some code for that, I'll try and post it tomorrow.

Don't mean to bug you Norie I thank you for your help so far, was just wondering if youve had time to dig up this code in between the holidays? TY!

Excel 2010 Select IE Dropdown

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Re: Excel 2010 Select IE Dropdown

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions