One of the most popular pages on this site shows code for automating Internet Explorer. However I have only tested the code in Excel XP/2003. The purpose of this post is to credit a gentleman named Durand Sinclair who, using Excel 2007, pointed out to me that the name of the IE object library in Excel 2007 is actually called "Microsoft Browser Helper" (click Tools>References in the VBE). Once you select the checkbox, exit the dialog box and re-enter it, the name changes to "Microsoft Internet Controls".
If anyone else would like to verify this behavior in Excel 2007 and let me know, I would be grateful to hear it.
Thanks Durand!
–JP
About JP
40 Comments:
These references are not part of any Office library, but show all possible libraries installed on a computer. These may or may not be appropriate for a version of Office.
Microsoft Internet Controls (C:\Windows\system32\shdocvw.dll) is different from Microsoft browser Helpers (C:\Windows\system32\browseui.dll). The former contains the Internet Explorer interface, the latter seems more related to handling of multimedia content.
I'm getting the impression that not much has changed in Excel 2007 (programmatically), other than the drastic user interface changes. Since I don't have a copy I'm not able to verify. Thanks Jon.
I have excel 2003 at work, and 2007 at home. I found that Microsoft Internet Controls wasn't automatically in my reference library in 2007, so I had to browse to C:\Windows\system32\shdocvw.dll and add it. After that, everything worked perfectly.
there is one Additional Details button, please tell me the object name to access the additional details button from vba.
how to access an image control (click event) of a website from vba. can anyone find that for me. vba code like
IeApp.Document.all.track.Click
worked for me but not the image control. can anyone please find for me?
Prem,
If you are trying to programmatically click a button on a web page, you'd need to view the source of the page to find out the name of the control, then set an object reference to that control and use the Click Method to programmatically "press" the button.
There is some sample code here you might adapt for your purposes:
http://www.jpsoftwaretech.com/AutomateInetExplorer.html
Thx,
JP
Hi JP, thanks for your prompt reply. I could find only the image control from the source code of that page. i need to have the click event for the button_addl_details which is an image control.
please find me a solution in having the click event on the button_addl_details.gif.
hi JP, my previous post deletes the html code which i wanted to get to you.
this is the portion of the code
input type="image" src="images/button_addl_details.gif"
how to have a click event on this image control? please help me.
Using the link I provided above, the code you need is something like:
Set ElementCol = appIE.Document.getElementsByTagName("INPUT") For Each btnInput In ElementCol If btnInput.src = "images/button_addl_details.gif" Then btnInput.Click Exit For End If Next btnInputBasically you are looking at all of the page elements called "input" (of which that button is one) and looking at the "src" tag. If it matches, click it.
HTH,
JP
Hi JP, thanks again for giving me something useful. However, the btnInput.src is not satisfying with any, resulting into not executing btnInput.Click. I will give you specifically what i am looking for. That is;
Please open http://www.usps.com and you fill find the Track & Confirm text box on the right side.
type this 16 digit tracing # 03062400000276026378 and then press Go
you will get Track & Confirm sheet where you will find "Additional
Details>" image control in red.
I need the click event for that image control which i need to access from vba.
Hope you understood what i am looking for.
What code have you got so far?
Sub ListLinks()
Dim IeApp As InternetExplorer
Dim IeDoc As Object
Dim i As Long
Dim MyScreen As Object
Dim ImageCon As Variant
Dim Name As String
Set IeApp = New InternetExplorer
IeApp.Visible = True
IeApp.Navigate "http://www.usps.com"
Do
Loop Until IeApp.ReadyState = READYSTATE_COMPLETE
Set IeDoc = IeApp.Document
IeApp.Document.all.trackField.Value = "03062400000276026378"
IeApp.Document.all.trackGo.Click
Do
Loop Until IeApp.ReadyState = READYSTATE_COMPLETE
IeApp.Visible = True
AppActivate IeApp
Set ElementCol = IeApp.Document.getElementsByTagName("INPUT")
For Each btnInput In ElementCol
If btnInput.src = "images/button_addl_details.gif" Then
btnInput.Click
Exit For
End If
Next btnInput
Set IeApp = Nothing
End Sub
Try this:
Sub ListLinks() Dim IeApp As InternetExplorer Dim IeDoc As Object Dim i As Long Dim MyScreen As Object Dim ImageCon As Variant Dim Name As String Dim imgbtn As MSHTML.HTMLButtonElement Dim ElementCol As MSHTML.IHTMLElementCollection Set IeApp = New InternetExplorer IeApp.Navigate "http://www.usps.com" Do Loop Until IeApp.ReadyState = READYSTATE_COMPLETE Set IeDoc = IeApp.Document IeApp.Document.all.trackField.Value = "03062400000276026378" IeApp.Document.all.trackGo.Click Do Loop Until IeApp.ReadyState = READYSTATE_COMPLETE Set ElementCol = IeApp.Document.getElementsByTagName("INPUT") For Each imgbtn In ElementCol If imgbtn.Name = "Additional Details" Then imgbtn.Click Exit For End If Next imgbtn IeApp.Visible = True Set IeApp = Nothing End SubHi JP,
Its working, with putting the system into the wait mode for atleast 5 seconds before reading the input for imgbtn, otherwise it never reads it.
Say, i have around thousand track numbers for usps.com which i need to get the status of each and everyone. suppose, if the internet gets bit down for a moment when the macro runs, resulting into not reading some from the source code, how will i temporarily stop executing the macro till the internet gets up.
will this IeApp.ReadyState = READYSTATE_COMPLETE sorts this problem?
please explain.
You could add a timer inside the Do Loop that breaks out (and displays error message) if there is a long delay. For example
This is just air code so you'll need to test it out to see how long the code should wait (the "If Timer – startTime = 10000" statement)
Enjoy,
JP
Hi JP,
Its great to have your code working, thanks a lot. Now how to fix "Page Cannot be displayed" while running the macro, some sort of trap is required to read page cannot be displayed. Is there any way to read page cannot be displayed? Please help.
Prem,
"Page cannot be displayed" is text on the screen. The link I gave you has sample code for assigning the text of a webpage to a string variable. After loading each page, you would need to check for that string inside the webpage body. What function would you use to search for one text string inside another?
–JP
No JP,
I don't have any code for finding the Page Cannot be displayed.
It's on the page I provided in my first reply.
Declare a string variable and, after each page loads, assign "IeApp.Document.body.innerText" to it.
To check for "Page cannot be displayed", use the Instr Function as follows:
Hi JP, great, everything is working fine, thanks a lot and please let me know how to print the web page that i had opened from vba.
i want to print the web page to the pdf writer which converts the web page to a pdf format file. also, i want to save that file with a name from my excel file.
please help me in this.
I am trying to run the code but it is opening a new window instead of the main appIE object window when I use the Navigate method. I am using IE7. I appreciate any help.
AD
The code will always create a new window. You won't be able to use the existing window.
HI JP,
I was reading the VBA code that you revised. But I got lost on where did you get the "Additional Details" string? I tried to view the source but I can find any. Can you helpme understand this?
Thanks
Paul,
"Additional Details" was the text of the button on the usps.com webpage after entering a tracking number. That button doesn't exist on usps.com anymore; I think they changed their page.
hi jp… i'm having a problem clicking an image link using vba in excel..
here's the the specific part of the code:
please help me make a code to click the said image…
thanks,
sorry jp.. i didn't read your instruction above on how to post my vba code.. here's the code
There some sample code at Automate Internet Explorer that should help.
Also, there is a snippet in the comments above that shows how to check the src property for a given string, although you might have to change the first line to
appIE.Document.getElementsByTagName("img")
hi jp.. thanks for your prompt reply.. i've already tried your samples and the snippet above for src but still the code does not work. i can enter the name but i can not click the "submit or get name button".. i am not a programmer… is there any other way to make the code working?
What specifically did you try that didn't work? Post your code and indicate what line isn't producing the expected result. Also, what is the URL and what are you trying to do?
jp.. i've inserted debug.print to see the values at btnInput.src and the output are numbers.. i thought the output will be the value of src in text.. here's the code that i've copied from your samples..
Set ElementCol = myIE.Document.getElementsByTagName("img") For Each btnInput In ElementCol If btnInput.src = "/images/getname1.gif" Then Debug.Print btnInput.src btnInput.Click Exit For End If Debug.Print btnInput.src Next btnInput output from debug.print 2127 673 905 869 517 822 836 792 318hi jp… finally i got it by trial and error..
i've used debug.print to look for clues.. here's the final code that reveal the "javascript:void(0);", then i manually search it on the source code to know how many buttons used the said code and enter the equivalent btnInput.src number..
Set ElementCol = myIE.Document.getElementsByTagName("img") For Each btnInput In ElementCol Debug.Print "1:" & btnInput.src Debug.Print "2:" & btnInput.parentElement Debug.Print "3:" & btnInput.nodeName Debug.Print "4:" & btnInput.alt Debug.Print "" If btnInput.src = "607" Then Debug.Print "my" & btnInput.src btnInput.Click Exit For End If 'Debug.Print btnInput.onclick Next btnInputfor trial and error, i 1st set
to
Is there any way we can get the VBtexteditor(Javascript enabled) IE text boxes automated through Excel VBA. I have searched for a long time I was not successful.
Thanks in advance
How can I verify (validate) the web page title of the page ?
When i'm trying to create set objIE=new InternetExplorer it hangs the application, pls suggest. I have properly checked the reference option.
What is the exact line as you're using it?
How is objIE declared?
Appreciate for you fast response
Before that I need to give you some background, Actually on my office machine I have Excel2003 and 2007 both installed side by side, I have tried in both version but same problems it hangs. Even though when I reference Microsoft Internet Control the Location it shows (C:\Windows\system32\shdocvw.dll) in both version. So i suspect that file reference is nor appropriate. Because I tried on my personal laptop that has only Excel 2007 and when I created the reference the dll location and filename was different (Actually I cannot remember now but I can post you later). So I think problem may be due to file conflict, please suggest.
Actually I'm trying to integrate InternetExplorer feature withing excel, that is one of my clients requirement and he wants to download the excel file from IE without navidation to IE then download and import in excel.
When I say hangs actually I'm getting loop error
"Microsoft office Excel is waiting for another application to complete an OLE automation"
so I added code Application.DisplayAlerts = False but it totally hangs
Below is the code copied from your blog,
Sub GoToWebSite()
Dim appIE As InternetExplorer
Dim sURL As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set appIE = New InternetExplorer (Code Hangs here and i have to kill application can't do anything)
sURL = "http://www.jpsoftwaretech.com"
With appIE
.Navigate sURL
.Visible = True
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Set appIE = Nothing
End Sub
Please let me know if you need further details, thans in advance
Here is the Excel 2007 Microsoft Internet Controls reference of file details.
Location: c:\windows\system32\ieframe.dll
If you're only trying to download a file from the Internet, you can do so without the costly overhead of automating IE.
There's an API called URLDownloadToFile you can use to download a file from the Internet to your local computer: URLDownloadToFile.
You can also use a web query, see Refresh Web Data into Excel Every Minute for assistance. The exact process depends on your Excel version.
Actually I have to push some data from Excel to Web page for searching the result, that generates the link on the webpage to report.xls file. And if you click on this link a pop up shows to download file. All this functionality i want to automate in excel.
Please suggest, i don't know how to deal with popups after clicking on hyperling in the navigation web page from excel.