Excel VB Macro to scrape webpage. Can't code to click html button

Question

I have a short excel macro that is designed to: 1) Open Internet Explorer and navigate to "http://www.puco.ohio.gov/pucogis/address/search.cfm" 2) Fill out a form on that site with data from the excel workbook 3) Click a button to submit the form 4) Scrape some innertext from the website and place it in a cell in the workbook 5) Close Internet Explorer

I can not get step 3 to work. That is, I can not get the click/submit function to work with this website. When the button is clicked the website populates with information specific to the information entered in the form. Everything else in the code is working. I have searched for an answer and tried the submit verses click approach with no luck.

Thanks for you help.

Code below:

Private Sub SiteData()

Dim ie As Object
Dim utility As Variant
Dim HTMLButton

Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "http://www.puco.ohio.gov/pucogis/address/search.cfm"
ie.Visible = True

While ie.Busy
DoEvents
Wend

ie.Document.all("address").Value = ThisWorkbook.Sheets("Site Info").Range("D14")

While ie.Busy
DoEvents
Wend

Set HTMLButton = ie.Document.getElementsByTagName("input")(1)
HTMLButton.Click

While ie.Busy
DoEvents
Wend

Set utility = ie.Document.getElementById("supName")
ThisWorkbook.Sheets("Site Info").Range("D50") = utility.innerText

ie.Quit
Set ie = Nothing

End Sub

Are you sure you don't need `ie.Document.getElementsByTagName("input")(6)`? I believe this is the "Search" button next to the address input field (verified against the .OuterHTML). The same problem persists, but let's make sure you're getting a handle on the correct button... — David Zemens, Oct 05 '16 at 15:37
Thanks! You are right. I Was working on a similar thing with a different site and found that the Input tag was not the number I was expecting and went back and found that Input 6 worked as well. Thanks for your help. — CJones, Oct 07 '16 at 13:56

score 0 · Accepted Answer · edited May 23 '17 at 12:34

Try this solution, which I found from this answer to a similar question. That answer was not accepted, but I have tested this with your code and seems to be working.

Private Sub SiteData()

Dim ie As Object
Dim utility As Variant
Dim HTMLButton

Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "http://www.puco.ohio.gov/pucogis/address/search.cfm"
ie.Visible = True

While ie.Busy
DoEvents
Wend

ie.Document.all("address").Value = ThisWorkbook.Sheets("Site Info").Range("D14")

While ie.Busy
DoEvents
Wend

Call ie.Document.parentWindow.execScript("codeAddress()")


While ie.Busy
DoEvents
Wend

Set utility = ie.Document.getElementById("supName")
ThisWorkbook.Sheets("Site Info").Range("D50") = utility.innerText

ie.Quit
Set ie = Nothing

End Sub

If you don't know or can't reasonably anticipate the function call codeAddress(), then you can try something like this to derive it from the button's onclick property:

Dim fn$
fn = HTMLButton.onclick
fn = Mid(fn, InStr(fn, "{"))
fn = Trim(Replace(Replace(Replace(fn, "{", vbNullString), "}", vbNullString), vbLf, vbNullString))
Call ie.Document.parentWindow.execScript(fn)

Thanks David. I had the incorrect handle for the input button i wanted to click so I was able to solve it by getting the correct number. This code appears to work as well. Thanks for your help. — CJones, Oct 07 '16 at 14:02

score 0 · Answer 2 · edited May 23 '17 at 10:34

You can call the JavaScript directly. try this it will work

Instead of:

Set HTMLButton = ie.Document.getElementsByTagName("input")(2)

HTMLButton.Click

use

ie.Document.parentWindow.execScript code:="codeAddress()"

note that IE may prompt you to confirm every run so you may need to stop showing this message for smooth operation

Private Sub CommandButton1_Click()


Dim ie As Object
Dim utility As Variant
Dim HTMLButton

Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "http://www.puco.ohio.gov/pucogis/address/search.cfm"
ie.Visible = True

While ie.Busy
DoEvents
Wend

ie.Document.all("address").Value = ThisWorkbook.Sheets("Site Info").Range("D14")

While ie.Busy
DoEvents
Wend

ie.Document.parentWindow.execScript code:="codeAddress()"
'Set HTMLButton = ie.Document.getElementsByTagName("input")(2)
'HTMLButton.Click

While ie.Busy
DoEvents
Wend

Set utility = ie.Document.getElementById("supName")
ThisWorkbook.Sheets("Site Info").Range("D16") = utility.innerText

ie.Quit
Set ie = Nothing

End Sub

thanks also to this article helped me to solve your problem How to find and call javascript method from vba

This would appear to be a duplicate solution to the answer I posted 15 minutes ago... — David Zemens, Oct 05 '16 at 15:59
yes I saw the issue and while preparing solution you posted yours then I posted before seeing that you already posted sorry for that duplication. — user2163008, Oct 06 '16 at 17:56

Excel VB Macro to scrape webpage. Can't code to click html button

2 Answers2