Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Using Vba Code To Extract Web Data

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello everyone. On a monthly basis I have a procedure to extract data from a website and place that data into an excel worksheet. it does not take long to do, as there is a "button" on the website that you can press to "dump-to-excel". However I would like to use VBA code to inlcude this procedure into some macros I have already developed. The only "variable" in the procudeure involves the user inputing dates in the following format: dd-three letter month abreviation-YYYY. The web data is contained in a nice table in the center of the site. The number or rows varies, but the number of columns is fixed at 5. The intranet site uses IE. The following code sort of works, but as you can tell it does not prompt the user to enter a date. The code below is a good start, I think.

Code:

Sub WB()

URL = "http://cds.worldbank.org/Pages/CurrMain.aspx?rate_date=31-mar-2010"

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


'get web page
IE.navigate2 URL & Ticker
Do While IE.readystate <> 4 Or _
IE.busy = True

DoEvents
Loop

Do While IE.document Is Nothing
DoEvents
Loop


End Sub


I would like to save the table into a worksheet called CDS_Raw.xlsx. Any suggestions would be greatly appreciated. Note that the code above does bring the website open.

I am assuing I should declare a variable say fdate

Code:

Dim fdate As String.


and use that somewhere in the URL?

View Answers     

Similar Excel Tutorials

Get the Name of a Worksheet in Macros VBA in Excel
How to get the name of a worksheet in Excel using VBA and Macros and also how to store that name in a variable for ...
Pause a Macro or Make it Slow Down in Excel
How to make a macro stop or pause for a specified amount of time. This allows you to slow down the execution of a m ...
Close Excel Workbook using VBA Macros
How to close an Excel workbook using VBA and macros, including how to save the file before you close it or discard ...
Run a Macro from Another Macro in Excel
I will show you how to run a macro from another macro in Excel.  This means that you can run any macro when you ne ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

Similar Topics







I am trying to use the SendKey method from Excel on a website to start a Search. I am using the SenKey opetion because I can't find the Name of the control in the Website Source Code. When I run the macro with the SendKey {ENTER} nothing happens, below is the code:

Code:

Dim ie As Object

Set ie = CreateObject("InternetExplorer.Application")

Sheets("Worklist").Select

ie.Visible = True

ie.Navigate "https://provider2.anthem.com/wps/myportal/ebpmybcbsco"
Do While ie.busy: DoEvents: Loop
Do While ie.ReadyState <> 4: DoEvents: Loop

ie.document.all.Item("wps.portlets.userid").Value = USERNAME
ie.document.all.Item("Password").Value = PASSWORD
ie.document.all.Item("login").Click
Do While ie.busy: DoEvents: Loop
Do While ie.ReadyState <> 4: DoEvents: Loop

With Sheets("Worklist")

Dim lLastRow As Long, i As Long, j As Integer, k As Integer, l As Integer

lLastRow = [a65536].End(3).Row

For i = Range("Start_Row") To lLastRow
    
'ie.navigate https://provider2.anthem.com/wps/myportal/ebpmybcbsco/kcxml/04_Sj9SPykssy0xPLMnMz0vM0Y_QjzKLNzGJd7FwBslB2Kah-pFYREOQRH098nNT9YOAspHmIDEjS2cP_aic1PTE5Er9YH1v_QD9gtzQiHJvR0cAPKaA4A!!/delta/base64xml/L0lJSk03dWlDU1lKSi9vQXd3QUFNWWdBQ0VJUWhDRUVJaEZLQSEvNEZHZ2RZbktKMEZSb1hmckNIZGgvN180NF8yOUNILzE5MjczOTUvc2EuRUJQLkJDQlNDTy5NZWRpY2FsTG9hZFNlYXJjaE1lbWJlckNsYWltQWN0aW9u?PC_7_44_29CH_breadCrumbIndex=1&PC_7_44_29CH_originPage=MedicalLoadSearchMemberClaimAction#7_44_29CH

'Do While ie.busy: DoEvents: Loop
'Do While ie.ReadyState <> 4: DoEvents: Loop


    mbrlast = .Cells(i, "E").Value
    mbrfirst = .Cells(i, "F").Value
    mbrname = mbrlast & "," & mbrfirst
    mbrdob = .Cells(i, "H").Value
    mbrdob = Format(mbrdob, "mm/dd/yyyy")
    mbrdosbegin = .Cells(i, "N").Value
    mbrdosbegin = Format(mbrdosbegin, "mm/dd/yyyy")
    mbrdosend = .Cells(i, "N").Value                        'serviceFromDateText
    mbrdosend = Format(mbrdosend, "mm/dd/yyyy")
    mbrid = .Cells(i, "G").Value
    npi = "1952373953"
    
    
    Set frm = ie.document.forms("eligibilityForm")
    ie.document.all.Item("memberId")(0).Value = mbrid
    ie.document.all.Item("birthDate")(0).Value = mbrdob
   
    
     

   
   SendKeys "{ENTER}"
     
     
  
     
     
    Do While ie.busy: DoEvents: Loop
    Do While ie.ReadyState <> 4: DoEvents: Loop
    Sleep 5000
    
    Stop





Hi

I have the below code which opens a web page, enter log in details, clicks a button 'Export to Excel'. Till here the codes works fine, now I want just a line to save the file in C drive.
Code:

Sub Web_Top_Login1()

Dim IE As Object
'Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
'(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
'Dim sFName

Set myIE = CreateObject("InternetExplorer.Application")
 myIE.Visible = True
   With myIE
.Navigate "http://test.com" ' Actual site name is changed due to security reason

    Do While .Busy: DoEvents: Loop
    Do While .readyState <> 4: DoEvents: Loop
    .Visible = True    
    With .Document.forms("ReportsSearch_0")
    .date_date.Value = "11-May-2011"
    .ReportsSearch_Button_0.Click   
   Do While myIE.Busy: DoEvents: Loop
    'Click 'Export to Excel
    myIE.Document.forms("ReportsSearch_0").ReportsSearch_Button_4.Click
   
   End With
End With
End Sub


the above codes works fine for me. but after the click, i get a dialogue box saying
"File Download - Security Warning "Do you want to save this file, or find a program only to open it?"

I need a code to click save and the file has to be saved in C:/MyDocuments folder

Can you guys help me...


Good Morning

I am trying to automatically log on to a website site using a login and password.

I have created a sample code to log on to the website and enter the relevant username and password but I can't get the submit button to work.

Any ideas on where I am going wrong

Sub GetTable()

Dim ieApp As InternetExplorer
Dim ieDoc As Object

Set ieApp = New InternetExplorer

ieApp.Visible = True

ieApp.Navigate "http://fantasyfootball.dailymail.co.uk/log-in"
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Set ieDoc = ieApp.Document

With ieDoc.forms(0)
.Email.Value = "n.collins@tauntondeane.gov.uk"
.pass.Value = "arsenal"
.ieDoc.submit
End With
End Sub


I found this code on a site but was unable to identify the programmer. I need to fix one problem I have with the code. When I log into my specific site, after entering the login and password I get a window that pops asking me if I would like to proceed. How do I select the yes button on the pop up?


Code:

Sub LoginToURL() 
    Dim ie As Object, strURL As String, strUsername As String, strPassword As String 
     
    Set ie = CreateObject("InternetExplorer.Application") 
     
    strURL = "http://www.skytronic.co.uk/product/index.php?s=128.685" 
    strUsername = "guest" 
    strPassword = "password" 
     
    ie.Navigate strURL 
     'Wait until page is loaded.
    While ie.ReadyState < 4 ' READYSTATE_COMPLETE = 4
        DoEvents 
    Wend 
     
    ie.Visible = True 
     
    ie.Document.All("username").Value = strUsername 
    ie.Document.All("password").Value = strPassword 
    ie.Document.All("submit").Click 
     
     'Wait until page is loaded.
    While ie.ReadyState < 4 ' READYSTATE_COMPLETE = 4
        DoEvents 
    Wend 
     
    Application.Wait (Now + TimeValue("0:00:05")) 
     
    urldownloadtofile http://www.skytronic.co.uk/product/index.php?s=128.685.txt 
     
End Sub





I currently use this macro to open the specific website and login (which I got from this forum and it works perfectly!! - as long as you enable/Reference Microsoft XML v4.0)

Code:

Sub WebpageLogin()
     
    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")
    ie.navigate "https://webpage.com/Login"
    ie.Visible = True
     
    Do While ie.Busy And Not ie.readyState = 4
        DoEvents
    Loop
    DoEvents
     
    ie.document.all.Item("txtUserName").Value = "username"
    ie.document.all.Item("txtPassword").Value = "password"
    ie.document.all.Item("btnLogin").Click
     
End Sub


However...I would like to write another macro to extract information from this already opened webpage.
I can't "CreateObject" again, because I'd have to login all over again...

How could I start a macro, to reference the already opened webpage?

Once I have logged in...the webpage is a 'search' screen. I have to search different 'cases' for specific bits of information.
So would I continue to use ie.document.all.Item("txtUserName").Value to fill in different fields, like the case number?

Once the 'case' screen is opened... what Code would I use to actually Extract information from the different fields?

Can anyone help?

Thanks!
M


Hi all.
I am trying to control a website from Excel VBA. It has page source below:

<FORM class=chooser_form id=chooser_form name=chooser_form
onsubmit="return false;" action=http://www.example.com/ method=post>
<INPUT type=hidden value=en name=lang>
<DIV class=entry_url_out>
<INPUT class=entry_url name=URL>
</DIV>
<DIV class=selector_site_2_out>
<SELECT class=selector_site_2 onchange=change_info(document.chooser_form.site.selectedIndex); name=site>
<OPTION value=Option1>Option1</OPTION>
<OPTION value=Option2>Option2</OPTION>
</SELECT>
</DIV>
<INPUT class=entry_submit onfocus=this.blur(); onclick=buttonDownload() type=submit>
</FORM>


That website change a text into another text. Normaly under IE, we must type on the input box in that website, and click the button. Afterwards, that web show the result.

By this code below, I have succeed to enter a text in to the input box, but I can't "click" in the button because that button didnt have a name. Here is my code

Private Sub OpenWebPageAndInput_Click()
Dim objIE As Object
On Error GoTo error_handler
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.Navigate "www.example.htm"
Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop
.Visible = True
With .Document.Forms("chooser_form")
.URL.Value = "test"
.submit '< this line was error

End With
End With
Set objIE = Nothing
Exit Sub
error_handler:
MsgBox ("Unexpected Error, I'm quitting.")
objIE.Quit
Set objIE = Nothing
End Sub


I have tried SendKeys "{ENTER}", True but it was fail too
I guess, that website use a Javascript or Ajax to send the input data to function "buttonDownload". Please help.


hi i have written a code where it opens a website and enter in by giving a
username and password. Then there is a serach box. I need to enter some data
into that search box which i have done the problem is now i am unable to
submit it. And once it enter into the next page it should click on a
particular link and should open that link from which i take the necessary
data. Please some one help me on this.
Private Sub CommandButton1_Click()
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.navigate "http://www.moodys.com/cust/default.asp"
.FullScreen = False
Do Until .readyState = 4: DoEvents: Loop
Do While .busy: DoEvents: Loop
Set mytextfield = .document.all.Item("userid")
mytextfield.Value = "amitavah"
Set mytextfield1 = .document.all.Item("passwd")
mytextfield1.Value = "amitava"
ie.document.forms("form1").submit
Set mytextfield2 = .document.all.Item("searchQuery")
mytextfield2.Value = "Aquila Inc"
ie.document.forms(0).submit
End With
End Sub




Afternoon All,
I have spent hours trying different ways to get this to work and I just can't figure it out.

I currently have a macro that opens up Internet Explorer to a webpage that I need info from. I then need it to click the submit button at the bottom to run the report. This is an internal website that houses volume data. I can not link straight to the webpage I need. When I try it says for security reasons you can not go straight to the pages you need to access through the website. So i have gotten it to open up the webpage I need all I need now is for the submit button to be pressed to load the report.

Here is my current code:

Code:

Sub Websearching()

Dim ie As Object


Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")
ie.NAVIGATE "http://fraud.intra.aexp.com/trpts/trpt3.asp"
ie.Visible = True


While ie.busy
DoEvents
Wend

End Sub


Here is the HTML Code for the button on the website:


Code:

 <td> 
          <div align="center"><font color="#008080"> 
            <input type="submit" value="Run Report" class="SubmitButton"  id="submit1" name="submit1">
            </font></div>
        </td>





Hi there,

Im trying to get data from a Sharepoint list to autodownload into excel, so far with my script I can access the website, login, navigate to the page with the datagrid list... and then im stuck.. I have no idea how to copy the data from the list and bring it back to paste in excel.

The code below is from the source file of the page im trying to get the data from.. I have no idea if it will assist anyone!

form name="aspnetForm" method="post" action="ExcelExportView.aspx" onsubmit="javascript:return WebForm_OnSubmit();" id="aspnetForm"

if you need any more info let me know!

This is my code so far

Code:

Private Sub CommandButton1_Click()
If TextBox1.Value = "" Or TextBox3.Value = "" Then Exit Sub
 
UserForm1.Hide
Dim EXP
Set EXP = CreateObject("InternetExplorer.application")
EXP.Visible = True
'put the webpage here
EXP.Navigate ("http://mysharepointsite.com")
   Do While EXP.Busy: DoEvents: Loop
   Do While EXP.ReadyState  4: DoEvents: Loop
   With EXP.Document.Forms("logonForm")
   .UserName.Value = TextBox1.Text
    .Password.Value = TextBox3.Text
    .Submit
  End With
    Do While EXP.Busy: DoEvents: Loop
    Do While EXP.ReadyState  4: DoEvents: Loop
EXP.Navigate ("http://mysharepointsite.com/dataviewpage")
    Do While EXP.Busy: DoEvents: Loop
    Do While EXP.ReadyState  4: DoEvents: Loop
    With EXP.Document.Forms("aspnetForm")
''''''Im guessing the above line is correct and from here Im stuck''''''''
 
    End With
 
End Sub





This particular thread is continued from my previous post that I am bumping because no one has fixed it yet . This is by far the toughest Exel issue I have ever ran across so far.

The below code represents my efforts on trying to take a website and fire an onchange event that has a postback procedure within the table I am trying to access. It seems like I cannot get it to trigger. I used the below code on this website and it worked:

http://www.dotnetmonster.com/Uwe/Thr...he-back-button

But it will not work on mine. Any suggestions for firing this event properly with the table and onchange etc.?


Option Explicit

Sub FVP()
Dim appIE As Object
Dim doc As Object
Dim frmNav As Object
Dim ddTool As Object
Dim btnSubmit As Object

Set appIE = CreateObject("INTERNETEXPLORER.APPLICATION")

appIE.Visible = True

appIE.navigate "Company Website"

Do While appIE.Busy: DoEvents: Loop

Do While appIE.ReadyState 4: DoEvents: Loop

Set doc = appIE.document

Do While doc.ReadyState "complete": DoEvents: Loop

Set ddTool = doc.getelementbyid("ELID") ' this is where I put the drop down id for the table onchange event

Do While doc.ReadyState "complete": DoEvents: Loop

ddTool.selectedindex = 1 'This is where the Code Breaks

Do While doc.ReadyState "complete": DoEvents: Loop


ddTool.onchange
Do While doc.ReadyState "complete": DoEvents: Loop
Set appIE = Nothing

End Sub


Hi all, could somebody help me. I am trying to control a website from
Excel VBA. It has page source below:

<FORM class=chooser_form id=chooser_form name=chooser_form
onsubmit="return false;" action=http://www.example.com/blog.php
method=post>
<INPUT type=hidden value=en name=lang>
<DIV class=entry_url_out>
<INPUT class=entry_url name=URL>
</DIV>
<DIV class=selector_site_2_out>
<SELECT class=selector_site_2
onchange=change_info(document.chooser_form.site.selectedIndex);
name=site>
<OPTION value=Option1>Option1</OPTION>
<OPTION value=Option2>Option2</OPTION>
</SELECT>
</DIV>
<INPUT class=entry_submit onfocus=this.blur(); onclick=buttonDownload()
type=submit>
</FORM>

That website change a text into another text. Normaly under IE, we must
type on the input box in that website, and click the button.
Afterwards, that web show the result.
I guess that website use Java or Ajax programming when we clicked the
button to send XMLHTTP and send request to web server.

By this code below, I have succeed to enter a text in to the input box,
but I can't "click" in the button because that button didnt have a
name. Here is my code

Private Sub OpenWebPageAndInput_Click()
Dim objIE As Object
On Error GoTo error_handler
Set objIE = CreateObject("InternetExplorer.Application")
With objIE
.Navigate "www.example.com/example.php"
Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop
.Visible = True
With .Document.Forms("chooser_form")
.URL.Value = "test"
.submit '< this line was error

End With
End With
Set objIE = Nothing
Exit Sub
error_handler:
MsgBox ("Unexpected Error, I'm quitting.")
objIE.Quit
Set objIE = Nothing
End Sub


I have tried SendKeys "{ENTER}", True but it was fail too




HTML Code:

<!--<input type="submit" id="sbu_btn" class="sub_btn" value="&nbsp;&nbsp;&nbsp;Login&nbsp;&nbsp;&nbsp;" class="sub_btn">-->
                                <input type="image" src="images/login.gif">


and
Code:

 
                                



Code:

Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True
.navigate "http://www.thesite.com/login.php"
Do Until .readystate = 4: DoEvents: Loop
Do While ie.busy: DoEvents: Loop
Set fa1 = .document.getelementbyid("user")
fa1.Value = "usern" 'usern
Set ea = .document.getelementbyid("pass")
ea.Value = "pass" 'pwd
ie.document.getelementbyid("sbu_btn").Click
Do Until .readystate = 4: DoEvents: Loop
Do While ie.busy: DoEvents: Loop


ok, in the first code snippet you can see where the input type 'submit' was changed to input type 'image'.

before the change i could log in using the second bit of code.

what code is there to 'click' on the image?


Hi,

I'm looking for a way to use VBA to uncheck a check-box on a website (it is permanently checked and I want to uncheck it).

Here's what I have Code:

Sub Facebook_Login_Terry()
    Set ie = CreateObject("InternetExplorer.application")
    ie.Visible = True
    ie.Navigate ("http://www.facebook.com")
    Do
        If ie.ReadyState = 4 Then
            ie.Visible = True
            Exit Do
        Else
            DoEvents
        End If
    Loop
    ie.Document.Forms(0).all("email").Value = "Email address here"
    ie.Document.Forms(0).all("Pass").Value = "password"
    ie.Document.Forms(0).submit
    

End Sub


and here's the HTML Ccode
Code:

<tr>
<td>
<input name="email" tabIndex="1" class="inputtext" id="email" type="text"/>
<td>
<input name="pass" tabIndex="2" class="inputtext" id="pass" type="password"/>
<td>
<label class="uiButton uiButtonConfirm" for="u168241_3">
<input tabIndex="4" id="u168241_3" type="submit" value="Log in"/>
<tr>
<td class="login_form_label_field">
<input name="persistent" class="inputcheckbox" id="persistent" type="checkbox" CHECKED="checked" value="1"/>
<input name="default_persistent" type="hidden" value="1"/>
<label id="label_persistent" for="persistent">
Text - Keep me logged in


What do I need to insert into the VBA code to ensure the check box "persistent" is unchecked.

Thanks

Terry


Code:

        Dim IE As Object
        Set IE = CreateObject("InternetExplorer.Application")
        IE.Visible = True
        IE.Navigate "http://www.custwebsite.com"
        Do
                If IE.ReadyState = 4 Then
                            IE.Visible = False
                            Exit Do
                    Else
                            DoEvents
                End If
        Loop
        IE.Visible = True
        SendKeys "username", True
        Application.Wait (Now + TimeValue("0:00:02"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "password", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{ENTER}", True
        Application.Wait (Now + TimeValue("0:00:04"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{ENTER}", True                                ' admin functiond
        Application.Wait (Now + TimeValue("0:00:04"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys textfile.txt                                         ' insert upload file name
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{END}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{TAB}", True
        Application.Wait (Now + TimeValue("0:00:01"))
        SendKeys "{ENTER}", True


I use this code to start IE, navigate to a webpage, insert username and password, insert a file name to upload, then TAB to the UPLOAD button and sendkey an ENTER. Most of the waits are so I can watch the process. Everything works fine till I sendkey the ENTER on the UPLOAD button. The first thing that happens is I start getting the slow green progress bars and an eventual failure. Rather than waiting for the failure I use the IE STOP button. Then, if I press enter or click on the UPLOAD button the upload function works perfectly. (using XP and Excel 2007) Any suggestions? Thanks.


Hi Forum

I have run into a little problem. Every week i must download data from a web site to be feed into various dashboards. I am using the below code with google as an example but when i try to use the sendkeys command to tab down the website nothing happens.

Code:

Dim ie As InternetExplorer
    Dim MyStr As String
    Set ie = New InternetExplorer
    ie.Navigate "http://google.com"
    ie.Visible = True
    SendKeys "%{tab}"
    SendKeys "%{tab}"
    SendKeys "%{tab}"
    SendKeys "%{tab}"
    SendKeys "%{tab}"
    SendKeys "%{tab}"
    Application.SendKeys "%{Enter}"


can anyone help?

Thanks


I am looking for some direction on using VBA in Excel to control a website that is using Javascript.

What I would like to do is log into a website that uses Javascript, click a few links, and capture the site contents. I can easily do this now with a website using HTML by logging into a form and clicking a button, etc.

Since the site is company confidential, the URL is not accessible, however for the purpose of some guidance and direction, lets use the following site as an example:

http://www.utopialv.com/utopia.html

Using VBA, (i.e. .Nagivate "http://www.utopialv.com/utopia.html"), how can I make it click any of the other links the site (such as 'Welcome home 2006')? If there were Username and Password boxes, any ideas on how I can identify the box names like in a HTML form and enter the information into those boxes using VBA?

Also, can a Javascript site be captured like an HTML site into a variable? For example, the code below navigates to anywho.com and looks some information, then can capture the site's contents into a variable 'doug' (please note that this is just a snippet of the entire code I'm using):

Code:

Dim EXP
Dim lnk
    Set EXP = CreateObject("InternetExplorer.application")
    EXP.Visible = False
    'http://www.anywho.com/qry/wp_fap
    EXP.Navigate ("http://www.anywho.com/wp.html")
    Do While EXP.Busy: DoEvents: Loop
    Do While EXP.ReadyState  4: DoEvents: Loop
    With EXP.Document.forms("fap_res")
.lastname.Value = last_name(a)
.firstname.Value = first_name(a)
If state(a)  "" Then .state.Value = state(a)
If city(a)  "" Then .city.Value = city(a)
If city(a) = "" And zipcode(a)  "" Then .zip.Value = zipcode(a)
11  .submit
    End With
    Do While EXP.Busy: DoEvents: Loop
    Do While EXP.ReadyState  4: DoEvents: Loop
30 Set strDoc = EXP.Document
    bln = True
    If bln Then  'As Text
        doug = strDoc.body.innerText
    Else    'As Html
        doug = strDoc.body.innerHTML
    End If


Any and all help is greatly appreciated - thank you in advance and for your reading.

Doug


Hi i am a beginner of EXcel and VBA coding. Iam trying to access a site which
takes input from the user and displays the data. I have written the following
code but not able to pull the required data into excel. Can anyone please
help me on this.
Private Sub CommandButton2_Click()
Dim ie As Object
Dim objBK As Workbook
Set objBK = Workbooks.Add
With objBK.Worksheets(1)
Set ie = CreateObject("InternetExplorer.Application")

On Error GoTo errHandler
With ie
.navigate "http://data.bls.gov/cgi-bin/srgate"
Do While .busy: DoEvents: Loop
Do While .ReadyState <> 4: DoEvents: Loop
With .document.forms(0)
.series_id.Value = " ECU11121I"
.Submit

End With
End With

End With

errHandler:
ie.Quit: Set ie = Nothing

End Sub



Hi all

I have the following code to click a button on a website.

Code:

Public Sub Lehman()

Dim ie As Object

Set ie = CreateObject("internetexplorer.application")

ie.Visible = True

ie.navigate "mywebsite"

While ie.busy

Wend

ie.document.all("/LL/dispatcher?show_nav=Y").Click  '<----- Error 91 Object variable or with block var not set

End Sub


The website opens correctly, but I get an error on the line set above. The website is mostly text and has a button to proceed. Here is the web code that references the button:

Code:

<p>&nbsp;</p>
<p>&nbsp;</p>
<h1>Click below to acknowledge
<br/>
<br/>
<a href="/LL/dispatcher?show_nav=Y"><img src="/LL_S/imagelibrary/buttons/proceed_orange.gif" alt="proceed" /></a></h1>
</div>


</body>
</html>


A couple questions:
1) Do I need to have any particular references checked to be able to navigate websites in Excel VBA?
2) I can't really read web code yet, what should that line be to click the button to proceed?

Thanks!


Hi to all,

I have the following problem: I have found some code on the web regarding the use of automating IE using VBA. However, as far as I understand the code has been written for a Class Module (I think). The code is below
(I have left the Class name as Class1)

Option Explicit
Public WithEvents ie1 As InternetExplorer
Public ie2 As InternetExplorer
Private Declare Sub Sleep Lib "kernel32" ( _
ByVal dwMilliseconds As Long)
Private Sub Command1_Click()
Set ie1 = CreateObject("InternetExplorer.Application")
ie1.Visible = True
ie1.Navigate2 "http://www.yahoo.com"
While ie1.ReadyState < READYSTATE_COMPLETE
Sleep 100
Debug.Print "ie1 busy"
Wend
Debug.Print ie1.LocationURL & "AA"
ie1.Navigate2 "http://www.google.com", 1
While ie2.ReadyState < READYSTATE_COMPLETE
Sleep 100
Debug.Print "ie2 busy"
Wend
Debug.Print ie2.LocationURL & "BB"
End Sub
Private Sub ie1_NewWindow2(ppDisp As Object, Cancel As Boolean)
Set ie2 = CreateObject("InternetExplorer.Application")
Set ppDisp = ie2.Application
Debug.Print "NewWindow2"
End Sub

My question is: How can I use this code ??

So far I have tried the following (Set up the code in a standard module)

Option Explicit
Dim IEobject As New Class1
Public Sub Test_Class1()
Set IEobject1.ie1 = InternetExplorer
End Sub

However I get the error:
Compile Error: Variable not defined

I am desperate for any help.

gauss1976


I am writing a macro that will pull up my local auditor website, search for a street name, and then import the results that show up.

Here is the site link with the initial search form:
http://franklincountyoh.metacama.com/do/searchByAddress

If one were to do a search by "Golding" as the street address, a results page populates. I've written the code to open IE, navigate to the form, and search by the street names.... however, I am stumped when it comes to querying the results.

Here is the code I use to navigate to the initial results page:

Code:

Sub Auditorlogin()

Dim objIE As Object
Dim htmlDoc As MSHTML.HTMLDocument
Dim htmlInput As MSHTML.HTMLInputElement
Dim htmlImage As MSHTML.HTMLImg
Dim htmlTable As MSHTML.htmlTable
Dim htmlColl As MSHTML.IHTMLElementCollection
Dim Link As MSHTML.HTMLAnchorElement
Dim iedoc As Object
Dim streetname As String
Dim login_URL As String
Dim counter As Integer
Dim i As Integer
Dim resultspage, queryrow As Integer
Dim name, address, parcelid, nextpageresults As String


Set objIE = CreateObject("InternetExplorer.Application")

' *****************************************
' ***  GET STREET RESULTS FROM AUDITOR  ***
' *****************************************
Sheets("SearchData").Visible = True
Sheets("NumResults").Visible = True
Sheets("AuditorResults").Visible = True
Sheets("Results").Visible = True
Sheets("SearchData").Select
streetname = Range("a2").Value
login_URL = Range("f2").Value

With objIE
    
    ' Here's where I navigate to the URL...
    .Navigate login_URL
    .Visible = 1 '0 = hidden, 1 = visible
    Do While .Busy: DoEvents:  Loop
    Do While .ReadyState <> 4: DoEvents: Loop
    Set htmlDoc = .Document
    
    ' Here's where I identify the inputs for streetname
    ' and input those values as defined above...
    Set htmlColl = htmlDoc.getElementsByTagName("INPUT")
    For Each htmlInput In htmlColl
        If htmlInput.name = "streetName" Then
            htmlInput.Value = streetname
            End If
        
        '**** OLD VERSION OF THIS WEBSITE USED the sid string in the URL****
        'If htmlInput.name = "sid" Then
        '    Range("j2").Value = htmlInput.Value
        'End If
        '
        
    Next htmlInput

    ' Here's where I identify the element on the page that needs to be
    ' clicked to submit the form...
    
    Set htmlColl = htmlDoc.getElementsByTagName("INPUT")
 
    For Each htmlInput In htmlColl
        If htmlInput.Value = "Search" Then
            htmlInput.Click
            Exit For
            End If
    Next htmlInput
    
    Do While .Busy: DoEvents:  Loop
    Do While .ReadyState <> 4: DoEvents: Loop


This gets me to the results page....what I need to be able to do is query the results page to captu
A) The # of entries found (using the Golding example it would be 39.
B) The parcel #'s of each result.

I think if you can point me in the direction of how to import just the 1st results page, I may be able to find my way around the rest... Just looking for a nudge in the right direction.

Any and all help is appreciated!


I am hoping this is a simple solution. I currently am trying to get my macro to log in to my bank and then to go to the detail page where I want to copy the detail information.

I can get the macro to log me in with the user id and password, however when I get to the main page, I am unable to figure out how to get to the specific detail account page. Below is the page source info. (note there are 2 accounts on this page). Any ideas how to get this to work? Any help would be greatly appreciated!

Quote:

<tr align="right" class="summary2"><td width="165" align="left"><table border=0 cellpadding=0 cellspacing=0><tr><td><img src="/images/sp.gif" width="5" height="10"></td><td class="bodytext"><a href="#" onclick="document.accountDetailsForm.inquiryAccountIndex.value = '0'; set(document.accountDetailsForm, '/OLB/fin/vie/als/accountDetails?mode=confirmation&buttonClicked=');document.accountDetailsForm.submit(); return true;" class="links">Chequing</a></td></tr></table></td><td width="135" class="bodytext"><a href="#" onclick="document.accountDetailsForm.inquiryAccountIndex.value = '0'; set(document.accountDetailsForm, '/OLB/fin/vie/als/accountDetails?mode=confirmation&buttonClicked=');document.accountDetailsForm.submit(); return true;" class="links">0122 3322-153</a></td><td width="90" class="bodytext">Jan 6, 2010</td><td width="165"><table border="0" cellpadding="0" cellspacing="0"><tr><td width="140" class="bodytext" align="right">$400.16 CAD</td><td width="5" class="bodytext" align="right">&nbsp;</td><td width="20" class="bodytext" align="center">DR</td></tr></table></td></tr>

<tr align="right" class="summary1"><td width="165" align="left"><table border=0 cellpadding=0 cellspacing=0><tr><td><img src="/images/sp.gif" width="5" height="10"></td><td class="bodytext"><a href="#" onclick="document.accountDetailsForm.inquiryAccountIndex.value = '1'; set(document.accountDetailsForm, '/OLB/fin/vie/als/accountDetails?mode=confirmation&buttonClicked=');document.accountDetailsForm.submit(); return true;" class="links">Savings</a></td></tr></table></td><td width="135" class="bodytext"><a href="#" onclick="document.accountDetailsForm.inquiryAccountIndex.value = '1'; set(document.accountDetailsForm, '/OLB/fin/vie/als/accountDetails?mode=confirmation&buttonClicked=');document.accountDetailsForm.submit(); return true;" class="links">0111 2222-111</a></td><td width="90" class="bodytext">Jan 6, 2010</td><td width="165"><table border="0" cellpadding="0" cellspacing="0"><tr><td width="140" class="bodytext" align="right">$1080.98 CAD</td><td width="5" class="bodytext" align="right">&nbsp;</td><td width="20" class="bodytext" align="center">&nbsp;&nbsp;&nbsp;&nbsp;</td></tr></table></td></tr>


Below is the macro that gets me to the page source info above

Code:

Private Sub CommandButton1_Click()
 Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "https://www1.bmo.com/cgi-bin/netbnx/NBmain"
        '  Loop until the page is fully loaded
        Do Until Not .Busy
            DoEvents
        Loop
     ' Make the desired selections on the web page and click the submit Button
       
          Set ipf = IE.document.all.Item("FBC_Number")
            ipf.Value = "xxinsert useridxx"  'filler user id for example
        Set ipf = IE.document.all.Item("go")
            ipf.Value = "submit"
        ipf.Click
         ' Loop until the page is fully loaded
        Do Until Not .Busy
            DoEvents
        Loop
         
         Set ipf = IE.document.all.Item("FBC_Password")
         ipf.Value = "xxinsert passwordxx"  'filler password for example 
          Set ipf = IE.document.all.Item("go")
            ipf.Value = "submit"
        ipf.Click
         ' Loop until the page is fully loaded
        Do Until Not .Busy
            DoEvents
        Loop
        
         End With







Hello,

I am trying to login to and scrape some web data. I read this thread :
http://www.excelforum.com/excel-prog...matically.html
I copied code and customized it and it gave me a run-time error '91'. I suppose I would have to customize it further to work with Scottrade but I don't know how.
Can you pls help ? Thank you.

Sub OAPData()
Dim a As String
Set ie = CreateObject("InternetExplorer.Application")
'Asks user for password and username ID's for the site
ExtranetUser = InputBox(Prompt:="Please input your Username", _
Title:="Input Extranet Username")
ExtranetPass = InputBox(Prompt:="Please input password", _
Title:="Input Extranet Password")
'Creates Internet Explorer webpage and logs into our website given the username and password entered.
'This process is not visible
With ie
.Visible = False
.Navigate "http://www.scottrade.com/"
Do Until .ReadyState = 4
DoEvents
Loop
.Document.all.Item("user").Value = ExtranetUser
.Document.all.Item("PassWord").Value = ExtranetPass
.Document.forms(0).submit
End With
'Creates new tab with the given name "CoopData"
For Each c In Sheets("Kickoff macros page").Range("A4")
Sheets.Add
ActiveSheet.Name = "CoopData"
Next c
'Calls remaining macros

Hi,

I'm trying to link a workbook to a table on the web, but the table is on a secure site. I have cobbled together code to open excel and enter the user name and password, but I can't figure out how to submit the form. This is the first time i've attempted to work with explorer via VBA, so I'm kinda lost....can anyone point me in the right direction...or at least give me a lead on what to research to figure this out? Code so far...

Code:

Sub Navigator()
Dim ie As Object
Set ie = CreateObject("internetexplorer.application")
ie.Visible = True
ie.navigate "https://secure.someaddress.com"
Do While ie.busy: DoEvents: Loop
Do While ie.readystate  4: DoEvents: Loop
With ie.document.forms("frmLogIn")
    .UserName.Value = "ThisWorks"
    .Password.Value = "ThisAlsoWorks"
End With
End Sub


At the bottom of the form ("frmLogIn") there is a button that says submit...I know I could probably use SendKeys, but I wanted to avoid it and learn how to interact with that button.

Thanks a lot,

Jason


I have a macro set up and what it should do is go to a website copy the web page and paste into cell A1.
The problem I am having is that it will open the IE, open the Page but will not do the copy paste function can someone tell me what i am doing wrong

Code:

Sub Macro4()

   Dim IE As Object
     
     Sheets("Sheet2").Select
     Cells.Select
    Selection.Delete Shift:=xlUp
    Range("A1:A1000") = "" ' erase previous data
    Range("A1").Select
     
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://www.google.com" ' shou
        Do Until .ReadyState = 4: DoEvents:  Loop
        End With
         
        
        IE.ExecWB 17, 0 '// SelectAll
        IE.ExecWB 12, 2 '// Copy selection
        ActiveSheet.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False
        Range("A1").Select
        IE.Quit
        
    
    
End Sub





Hi All, I'm trying to export all of the tables on this website:
http://ca.ishares.com/broker_dealer/index.do

Using a standard web query only brings in the first table "iShares Cdn Equity Fund" ...and none of the tables below (with Fixed Income Funds or Hedged Funds). I've tried to edit the query and 'check off' these tables, but it doesn't work.

Alternatively, I've borrowed some code from another post to get the data using VBA. I've pasted it below. It gets ALL the data (tables, text, other), but dumps it all into a single cell! instead of a 'proper table'.

I could really really use some help either making the web query work (and bring in all these price tables on the website), or modifying the code to capture the data properly in excel. Please take a look and let me know if you have any thoughts! All help much appreciated!


Code:

 
Sub GetNavs()
 
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://ca.ishares.com/broker_dealer/index.do"
        Do Until .ReadyState = 4: DoEvents: Loop
        Set myTextField = .Document.all.Item("Search_SAC")
        myTextField.Value = "529910"
        IE.Document.Forms(0).Submit
 
        Do Until .ReadyState = 4: DoEvents: Loop
        Do While .Busy: DoEvents: Loop
        Set doc = IE.Document
        GetOneTable doc, 1
        .Quit
    End With
 
End Sub
Sub GetOneTable(d, n)
' d is the document
' n is the table to extract
Dim e As Object ' the elements of the document
Dim t As Object ' the table required
Dim r As Object ' the rows of the table
Dim c As Object ' the cells of the rows.
Dim I As Long
Dim J As Long
    For Each e In d.all
        If e.nodename = "TABLE" Then
            J = J + 1
        End If
        If J = n Then
            Set t = e
 
            tabno = tabno + 1
            nextrow = nextrow + 1
            Set rng = Range("A" & nextrow)
            For Each r In t.Rows
                For Each c In r.Cells
                    rng.Value = c.innertext
                    Set rng = rng.Offset(, 1)
                    I = I + 1
                Next c
                nextrow = nextrow + 1
                Set rng = rng.Offset(1, -I)
                I = 0
            Next r
            Exit For
        End If
 
    Next e
 
End Sub