Daily Dose of Excel

Automating Internet Explorer

22nd September 2004, 10:33 am by Dick Kusleika

Usually, web queries fit the bill when you need web data in your workbook. But sometimes, you need to do more. Actually, sometimes I need more; I have no idea what you need.

I don’t know too much about automating IE, but I thought I would share what I do know. If you know less than me, you might learn something you can use. If you know more, hopefully you leave a comment so I can learn something.

First, set a reference (VBE>Tools>References) to Microsoft Internet Controls.

Autoweb1

The basic structure of my automation subs go like this: Dimension a variable as InternetExplorer and one as Object (that will hold the Document object); Set the IE variable to a NEW instance of InternetExplorer; Define a URL and Navigate to it; Run a loop that basically waits until the document is loaded; Set the Object variable to the Document object.

What happens next varies greatly depending on what you want to do. When I get to this point I go to the MSDN page on the Document object and start looking for what I need. Because Microsoft has crazy software behind their MSDN pages, I can’t link directly to the Document object page. You have to select Properties, then Document, then another Document link to get where you need to be. Highly scientific, I know. For this example, I want to list all the hyperlinks on this page on a worksheet. I noticed that the Document object has a Links collection - sounds perfect. I’ve never had much luck with For Each loops while automating IE, so I generally loop using a Long variable.

Have a look at this example and see if you can make heads or tails of it.

Sub ListLinks()

    Dim IeApp As InternetExplorer
    Dim sURL As String
    Dim IeDoc As Object
    Dim i As Long

    ‘Create new instance of IE
    Set IeApp = New InternetExplorer

    ‘Make it visible - some things don’t work
    ‘unless it’s visible
    IeApp.Visible = True

    ‘define the page to open
    sURL = “www.dicks-blog.com”

    ‘navigate to the page
    IeApp.Navigate sURL

    ‘Pause the macro using a loop until the
    ‘page is fully loaded
    Do
    Loop Until IeApp.ReadyState = READYSTATE_COMPLETE

    ’store the Document object
    Set IeDoc = IeApp.Document

    ‘Loop through the links collection.  Most collections
    ’seem to be zero based, but it’s pretty much trial and
    ‘error for me
    For i = 0 To IeDoc.links.Length - 1
        ‘write the linking url to a cell
        Cells(i + 1, 1).Value = IeDoc.links(i).href
    Next i

    ‘Clean up
    Set IeApp = Nothing

End Sub

Category: VBA Advanced, Automation | Comment (RSS)

108 Comments

ross:

i have never needed to do more, but look ok to me Dick.

off topic, but i would be intrested on any feedback people may have, this is the code i use to fire a link to my site from excel (note that the site is “in the makeing”)

Private Sub Label2_Click()
On Error GoTo couldnotopen
If Left(Application.Version, 1) >= 9 Then
ActiveWorkbook.FollowHyperlink Address:=”http://www.methodsinexcel.here.ws”, _
NewWindow:=True
Else
Shell “C:\Program Files\Internet Explorer\Iexplore.exe www.methodsinexcel.here.ws”
End If

this works ok, but there is there a better way, a way to use the shell method with 97?

no bother like, just wondering
Exit Sub
couldnotopen:
MsgBox “Sorry access to WWW.METHODSINEXCEL.HERE.WS could not be achieved” _
& Chr(13) & “If you would like to visit the site please try typing the address in you web” _
& Chr(13) & “browser, or performing an internet search, thanks.”, vbInformation + vbOKOnly, “M.I.E XL 97 quickview”
End Sub
23 September 2004, 3:25 am
Juan Pablo G:

Ross,

What I do is use the ShellExecute API, to load the default browser. Look at an example here:

http://www.xcelfiles.com/openanyprgrm.htm
23 September 2004, 12:10 pm
ross:

Cool, didn’t understand any of it, will take a poper look when i get home (bust at work today!), thanks PJG
24 September 2004, 4:43 am
Ken:

Where do you get a listing of all of the flags that can be used with .Navigate? I have been trying to get spreadsheet with multiple hyperlinks in it to launch all of them to the same window. It seems that Excel want to open a new window for each link by default.
25 September 2004, 12:29 pm
Mpemba:

Flags for navigate are (currently) listed at:
http://msdn.microsoft.com/workshop/browser/webbrowser/reference/enums/browsernavconstants.asp
7 December 2004, 7:55 am
Tushar Mehta:

If anyone is interested in using a browser without leaving XL (or Word or PowerPoint) — with special support for searching the google newsgroup archive, check
Floating Browser
http://www.tushar-mehta.com/excel/software/webbrowser/index.html
7 December 2004, 5:42 pm
M. Mendez:

THANK YOU for this. Looked for 2 hours trying to find this. Kept finding Shell to Explorer which doesn’t give you any control. What a lifesaver!
11 December 2004, 11:47 am
John F. Jackson:

Super - did just what I wanted, once I learnt to type certain things in upper case ;-(

shtml = document.documentElement.innerHTML

One little suggestion for your splendid page …

‘Clean up - close web page window and recover storage
IeApp.quit
IeApp = Nothing

Thanks.
15 December 2004, 6:52 am
jose luis sainz:

hello, I`m looking information about fill a form in internet from excel. mi problem is that I need to send some parameters to the website before to do the query.
I’ve found many information about downing data from internet to excel but not in the other direction.
I know the fields to fill in HTML. but not how

it�s possible to do it?
15 December 2004, 9:41 am
Jake Marx:

Hi Jose,

You can look here for an example on using the XMLHTTP object to POST data to a URL and retrieve the results.
15 December 2004, 10:58 am
John F. Jackson:

Another suggestion: I found the tutorial on DOM at www.BrainJar.com a more helpful introduction than diving straight into Microsoft’s object definition; for non-expert programmers I should add.
16 December 2004, 3:14 pm
jose luis sainz:

thaks a lot, I’ve found an other direcction,
http://www.dotnetjunkies.com/weblog/davetrux/archive/2003/09/08/1447.aspx

I would like ask you another thing, I am looking for is a book about VBA in excel (medium or advance level) the important thing is that explains the class modules, the notions (I do not understand how it works, and I’m accountan not a programer).

Thank you very much for your help.
17 December 2004, 6:45 am
Neon Angel:

This prolly won’t fit on one comment - I may have to use another - but here is the code behind the Browser class object I use, based on the same Microsoft Internet Controls reference. Also note that for regular expressions to work you need a reference to Microsoft VBScript Regular Expressions.

To use, simply declare a (Public) variable as Browser. Then use any of the following methods:

‘ NAVIGATE(LOC as string) | equivalent to typing loc into the address section of the browser
‘ REFRESH | Equivalent to pressing the refresh button in your browser
‘ BACK | Equivalent to pressing the back button in your browser
‘ BUTTON(CAPTION as string) | click the button containing text CAPTION
‘ HYPERLINK(TEXT as string, URL as string, DISABLEONCLICK as boolean) | click the hyperlink containing Text or linking to URL
‘ DROPDOWN(NAME as string, OPT as integer) | select option OPT from dropdown NAME
‘ TEXTBOX(NAME as string, TEXT as string) | fill textbox NAME with TEXT
‘ OPTION BUTTON(NAME as string, NUM as integer) | click the element NAME(NUM)
‘ FINDHTML(TEXT as string) | returns True if TEXT is found in the current page
‘ FINDNEXTTAG(TAGTYPE as string, TAGINDICATOR as string, TAGDELAY as integer) | searches for a TAGTYPE containing TAGINDICATOR and returns the content of TAGDELAY TAGTYPEs after it
‘ REGEXTRACT(PTN as string, SNG as boolean, STARTSTR as string, ENDSTR as string) | returns an array (value if SNG) of regular expression PTN in the code between STARTSTR and ENDSTR
‘ RUNJAVASCRIPT(SCR as string) | executes the javascript SCR
‘ OBJECT | allows direct access to the browser object when the above methods are inadequate

Many of the parameters are optional and most functions have the capacity to return False if the element being interacted with is not found. Hope this proves useful!

Option Explicit
Private Declare Function FindWindow Lib “user32″ Alias “FindWindowA” (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function PostMessage Lib “user32″ Alias “PostMessageA” (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Private Declare Function FindWindowEx Lib “user32″ Alias “FindWindowExA” (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function SendMessage Lib “user32.dll” Alias “SendMessageA” (ByVal hwnd As Long, ByVal Msg As Integer, ByVal wParam As Integer, ByVal lParam As Integer) As Integer
Const optActiveX As Boolean = False ‘ true for activex disabled (also used in pricing)
Const brsVisible As Boolean = True ‘ true for debugging/voyeurism
Dim brs As InternetExplorer

Private Sub Class_Initialize()
Set brs = New InternetExplorer
brs.Visible = brsVisible
End Sub

Private Sub Class_Terminate()
Set brs = Nothing
End Sub

Property Get URL() As String
URL = brs.LocationURL
End Property

Sub LoadPage()
‘ Pauses execution until the browser window has finished loading
Do While brs.Busy Or brs.ReadyState <> READYSTATE_COMPLETE
If optActiveX Then ‘ close any activex popup notifications
PostMessage FindWindow(”#32770″, “Microsoft Internet Explorer”), &H10, 0&, 0&
End If
DoEvents
Loop
End Sub

Sub LoadPageAndClick(cmb As String)
‘ Pauses execution until the browser window has finished loading, clicking prompt button cmb
Dim cmbIndex As Long

Do While brs.Busy Or brs.ReadyState <> READYSTATE_COMPLETE
cmbIndex = FindWindow(”#32770″, “Microsoft Internet Explorer”)
If Not cmbIndex = 0 Then
SendMessage FindWindowEx(cmbIndex, 0, “Button”, “&Retry”), &HF5, 0, 0 ‘ button click
End If
DoEvents
Loop
End Sub

Public Sub Navigate(loc As String)
‘ Navigate to loc
Call brs.Navigate(loc)
Call LoadPage
End Sub

Public Sub Refresh(Optional cmb As String)
‘ Refresh current page
Call brs.Refresh
If cmb = “” Then
Call LoadPage
Else
Call LoadPageAndClick(cmb)
End If
End Sub

Public Sub Back()
‘ Return to previous page
Call brs.GoBack
Call LoadPage
End Sub

Public Function Button(Caption As String) As Boolean
‘ Clicks the button containing text Caption or returns false if button cannot be found
Dim Element As HTMLButtonElement

Button = True
For Each Element In brs.Document.getElementsByTagName(”Input”)
If InStr(Element.Value, Caption) > 0 Then
Call Element.Click
Call LoadPage
Exit Function
End If
Next Element
Button = False
End Function

Public Function Hyperlink(Optional Text As String, Optional URL As String, Optional DisableOnClick As Boolean) As Boolean
‘ Clicks a link based on whichever of lnkText or lnkURL is provided, returning false if lnk cannot be found
‘ Optional lnkOnClick can be used to disable the OnClick event for the link
Dim Element As HTMLLinkElement

Hyperlink = False
If Not URL = “” Then ‘ hyperlink by url
URL = Replace(URL, “&”, “&”)
For Each Element In brs.Document.links
If Element = URL Then
Hyperlink = True
Exit For
End If
Next Element
Else ‘ hyperlink by link text
For Each Element In brs.Document.links
If Element.innerText = Text Then
Hyperlink = True
Exit For
End If
Next Element
End If
If Hyperlink Then
If DisableOnClick Then
Element.onclick = “”
End If
Call Element.Click
Call LoadPage
End If
End Function

Public Function DropDown(Name As String, Opt As Integer) As Boolean
‘ Select option Opt from dropdown Name
Dim Element As HTMLDDElement

DropDown = False
Set Element = brs.Document.getElementsByName(Name)
If Not Element Is Nothing Then
Element(0).Value = Opt
DropDown = True
End If
End Function

Public Function TextBox(Name As String, Text As String) As Boolean
‘ Fill textbox Name with Text
Dim Element As Variant

TextBox = False
Set Element = brs.Document.getElementsByName(Name)
If Not Element Is Nothing Then
Element(0).Value = Text
TextBox = True
End If
End Function

Public Function OptionButton(Name As String, Optional Num As Integer) As Boolean
‘ Toggle option button Name(Num)
Dim Element As Variant

On Error GoTo ErrorHandler
OptionButton = True
Set Element = brs.Document.getElementsByName(Name)
If IsMissing(Num) Then
Element.Click
Else
Element(Num).Click
End If
On Error GoTo 0
Exit Function

ErrorHandler:
OptionButton = False
On Error GoTo 0
End Function

Public Function FindHTML(Text As String) As Boolean
‘ Searches for SearchText in browser
FindHTML = (InStr(brs.Document.body.innerText, Text) > 0)
End Function

Public Function FindNextTag(tagType As String, tagIndicator As String, tagDelay As Integer) As String
‘ Returns the content of the tag of type tagType tagDelay tagType’s after the first one containing tagIndicator
Dim Element As HTMLGenericElement
Dim Count As Integer

For Each Element In brs.Document.getElementsByTagName(tagType)
If Count > 0 Then
If Not Count = tagDelay Then
Count = Count + 1
Else
FindNextTag = Element.innerText
Exit Function
End If
Else
If Element.innerText = tagIndicator Then
Count = 1
End If
End If
Next Element
End Function

Public Function FindValue(Name As String)
Dim tmp As Object

Set tmp = brs.Document.All.Item(Name).Value
FindValue = brs.Document.All.Item(Name).Value
End Function

Public Function RegExtract(ptn As String, Optional sng As Boolean, Optional StartStr As String, Optional EndStr As String) As Variant
‘ Searches for ptn as a regular expression in browser, returns submatches (case insensitive)
Dim RegEx As RegExp
Dim Ex As Variant
Dim Page As String
Dim Extracted() As Variant
Dim Record As Integer, i As Integer
Dim StartPos As Long, EndPos As Long

Set RegEx = New RegExp
RegEx.Global = True
RegEx.IgnoreCase = True
RegEx.Pattern = ptn
Page = brs.Document.body.innerHTML
Page = Replace(Page, “align=center”, “align=middle”) ‘ align=center in html gets interpreted as align=middle
Page = Replace(Page, “,”, “”) ‘ commas make formatted numbers difficult to gather
StartPos = InStr(1, Page, StartStr, vbTextCompare) + Len(StartStr)
Page = Mid(Page, StartPos)
If Not EndStr = “” Then
EndPos = InStr(1, Page, EndStr, vbTextCompare)
Page = Left(Page, EndPos)
End If
Page = Replace(Page, Chr(13) & Chr(10), “”) ‘ allows regexp to flow across lines
For Each Ex In RegEx.Execute(Page)
Record = Record + 1
ReDim Preserve Extracted(1 To Ex.SubMatches.Count, 1 To Record)
For i = 1 To Ex.SubMatches.Count
Extracted(i, Record) = Ex.SubMatches(i - 1)
Next i
Next Ex
If Not Record = 0 Then
If Not IsMissing(sng) Then
If sng = True Then
RegExtract = Extracted(1, 1)
On Error GoTo 0
Exit Function
End If
End If
RegExtract = Extracted
End If
End Function

Public Function RunJavascript(scr As String)
‘ Runs the javascript scr
Call brs.Document.parentWindow.execScript(scr, “JavaScript”)
Call LoadPage
End Function
3 January 2005, 3:51 pm
Neon Angel:

What do you know, it did fit. Indenting really needs to be implemented in this forum :p

As an aside, to test regular expressions in advance, I usually copy some of the html from the page and use the following procedure to test my ptn:

Sub TestRegExp()
Dim RegEx As RegExp
Dim Ex As Variant
Dim Page As String
Dim i As Integer

Set RegEx = New RegExp
RegEx.Global = True
RegEx.IgnoreCase = True
‘ place your best guess pattern here, then systematically comment out sections of it and see if the loop still returns output
RegEx.Pattern = “Your PTN goes here”
‘ place an excerpt from the source code here, using & _ to separate lines
Page = “Your HTML goes here”
‘ browser objects remove the single quotes from the raw html
Page = Replace(Page, “‘”, “”)
Page = Replace(Page, “align=center”, “align=middle”)
‘ if this loop returns output you know you are on the right track
Debug.Print vbCrLf & “**New Output**”
For Each Ex In RegEx.Execute(Page)
For i = 1 To Ex.SubMatches.Count
Debug.Print Ex.SubMatches(i - 1)
Next i
Next Ex
End Sub

Regards,

Alex
3 January 2005, 3:54 pm
Mpemba:

I’m having problems with the line:

Do While brs.Busy Or brs.ReadyState READYSTATE_COMPLETE.

It seems to work OK if I put a “less than” symbol before READYSTATE_COMPLETE.

I’m guessing that “<>” fails to be shown.

M
6 January 2005, 8:34 am
John F. Jackson:

“… some things don’t work if it (the IE window) isn’t visible.”

Can anyome make this statement more precise please?

I’ve written a scheduled data collection process designed to run over hours or even days. I’d still like to use my machine in the meantime! Having IE keep popping up out of the blue is becoming annoying.

I’ve ignored the advice to make the IE window visible … and everything I want to do works fine.

What’s the catch Dick? (anyone?)
9 January 2005, 12:16 pm
Gates Is Antichrist:

Hi, first time listener and first time caller.

Referring to the original code, besides adding
Set IeApp = Nothing
as noted, the For loop might be preceded by
Sheets.Add
for those of us children who should not play with guns or matches. Seriously, sheets.add ensures no destruction of data, for those who simply want to try out your code. (Naturally, in active use, one would instead know what they were writing to in using Cells().Value=…)

Thank you for Dick’s blog. From all I’ve just seen after a long browse through time, and all I’ve long heard, you rule. I say without exaggerating any of the following: the URL structure is commendably user-useful, your content is useful, you hit things I’ve not seen, you provide the powerful comment interface (and have attracted big hitters, recognizing many of their names), you have interesting off-Excel blogstuff, and I’m sure I could go on. If there was a People’s Choice Award, there might be blood and fists between choosing yours and j-w — or even Woody! Thank you for all the effort and the not-unnoticed EXTRA effort you have put in. (But beware - if you keep this up, the Redmond pea-brains will buy you out and lobotomize all that quality they so despise.)
10 January 2005, 1:53 am
Ruud van der Linden:

I put the code in my application, but I get the following, when I want to execute the code:

Class does not support Automation or does not support expected interface
19 January 2005, 2:48 am
peter gooding:

hello folks

I want to start an instance of IE from Word. Then get the blank HTML page to display some plain text. (Similar process to starting a new instance of Word using automation and then using ‘Selection.InsertAfter(Text As String)’ to add text to the blank document).

I can get IE started and made visible - no problem there. But how to add the text to the blank page? (NB The text to be added is stored within a VBA module in Word).
24 February 2005, 10:10 am
Jake Marx:

Hi Peter,

You can do something like this:

Dim ie As InternetExplorer

Set ie = New InternetExplorer
ie.Visible = True

ie.Navigate “about:blank”
ie.Document.body.innerhtml = “test
this is a test.”
‘/ OR
‘/ ie.Document.body.innertext = “test” & vbLf & “this is a test.”
Set ie = Nothing

If you use innerhtml, you can use HTML formatting. If you just want plain text, you can use innertext.
24 February 2005, 5:35 pm
prashanthy:

Dick,
There is two text Boxes in my Internet Explorer User Name and Password Text box where i have to key in both how to automate from excel your page was excellent to automate the web Page.
31 March 2005, 9:58 pm
Nate Oliver:

Hello prashanthy,

Here are a couple of examples that might help:

http://www.mrexcel.com/board2/viewtopic.php?t=25039&p=362011#362011
http://www.ozgrid.com/forum/showpost.php?p=159601

Private Sub PostalCodes()
Dim ie As Object
Set ie = CreateObject(”InternetExplorer.Application”)
On Error GoTo errHandler
With ie
.navigate “http://www.canadapost.ca/personal/tools/pcl/bin/quick-e.asp”
Do While .busy: DoEvents: Loop
Do While .ReadyState 4: DoEvents: Loop
With .document.Forms(1)
‘Stree Number
.street_number.Value = “10132″
‘Stree Name
.street_name.Value = “Imperial Street”
‘City
.city.Value = “Chilliwack”
‘AB = Alberta, BC = British Columbia, MB = Manitoba, NB = New Brunswick, _
NL = Newfoundland and Labrador, NS = Nova Scotia, NT = Northwest Territories, _
NU = Nunavut, ON = Ontario, PE = Prince Edward Island, QC = Quebec, _
SK = Saskatchewan, YT = Yukon
.prov.Value = “BC”
.submit
End With
Do While Not CBool(InStrB(1, .document.URL, _
“cp_search_response-e.asp”))
DoEvents
Loop
Do While .busy: DoEvents: Loop
Do While .ReadyState 4: DoEvents: Loop
MsgBox .document.all.tags(”table”).Item(11).Rows(1).Cells(7).innerText
End With
Exit Sub

errHandler:
ie.Quit: Set ie = Nothing
End Sub

The trick is to check out the source code of the page you�re logging into and figure out the names of the controls you’re looking at.

This is not always as straight forward as it might be, e.g., at one point Hotmail’s controls names had spaces in them (they changed their source code quite a bit over the past few years); so you couldn’t directly refer to them as I have in my examples.

Another time you might want to automate IE via VBA is if your target site has a variable number of tables.

I could be wrong, but when I was trying to help someone query Yahoo, it looked the critical data table in question was variable in terms of which table number it was within the page by stock symbol. So, I ended up writing a loop:
http://mrexcel.com/board2/viewtopic.php?p=357149#357149

All of the aforementioned examples use late binding, so no reference is necessary to either:
Microsoft Internet Controls library
Microsoft HTML Object Library

Regards,
Nate Oliver
1 April 2005, 1:17 pm
Nate Oliver:

I’ve uploaded Example 2 (The Ozgrid post), regarding grabbing Canadian Postal codes, here:
http://www.mrexcel.com/board2/viewtopic.php?p=671041

The intended formatting makes for an easier read.

Regards,
Nate Oliver
2 April 2005, 6:44 pm
peter gooding:

Message to Jake Marx

Jake. Thanks a bunch for your help with my IE VBA problem (24th Feb) - your solution works perfectly. Much appreciated

Peter
6 April 2005, 6:41 am
Anonymous:

Just wanted to thank everyone for their participation here.

I have added Internet Explorer automation to existing mixture of Excel, Access and Outlook, and have successfully automated several hideous manual processes.
15 April 2005, 8:18 am
Leo Barnhoorn:

Hello,

At first thanks for the code i found here it help me a lot.

I have a question, hou can i check for textbox in a FRAME?

Now it reads only the first frame and in the second frame there are textboxs and i can not find out how to reache them.

Regards,
Leo Barnhoorn
30 April 2005, 11:17 am
Chrispy:

Hi,

Is it possible to automate a few keystrokes and a mouseclick from Excel into IE?

I’m trying to openan intranet report, click insside one of the frames, then Select All and Copy, so then Excel can paste the data into a new spreadsheet.

Or does some scripting tool need to be run to perform the mouse click?

With Thanks,
Chrispy
3 May 2005, 6:42 pm
Juan Manuel:

I am not able to access a web page that contains an application client/server made with citrix. What can I do?
12 May 2005, 2:29 pm
Robin Lavoie:

Thanks Nate for your excellent suggestion (22 and 23 above).

I create my little application using a form to get user input, push those to the web query, and then display the results in that same form.

In some cases, the result on the web site (Canada Post) is on more than one page. To get the following page the user, if on the web page, has to click on number that have javascript like:

href=”javascript:SubmitNavigate(51);

Is there a way from Excel VBA to submit those javascript?

Regards,
Robin Lavoie
16 May 2005, 8:34 am
Guy Carnegie:

I’m using your code to loop through around 400 coldfusion pages, making a change to a form, automating a “submit” by navigating to the relevant javascript call and closing the window, before starting all over again.

Some of the forms I’m trying to change have outdated information though and submitting these forms kicks out an error (an alert box event). Obviously, this means that I can’t automatically close the window as I need to acknowledge the alertbox first.

What I want to know is how to automatically detect the alertbox when it appears so I can then change a couple of other form fields before resubmitting and closing the window.

Many thanks
Guy Carnegie
7 July 2005, 1:52 pm
Guy Carnegie:

On a related note, how can I “click” on the “OK” button in the IE alertbox before continuing to change the above forms?

thanks
Guy
7 July 2005, 1:57 pm
Guy Carnegie:

What about this? Because I am looping through 400 pages, opening and closing intances of IE for each one, when an “alert” is generated by bad data on a form, that window will remain open (as I can’t close it as it has an alert which hasn’t been acknowledged). The VBA will, however, go merrily on its way and carry on looping, opening another page in another instance of IE.

Could I detect in VBA that TWO instances of IE are open and ASSUME that the first one is still open BECAUSE there is an alert on it.

Could I then use hwnd (I’ve no idea how) to switch to the first window (I think the focus will automatically go to the alert box “OK” button, send a “sendkeys(ENTER)” to acknowledge the alert and then make the relevant changes to the form, followed by a submit and close window, before carrying on?

I have no idea how to do the above, but in the case that I can’t detect or automate the IE javascript alert I don’t think there would be any other option.

Any ideas how I would do this?

many thanks
Guy
7 July 2005, 2:21 pm
Simon:

To Leo Barnhoorn in point 26:

Frames can be accessed in the DOM thus:

if you had a documnet that contained a frame called “MyFrame”

Set Document = ExplorerWindow.Document
Set Frame = IPCDocument.all.namedItem(”MyFrame”)
Set FrameDocument = Frame.ContentWindow.Document

FrameDocument will now be the DOM for the Frame. If it’s just one Frame then your textboxes should be in there to see.

Hint: I had to do this on a very complex webpage (4 nested frames, about 15 frames in total!) I found it exteremely helpful to use the above method then step in using the debugger and the explore the structure of the DOM in the locals window
11 July 2005, 4:50 am
Simon:

To Chrispy in point 27:

My solution to this was to itterate through the elements of the DOM and put them into my spreadsheet. In my case I was actually looking for specific infomation so I did some processing at the same time but here is no reason you could not extract the entire document, for example:

For Counter = 0 To IPCOutputFrameDocument.all.Length - 1

Cells(counter,1) = IPCOutputFrameDocument.all.Item(Counter).innerText

next Counter

This would be extremely crude and put the contents of each HTML element (e.g. a table cell or paragraph marker or anything else)

The system I used was mor like this:

//Set up a loop
For Counter = 0 To IPCOutputFrameDocument.all.Length - 1

//test to see if I have a TD element, you could use any element or indeed all elements
If IPCOutputFrameDocument.all.Item(Counter).nodeName = “TD” Then

//Get the text out
Text = IPCOutputFrameDocument.all.Item(Counter).innerText

//Test if the infomation is what I want, for example if your looking for prices you could use “�” or whaterver…
If InStr(Text, “SomeString”) > 0 Then

//Using a separate variable paste row so I don’t end with blank rows where i cose not to take the data
Cells(PasteRow, 1) = Text
PasteRow = pasteRow + 1
end if
end if
next counter

hope this makes sense!
11 July 2005, 5:00 am
Simon:

To Guy Carnegie in points 31,32 and 33:

I’ve just done some experimenting and from what I can see the alert box is modal and this actually stops the VBA script from executing any more code until it is dealt with. This means it is impossible to deal with except by human intervention once it has appeareds.

The only solution seems to make sure it does not appear, for example by checking to make usre your on a new page some other way before trying to excute the rest fo your logic or maybe by turning off the alerts. I’m not sure if this is directly possible (it may be I don’t know) but if you disabled javascript that would do it. That of course may effect other functionality on the page you need…?
11 July 2005, 5:34 am
Simon:

To Robin Lavoie in point 29:

Yes, easily if it’s a link, for example

Set DOM = MyWindow.document

Set myLink = Mywindow.links(ITEM_NUMBER)
myLink.click

This euqivilent to a user clicking so so long as you can identify your desired link you can click it.
11 July 2005, 5:53 am
Benjamin Compton:

Okay, I have been looking through this code and finding out some things that I can do in order to make my task easier.

What I am trying to do is input data from Excel into a Internet Explorer Database. I cannot upload the data the easy way (ISD rules) so I am trying to develop a VBA solution to my problem. One thing that I want to do first is open a Form with Explorer in it. I am using code sent down from Nate on #22. It opens a internet connection but does not show it to you. How do I make this visible?

The second thing I would like to do is insert cell data into textfields on the internet Explorer page. I believe I can do this by following the code set down from Nate but I can not view it so I don’t know if it is working. Any suggestions?

Thanks
Ben Compton
11 July 2005, 1:09 pm
Benjamin Compton:

Okay I fixed the visible part…

Remembered to put .visible = True

Thanks
Ben
11 July 2005, 1:11 pm
Benjamin Compton:

Now is there a reference also to any thing about calling JAVA items in the code. I mean to input information into the Java Text boxes on the screen. I will look around further….

Thanks
Ben Compton :)
11 July 2005, 1:21 pm
Jaime Givens:

What’s the VBA script I use to click a button on a webpage…

For instance I’ve tried

IE.Document.Form1.Button.Click

But I get an error every time…

Please help.

Thanks

Jaime Givens
27 July 2005, 1:59 pm
Benjamin Compton:

Well I finally found out how to work mine. Do you know the name of the Button? Here is a bit of code to click a button or a link…

ie.document.getelementbyid(”name of element”).click()

That works with command buttons and links. Now to find the name, look at the source code of the webpage… Go to the webpage, click on View, then Source. That will give you the Java behind the screen. Locate the ID= for the button you want to click and then insert it in the GetElementIdbyId() quotation marks… Here was the solution to my question about the same thing….

http://www.experts-exchange.com/Web/Web_Languages/JavaScript/Q_21489269.html
29 July 2005, 11:50 am
Brian Nicoloff:

Does anyone have any idea how I can access the fields on a popup window after I click a link on a webpage?> I am able to fill in fields on the main page that opens by accessing there element id’s but when I have it click the save button it pops up another window asking to save. I cannot figure out how to click the save button on this child page.
Thanks,

Brian
11 August 2005, 9:23 am
Simon:

To Brian Nicoloof in point 42:

You need to right a sub to intercept the NewWindow event, this event and others are described here.

http://msdn.microsoft.com/library/default.asp?url=/workshop/browser/webbrowser/reference/objects/internetexplorer.asp

Can’t offer any more advice as I’ve never used this event.
19 August 2005, 8:28 am
Beth Trainer:

Sub EDIT_REAL_ba_trainerIE()
Dim ie As Object

Set ie = CreateObject(”internetexplorer.application”)
ie.Navigate2 “https://npadmin.prod.fedex.com:8443/en/login.html”
Do While ie.readyState 4 ‘4=READYSTATE_COMPLETE
DoEvents
Loop
ie.Document.forms(”LoginForm”)(”login”).Value = Range(”A1″).Text
ie.Document.forms(”LoginForm”)(”password”).Value = Range(”A2″).Text
ie.Document.forms(”LoginForm”)(”rememberid”).Value = True ‘this may or may not work
ie.Document.forms(”LoginForm”).Submit

Do While ie.readyState 4 ‘4=READYSTATE_COMPLETE
DoEvents
Loop

ie.Navigate2 “http://sales.fedex.com/PersistantLayerApp/jsp/persistFrameSet.jsp”

Do While ie.readyState 4 ‘4=READYSTATE_COMPLETE
DoEvents
Loop

ie.Visible = True
Set ie = Nothing
End Sub

The above is working perfectly…however..
The problem: “http://sales.fedex.com/PersistantLayerApp/jsp/persistFrameSet.jsp” is a frame. I need to stay on this frame - not navigate away.

I need to change the other part of frame:
“http://sales.fedex.com//contentApp/salessource/pages/sales_home.jsp. — and go to —– “http://sales.fedex.com//SamApp/controller?requestId=GET_EMP_LOGIN_INFO.

Can I “open” but not “navigate” to?? Thanks!
15 September 2005, 1:33 pm
Ashok:

Hi,
www.dicks-blog.com is a good link.

AShok
22 October 2005, 4:55 am
JRizzle:

Hey.

I have a quick query. I am attempting to automate a process whereby I need to open an internet explorer window, navigate to a desired site, and save the site as a text file.

I am using Excel VBA and am attempting automate this process there.

Thanks

J
24 October 2005, 10:03 am
A. Favre:

Hello,
I’ve got a similar problem as JRizzle. I need to download a file listed on a web page.
Any suggestion?

Thanks

Alain
11 November 2005, 11:26 am
brettdj:

Alain,

You can use XMLHTTP to download a file, see http://vbaexpress.com/kb/getarticle.php?kb_id=799 for an example from Matt Vidas

Jrizzle,

Are you looking to save the entire site or just parse out selected text?

Cheers

Dave
11 November 2005, 7:06 pm
John Weirich:

Hi,

I am trying to use the InternetExplorer object to display a local directory in a browser based on the entry in a field ([Group Name]) in Access. So basically I want to quickly go to the folder that is named the same as the entry in the field by clicking a button on the form. What I have so far is:

Set myExpApp = CreateObject(”InternetExplorer.Application”)
myExpApp.Visible = True
myExpApp.Navigate “Y:\CharterUpdatesx\Groups\” & Me![Group Name]
myExpApp.statusBar = 0

It works fine, but all the files in the directory are displayed as icons, not as a detail list. I allways have to go through the “view” menu to display the folder correctly. Is there a way to automate this? Is there a better way to go about this since I’m not accessing the internet, but a local directory?

Any input would be appreciated.

Thank You!
15 December 2005, 10:11 am
Sean:

I have an Internet Explorer application that requires users to login. I need to launch IE from VBA and then “remember” that specific IE session so I can send additional URL’s once the user has logged in.

So I need to open an internet explorer session from VBA with a specific URL and then send a different URL to that same session later (assuming the user has not closed it). Everything I have tried sends subsequent URL’s to the most recent IE session started instead of the specific (original) IE session. This causes problems if the user has started additional IE sessions after they have logged into the web application.

Any help is appreciated.
30 January 2006, 6:16 am
jkpieterse:

I’d use a public variable (create a reference to “Microsoft Internet Controls”):

Public goIE As internetexplorer ‘Holds internet explorer object

Then I would create a simple subroutine like this:

Public Sub GetIE()
If goIE Is Nothing Then
Set goIE = New internetexplorer
End If
End Sub

Then in the code that has to use the internet explorer object, you could do something like this:

GetIE
With goIE
.Visible = False
.Silent = True
.Navigate sURL
Do Until Not .Busy
DoEvents
Loop
sHTML = .Document.Body.innerhtml
.Quit
End With

Note: sURL contains the URL to navigate to
sHTML contains the content of the page IE returns.
30 January 2006, 9:50 am
chourour:

I need to know how can i click on a Menu item in Javascript, it has no href like:

oCMenu.makeMenu(”sub”+q+”1″,”top”+q+”",” Follow up >”,”",120,0)

Where oCMenu is a menu, and “Follow up>” is an item in this menu which leads to another menu.

So how can i click it “Follow up>” ???
25 February 2006, 1:28 am
Chris Bezant:

Hello

Many thanks to Neon Angel for providing all that code for an explorer class.
I used it to create a simple VBA routine in Excel to call Google and fill in the search box then press the search button. And it works - wonderful!!!!

Now I moved to the real task in hand which is to log on to the Racing Post site in the UK www.racingpost.co.uk but I cannot fill in the login box. Perhaps it’s in a frame or something but I don’t know enough yet to figure it out :o(

Someone referred to a DOM and an IPCDocument but I can’t figure these out either. I have tried debugging and looking at the IE object and its entrails but cannot find what I am looking for.

Can some kind soul point me in the right direction, please?

Thanks again for the generosity of those sharing their knowledge in this thread.

Best Regards
Chris
4 March 2006, 12:19 pm
Ashok:

Hi,
I want to detect any errors that occurs while trying to open a site using internet explorer object in vb ,like url could not be retrieved ,page unavailable etc…can anyone help me on this..
also i tried using the navigateError event but did not work let me know about how to use this event ..
Thanks in advance
Ashok
16 March 2006, 8:37 am
david nguyen:

Hi everyone, I have a project in Vb6. It is program auto input fill to form Internet Explorer 6 on WinXP Pro SP2.

Can you help me? Thank!
23 March 2006, 7:36 am
KG:

Hi,

I want to copy data from a webpage to the excel sheet. I cannot access the form elements cos for security reasons they hav not given access.. So i hav to simulate a ctrl-A ctrl-C operation to copy to clipboard. From the clipboard… activesheet.paste wil paste it i guess..

Thanks in advance!!!
4 April 2006, 6:38 am
gk477:

Hello.

With vb6, is it possible to get the web page title of an IE window that was not launched in vb?

Also, what if that web page title is in some non-English language like Japanese or Korean, can that web page title be retrieved with vb6 without having to change the locality of the operating system?

Thanks.
22 May 2006, 10:05 pm
Chris:

Neon Angel, you are my hero!
12 June 2006, 4:59 pm
Satsco:

Hello everyone,

Thanks to every1 who has posted ideas, links and VB code on this page, I have been able to create a program in VB6, which will open a new IE browser, and submit the form to login to the site. I have been successful with sites like Yahoo!Mail, Hotmail, etc.

Now my problem is that I am not able to do this with sites which have user authentication through .htaccess. There are a few sites, which pop-up an authentication window of IE, and I need to be able to enter the username and password and then submit it for automatic authentication.

Can any1 please suggest how this can be done ?

Thanks in advance,
Satsco.
29 June 2006, 11:03 am
Bryan.Schmidt:

This site has been really helpful for my automation macro in excel. I have reached a roadblock though. I fill out a form on a web page with the following:

_With ie.Document.forms(0)
__.calStartDate_input.Value = “10/21/2006″
__.ddSortOrder.Value = “ReceivedDate”
__.ddExportType.Value = “xls”
__.btnRun.Click
_End With

However, this invokes a FileDownload Dialog. I want the macro to be able to open the Excel file referred to in the dialog and continue with the data extraction. Any ideas how I would do this?
26 October 2006, 3:17 pm
Lou Wilson:

Neon Angel, your code was wonderful! A couple of things missing, though, that I have had to fill in for myself.

One of them is Image, where you need to click on it.

Public Function image(Name As String) As Boolean
‘Clicks the named image, or returns false if not found
Dim element As HTMLImg
image = True
For Each element In ie.Document.getElementsByTagName(”IMG”)
If InStr(element.ID, Name) > 0 Then
Call element.Click
Call LoadPage
Exit Function
End If
Next element
image = False
End Function

I’m still working on Radio. I’ll put it up as soon as I get it working right.

I think that the Neon Angel code deserves the kind of attention that Watir (Web Application Testing In Ruby) gets in Ruby, and for that matter I am considering converting my Ruby scripts because of this code. Thanks again.
8 November 2006, 3:28 pm
John Foulger:

Lots of useful stuff here, but I can’t find exactly what I want.
I already have some VBA code that uses an id let’s call it “id666″ to get information from a website using custom code without using internet explorer. The website has 1000s of pages, and each one relates to a different id.
You can see the id when you right click on a web page and choose “properties”. It’s part of the Address URL eg http://www.thiswebsite.com/MarketView.do?mi=id666&origin=HFL&ex=1

What I want to do is get the value held in “Address: (URL)”, then extract the id, then use my existing code to extract the data for the id.

I’ve written some code that identifies the internet explorer object and document object of the web page, but all the URL type properties I’ve tried just give the url as “www.thiswebsite.com” without the id . How do I access the “Address: (URL)” value displayed on the properties page?
22 November 2006, 8:09 am
Nat Beyer:

First I love this page,

However I got one question. The web site I go to has a bunch of forms with input boxes. Those I can interact with fine. With this code

IeApp.Document.forms(”login”).elements(”User”).Value = “AM8035″
Where IeApp is internet explorer

But it also has one large table. I need to read this table and get only specific infomation.
Specificially I need to read the value in Column 3 and if it matches 230679, I need to copy the all 7 columns in that row.

How do I refer to a table on the web? How to I refer to specific colums/row/cells in a web table?

Thanks
29 November 2006, 2:45 pm
Dick Kusleika:

Nat: http://www.dailydoseofexcel.com/archives/2006/11/29/html-tables/
29 November 2006, 8:28 pm
Nat Beyer:

Thanks so much, I just finished writing the macro I needed, Would of taken me a month or more to figure it out on my own (Im an accountant not a programmer)
4 December 2006, 10:44 am
simus:

Hi everyone,

ok, the thing is there is this WEBSMS sender @ www.dhiraagu.com.mv, its a web page where you sign in & send text messages, you put the mobile number, write the messege in the text field and clik send. and what i wanna do is add some controls to an excel sheet and send text messeges through this page. in the excel sheet there will be a cell for mobile number, another cell for the messege text and a comman button, so when i klick the button the text messege will be sent to the give mobile number, through the website

can anyone help me with this

thanks
12 January 2007, 12:01 am
IMTIAZ:

Hi

I would like to know when i create an IE object in excel and obtain the document from it. If that web page dynamically changes, will my document have the updated html/xml or do i need to referesh the document or IE object. The reason is i need to obtain a tag name form a web page that is updating using AJAX technologies and i cannot obtain the name by right clicking and viewing source?

As i am trying to automate a procedure but cannot obtain the tag names of certain elements.
Please can someone help me with this
12 March 2007, 12:53 am
Sachin:

Hi,
I am trying automate a website, where I am clicking a hyperlink, which furthur opens a new IE window. Now, I have to transfer control to the new window, so that I can control new window to access its links, etc.

Can any one help me?
22 May 2007, 3:20 am
PR:

It gives me an automation error at this line:

Do
Loop Until IeApp.ReadyState = READYSTATE_COMPLETE

The webpage is totally loaded (IE7) but the loop causes an error, i have firefox installed as predefined browser.

…
6 June 2007, 5:55 pm
ruben:

Can the way excel handles hyperlinks be changed? I mean, I have set an hyperlink to an image, and when I click it the image is opened in internet explorer… can I change the program used to open the hyperlink?

Thanks!
15 June 2007, 11:26 am
Nat Beyer:

I am using Excel to gather information from the Web using IE. One page I get information has a table called. “grdSearchResults”. This table only shows up to 50 rows and some quires have 2 or 3 hundred. So I click on the page number at the bottom of the screen with the following line of code.

IeDoc.all(”urlPages”).Children(T).Click

This Code works but it does not wait for the table to load to execute the next line of code. I tried the following two wait commands and both failed.

Do Until IeApp.Busy = False
Loop
Does not work because background scripts on this page mean that is always busy

Do Until IeApp.ReadyState = READYSTATE_COMPLETE
Loop

For some reason the readystate is always complete even when it is updating the new table. I think maybe it’s because the URL does not change just the table. Not sure though.

How do I wait for a table to be loaded? I tried every wait command I can find.
19 July 2007, 12:03 pm
Aaron:

Hello!

I’ve been searching randomly for a couple of weeks now…with no luck.

Does anyone know how to create a VBscript that will prompt for an excel file. The excel file has a list of file numbers.

The script would then go to a specific webpage (intranet)…put the file number into a page and submit the data (click a button on the page. Once the next page loads it clicks a link.

I’ve gotten as far as having IE open and load the specific address. After this, I’m at a loss
24 July 2007, 3:30 pm
Lee:

you would need to know all the references to the fields you want to populate.

ie
doc.all(”field1″).Value = DATA.Cells(2, 2).Value
assuming your first value is in row 2 column 2 on a sheet called DATA
etc

Lee
29 August 2007, 8:25 am
Ben:

I need to automate a process where i go to a website (same every time) check one of 14 boxes (also always the same box) change a date to yesterday and the hit a submit button. The suggestions above have gotten me as far as opening the webpage and changing the date. how can I check the necessary box? thanks!
-Ben
11 September 2007, 9:55 am
Tushar Mehta:

Depending on the specifics of the webpage, you may be able to leverage the ideas at
Using VBA to access the ’Net
http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/index.htm
11 September 2007, 3:12 pm
Joshua Collier:

To Nat Beyer

I had the same problem with another wedsite. The end result was that I wrote a “wait” sub.

Private Sub Wait(Optional NumberOfSeconds As Integer)
If NumberOfSeconds = 0 Then NumberOfSeconds = 5 ‘default the value to 5
EndTime = Time() + (NumberOfSeconds * 0.0000115) ‘0.0000115 is the numeric value of a second
Do
Loop While Time()
21 September 2007, 5:55 pm
Joshua Collier:

Oops it appears ome of the code got cut off. The last two lines should say

Loop While Time() (substitute less than symbol here) EndTime()
End Sub

Now I just need to learn HTML. LOL.
21 September 2007, 5:57 pm
Matheus:

First of all, GREAT SITE!

I have no experience on writing codes like this and I need help to do the following. Can anyone help me?
Here is what I’m trying
to do:

1. access the following intranet page:
http://eqos.ssc.trw.com/bess/Default.aspx
2. On this page, click on “Login” link, that takes me to the following page:
https://eqos.ssc.trw.com/BESS/DefaultLoginPage.aspx
3. Then I enter my user name on a box, my password in another box and click
on “Log in”
4. Then, I go back to the http://eqos.ssc.trw.com/bess/Default.aspx page,
but now I see my name on the top of the page indicating that I’m logged in.
5. On this page, I click on a button called “HR” which takes me to this
page: http://eqos.ssc.trw.com/BESS/metrics/MetricSummaryView.aspx
6. On this page I click on a HR Metric called “General Turnover” that takes
me to http://eqos.ssc.trw.com/BESS/metrics/MetricTrendChart.aspx, and on here
I select from a drop down menu the option “By Organization”, which takes me
to http://eqos.ssc.trw.com/BESS/metrics/MetricViewByOrganization.aspx
7. Then on this page I click on “Expand All” so I can see the complete list
of sites that we have (around 160). Now I see all the sites’ information for
the metric General Turnover. And now this is the table I want to export but I
can’t!

The big thing is that I have 13 metrics that I need to do this. That means
that, after I perform steps 1-7 above, I need to change the metric on that
last page I referred by clicking on the metric I want, for example
“Absenteeism”, and the table is updated with the absenteeism information for
all the sites.

So now, I kind of need to do steps 1-7 then copy and paste tables 13 times!
Do you now of a way, if there is any, to do this automatically? I’ve talked
to my IT people and they will not work on a report that I can just download
data from all the metrics at once. HELP!!!
14 October 2007, 12:27 pm
Jeff:

I’ve got an issue that hopefully someone can help with. I am using VBA to automate IE. The automation clicks a link that downloads a .csv file. My question is how can I automate the message box that pops up asking if I want to Save/Open/Cancel the csv file? Further, if the Save option is chosen, I would like to automate the process of indicating where the file should be saved.

Thanks in advance for any help.
17 October 2007, 10:21 am
Nat Beyer:

To Matheus

On the Log On Section. I found that because Login and pass word change, alot. I write my subs, so they open up, to the log on page. Then I pause the macro until I manually make it through the log-on Steps. When you try to automate Log-in the security people get mad. And for the State atleast they are always changing how you Log on to differnet sites.

Sub Open_Explorer_Login_ANd_GOTO_Correct_Page()

‘Make sure you refereence internet Exploer see directions way at the top

Dim IeApp As InternetExplorer
Dim strLogOnAddress As String
Dim Str_URL_After_Click_HR As String

‘are Web Links On cold Servers
strLogOnAddress = “http://eqos.ssc.trw.com/bess/Default.aspx”
Str_URL_After_Click_HR = “http://eqos.ssc.trw.com/BESS/metrics/MetricSummaryView.aspx”

Set IeApp = New InternetExplorer

IeApp.Visible = True
IeApp.Navigate strLogOnAddress

‘Pause until page is fully loaded
Do Until IeApp.ReadyState = READYSTATE_COMPLETE
Loop

‘This Section Then Pauses the Macro Until you press a button called HR.
Do
Loop Until IeApp.Document.URL = Str_URL_After_Click_HR

‘Pause until page is fully loaded
Do Until IeApp.ReadyState = READYSTATE_COMPLETE
Loop

End Sub

After That your on your own. I can email you Excel workbooks with subs I made for automating IE at work, There maybe some sections of code that you can copy.
17 October 2007, 10:31 am
Jeff Byrne:

Hello

I have something like this:

vv_TestMode : BOOL (Description := “Virtual Validation$NTest Mode Bit”,
RADIX := Decimal) := 0;
InfotoCPL1_007Msg : MESSAGE (MessageType := CIP Data Table Write,
RemoteElement := “CPL1_001_Status”,
RequestedLength := 1,
ConnectedFlag := 1,
ConnectionPath := “Enet02, 2, xxx.xx.xx.xx, 1, 0″,
CommTypeCode := 0,
LocalIndex := 0,
LocalElement := “InfotoCPL1_007″,
CacheConnections := “TRUE”);
New_MSG : MESSAGE (MessageType := Unconfigured,
RequestedLength := 1,
CommTypeCode := 0,
LocalIndex := 0,
CacheConnections := “TRUE”);

I want to get the word InfotoCPL1_007Msg, which is no problem, however I am having trouble getting what’s between the 2 ( ) after MESSAGE, which spans multiple line. I can easily get the “MessageType := CIP Data Table Write” from the first line, but when I try to expand the match to get everything between ( ), it doesn’t match.

Any suggestions?
5 November 2007, 2:01 pm
Jeff Byrne:

Sorry, my last post deals with using regex.
5 November 2007, 2:02 pm
lucky:

Hi

I need to do some what like the code you have given.
I dun really understand the technical terms and I am a newbie in excel.
Here is what i am supposed to do:

I have to copy a value from an excel sheet and paste it to a textbox on a website.
and vice versa.

is there anyway for any of you to help me.. also expleining the technical terms.
I just started creating macros like a month ago.
3 December 2007, 9:59 pm
Julie:

Hi there,
>
>Thank you to everyone who has posted their ideas and suggestions to
this site!
>
>I am not a VBA programmer, and I am having difficulty deciphering some
of the code and tweaking it to my needs. I downloaded an add-in that
Randy Harmelink created, and I have had a little bit of correspondence
with him (please find that correspondence below.) I would like to
create a VBA script that will open this site:
https://investing.schwab.com/trading/start?kc=y and wait for the user
to input his/her username and password (and also change the “Start Page”
drop-down entry to “At a Glance”) and then click the “Log In” button.
Do I need to use some sort of “wait” code, plus other code that will
change the drop-down list entry and then click a specific button? I am
particularly interested in the code that Nat Beyer posted on Oct. 17.
Nat, if you (or someone else who knows how to create this code) would
post something back to me, I would really appreciate it!
>
>Thank you SO MUCH!
>
>Below please find my correspondence with Randy Harmelink:
>
>Randy Harmelink wrote:
>
> Actually, the add-in replaces web queries with user-defined
functions.
> I originally started writing it because of frustrations I had with
> EXCEL web queries.
>
> However, the add-in will have the same login issues. For example,
if
> I want to get data from Morningstar or Investors.com, I first need
to
> create a login cookie on either site.
>
> The add-in is located in the “Add-In Files” folder of the files
area.
> It’s a ZIP file containing the add-in and its supplementary files.
> You would extract all of the files into the “C:\Program Files\SMF
> Add-in” file folder on your computer. You would then need to
> activate the add-in in EXCEL.
>
> The add-in will not help you at all in recording a macro to do a
web
> query, since it replaces web queries instead of increasing their
> functionality.
>
> It may be possible to do the login to the web site using the
Internet
> Explorer object, but that would require some custom VBA
programming
> that depends on the web site and how one logs into it.
>
> On 12/5/07, Julie wrote:
> > I have a question about recording a macro to perform a web query
in
> > Excel. It seems to work fine if you go to a “regular” website;
but if
> > you go to a financial website that requires you to log in with a
> > username and password, the web query no longer works. I did a
search
> > for help online, and I came across a “user forum” that you had
posted
> > to. You suggested that someone use your add-in
> > (http://finance.groups.yahoo.com/group/smf_addin/) to solve his
> > problem. I’m wondering whether it may help me as well? When I
went
> > to your YahooFinance group site, I see the welcome message, but
I
> > don’t see any place to download your add-in. Do you think that I
will
> > be able to use your add-in to allow me to record a web query
into a
> > macro (including being able to log into the financial website to
pull
> > out the data)??
6 December 2007, 8:04 pm
Nat Beyer:

Julie, here is some info I did quickly its not perfect but it gets you to the pages, as I stated above I never code in passwords but you can. Also I use elements by ID number example element(0), it is better to use Element(”Login”), but I can’t really read web pages and it takes me a while to guess the names so I did Index numbers to save time.

Sub Swab()

Dim IeApp As InternetExplorer
Dim StrlogOnURL
Set IeApp = New InternetExplorer

StrlogOnURL = “https://investing.schwab.com/trading/start?kc=y”
IeApp.Visible = True
IeApp.Navigate StrlogOnURL
‘Pause until page is fully loaded
Do Until IeApp.ReadyState = READYSTATE_COMPLETE
Loop

‘This Line of coding Would Enter your login “6″
‘This line of coding is optional and probally should be entered by hand

IeApp.Document.Forms(”SignonForm”).elements(0).Value = “6″

‘This Line of coding Would Enter your Password “7″
‘This line of coding is optional and probally should be entered by hand
IeApp.Document.Forms(”SignonForm”).elements(1).Value = “7″

‘Element 3 is the Listbox

‘This loop Goes throught the StartPage ListBox and finds the index Number Which is 4
Dim X As Integer
Dim Str_At_A_Glance As String
X = 0
Do Until IeApp.Document.Forms(”SignonForm”).elements(2).Children(X).innertext = “At a Glance” Or X = 20
X = X + 1
Loop

‘This then selects the index # found from loop above
IeApp.Document.Forms(”SignonForm”).elements(2).selectedindex = X

‘This line of code Checks the box on the Login Save as Start Page
IeApp.Document.all(”SetAsPref”).Checked = True

‘This command Checks that box if you want it checked
IeApp.Document.all(”SetAsPref”).Checked = True

‘This button Press login if you want to automate it.
IeApp.Document.Forms(”SignonForm”).elements(4).Click

‘Pause until page is fully loaded
Do Until IeApp.ReadyState = READYSTATE_COMPLETE
Loop

End Sub
11 December 2007, 2:05 pm
Albert:

I would love to know how to write Excel VBA code that will automate logging in to email accounts for both my work and personal email. The personal email is yahoo.

The work email is https://webmail.nyc.gov. When the page loads a pop-up box comes onto the screen into which you have to enter the id and password and then click the OK button.

I have had a couple of years experience working with Excel VBA. Great forum.
9 January 2008, 1:09 pm
MK:

Hi- I tried Neon Angel’s suggested functions in VB, but they go unrecognised (shame- what version is this meant to be for?)>
What I want is something like this - to read specific data off a table on a web page and store it, into a variable, in visual basic.
I also want to be able to do the revers- read data into cells on the URL.
Tricky as I have not found a way I can do this without copying tables into Excel, which I do not want to do- I want to keep my programme robust.

I appreciate your considerations. I believe the answer to this question will also help the last person that made an entry on this forum.
11 January 2008, 9:53 am
antony:

very helpul thankyou
21 January 2008, 6:35 pm
Finklehelm:

I’m using NeonAngel’s code but get a compile error with this statement:

Dim Element As HTMLButtonElement

The comple error is “User defined type not Defined”

Is anyone else getting the same thing? Any pointers?
28 February 2008, 12:49 pm
Dick Kusleika:

Finklehelm: Set a reference to the Microsoft HTML object library (Tools - References in the VBE)
29 February 2008, 7:15 am
Matthew Pfluger:

I would like to open a browser and save an image to a file. I used code above to locate the image, but I can’t figure out how to simulate the “right-click” and “save picture as” command. Can anyone suggest how to do this?

Thanks,
Matthew Pfluger
29 February 2008, 8:58 am
Juan Pablo Gonzalez:

Matthew, you can use the code from this post instead.

http://www.mrexcel.com/forum/showthread.php?t=116387

You can download the image directly without using IE.
29 February 2008, 10:38 am
Matthew Pfluger:

Correct me if I’m wrong, but doesn’t that code only apply if the image itself is the website? The image I want to download is an imbedded animated gif, yesterday’s national radar loop from intellicast.com. When you left-click on the image, it zooms in on the image. Right-clicking, however, gives you the option to Save Picture As… When the image is saved this way, it is saved in the correct format and opens nicely.

So, how can this be automated? Thanks.
Matthew Pfluger
3 March 2008, 7:57 am
Jon Peltier:

Matthew - The image has an URL, that’s all you need. http://SomeSite.com/SomeFolder/MyPicture.gif
3 March 2008, 9:42 am
Matthew Pfluger:

Juan & Jon,

Thank you for clarifying that. I added a Watch to the IE object, found the image, found its URL, and was able to modify the code to download the image without a complicated IE object. That’s awesome, thanks for the help.

My plan now is to automatically download yesterday’s radar loop each time I log on to my computer. Then, I’ll build a video of each frame over many days, showing the radar loop for the country over an extended period of time.

Thanks for the help, everyone!
Matthew Pfluger
3 March 2008, 10:09 am
Paco:

great code Nat and thanks to Julie for suggesting an example like swab (as i am trying to do the same for fidelity.com and thanks to this code i managed to get ie to logon to my fidelity account.

Nat, you mention on the post that you have some excel workbooks with subs that you have used to automate ie at work,… is there any chance you could email me some of the code,…

i ultimately would like to get real-time trading prices from fidelity (the data is placed in tables) and paste it into a workbook in excel. (i would like to get this done this way so that people here at work don’t think i’m logged on to fidelity all day long and instead I used an excel spreadsheet).

thanks
Paco
macutan@hotmail.com
5 March 2008, 6:24 pm
Amit Saxena:

Hi All….. this is my favourite blog for VBA .. coould do a lot of stuff reading the inputs from all of you. But I am not stuck on one small problem. I m opening up this page https://www.ultimatix.net in I.E. 6.0 and the very first this appears is a security pop up window where i need to click yes in order to procedd further.
And i m not able to do this using the VBA script.

Any help is highly appreciated.
17 April 2008, 8:02 am
WIll:

I cannot see the reference on my reference list…. any ideas why?
17 April 2008, 2:59 pm
suresh k:

hi,
i am unable to click on the radio button
could u please help me with the code
24 April 2008, 12:02 am
suresh k:

hi
how can i select multiple values from the drop list
using VBA
24 April 2008, 12:04 am
kasi:

hi Ross

How do i click on the hyperlink text uing VBA
12 May 2008, 2:47 am
Steven:

Hi all, I have a macro which launches and interacts with IE but clicking a button opens a new window. Does anybody know how to reference to this new window?

I know a few other people have asked this question on this blog and it seems to have been ignored in the past, but I would really appreciate it if someone could provide a solution.

Thanks in advance
13 May 2008, 7:42 am
Jon Peltier:

Steven -

This isn’t the place to come looking for technical help. This is where Dick and others hold discussions of interesting topics. Here are a few places to find help:

1. Google. Search based on a few keywords. If this doesn’t get what you need:

2. Microsoft Newsgroups. Add a news account in your NNTP newsreader (for example Outlook Express) using the news server msnews.microsoft.com, and subscribe to the Excel groups. Pick a relevant one (probably .programming) to ask this question, and be sure to include the code that isn’t working the way you expect, as well as a description of what you expect to happen and what happens instead.

3. MrExcel.com. Sign in to the Excel Questions forum, search for keywords (Google includes MrExcel in its searches), then post a question, again clearly worded, with substantial descriptions of the issue.
13 May 2008, 11:18 am
sania:

hi All
I would require your help
i am writing a VBA program,
let us take an example of yahoomail, if i enter the correct id and wrong pwd, it will give an error saying, please enter the correct pwd, or id

can i know how to read this string

if this error comes i need to add another used id and pwd
could help me with the code
19 May 2008, 4:22 am
Ginger:

Hi I have a similar problem and would appreciate any advice - where my problem is different is that the IE window already exists with the data required to be copied to XL.
The HTML already contains a button that opens the XL file (this is a reporting mechanism) - the XL file runs the macro on opening. Currently I have to “^a” then “^c” to get all the data from the IE page, then I select the Xl file button on the same page. I am finding it real hard to get XL to switch back to the already populated window and make it active. I guess once I do that, the rest is easy.

Did lots of looking around and everything I find that is relevant always assumes the user wants to open a new instance of IE. I don’t. :O) Any ideas please? Many thanks in advance!
22 May 2008, 3:25 pm
Ginger:

I just read this back and wasn’t very clear. Currently I have to hit Ctrl+”a” (select all) then Ctrl+”c” (copy) on the IE page. The I open the XL file with all my macro stuff auto running. The first thing this macro currently does is to paste whatever is in the copy buffer to a worksheet - then it does all the clever reporting stuff.

This works fine but it’s a bit clumbersome - what needs to change is that I want the Macro to do the Ctrl+”a” and Ctrl+”c” (or equivalent) and I know sendkeys will work here. If I can do this then it’s one button press - far more professional.

The only thing I can’t do is to get XL to open the existing IE window so I can ’sendkeys’ to do that part. I can only see similar posts for “create.object” related to IE, and nothing for “get.object” (or whatever will work).

I’m a nubie to all this so be gentle with me :O) Cheers all
22 May 2008, 3:31 pm
Dick Kusleika:

Ginger: http://www.dailydoseofexcel.com/archives/2008/05/23/giving-ie-the-focus/
23 May 2008, 7:32 am
Mark Merrigan:

Jose Luis:

Try these great reads:

Excel 2003 Power Programming with VBA, John Walkenbach

and

Writing Excel Macros, Steve Roman

Good luck to you,
~Mark
26 May 2008, 12:04 pm

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Automating Internet Explorer

108 Comments

ross:

Juan Pablo G:

ross:

Ken:

Mpemba:

Tushar Mehta:

M. Mendez:

John F. Jackson:

jose luis sainz:

Jake Marx:

John F. Jackson:

jose luis sainz:

Neon Angel:

Neon Angel:

Mpemba:

John F. Jackson:

Gates Is Antichrist:

Ruud van der Linden:

peter gooding:

Jake Marx:

prashanthy:

Nate Oliver:

Nate Oliver:

peter gooding:

Anonymous:

Leo Barnhoorn:

Chrispy:

Juan Manuel:

Robin Lavoie:

Guy Carnegie:

Guy Carnegie:

Guy Carnegie:

Simon:

Simon:

Simon:

Simon:

Benjamin Compton:

Benjamin Compton:

Benjamin Compton:

Jaime Givens:

Benjamin Compton:

Brian Nicoloff:

Simon:

Beth Trainer:

Ashok:

JRizzle:

A. Favre:

brettdj:

John Weirich:

Sean:

jkpieterse:

chourour:

Chris Bezant:

Ashok:

david nguyen:

KG:

gk477:

Chris:

Satsco:

Bryan.Schmidt:

Lou Wilson:

John Foulger:

Nat Beyer:

Dick Kusleika:

Nat Beyer:

simus:

IMTIAZ:

Sachin:

PR:

ruben:

Nat Beyer:

Aaron:

Lee:

Ben:

Tushar Mehta:

Joshua Collier:

Joshua Collier: