How to automate a dynamic web page using VBA?

May 9, 2012 10:32 AM

Help me automate some of my work involving taking values in a spreadsheet and plugging them into a website with dynamically generated content.

I need someone to point me in the right direction. I have several tasks which are very mechanical which I would like to automate. One, for instance, involves taking a list of product numbers from a spreadsheet, entering them one at a time to a website, then printing the resulting page.

I have tried to automate this via VBA, and I can get as far as loading the page and entering the product number into the correct form field, but there is actually a step before this where a drop down needs to be selected before the product number field is active. I am actually able to set the value of this drop down, but doing this doesn't cause the page's javascript to enable the product number field and product number submit button which then ignores my attempt to programatically click it.

Basically the page I want to use is dynamically generated, but the parts I want don't show up until after the necessary info has been entered, and I can't get the page to recognize that I've automatically entered it. Further complicating the situation is that the web page I'm working with is generated by ASP (I think) which I'm not familiar with and which is inserting a lot of strange script tags with long strings of encoded data.

Any help or pointers in the right direction would be greatly appreciated.

posted by Reverend John to Computers & Internet (6 answers total)

I point you in the following direction but can't say if it is the right one or not: don't worry so much about the page with the form as the one that processes it. Look at the action of the <form> element and the names of the <input> elements and send an http POST request directly to that action with values for the names. In other words, skip the step where a bunch of javascript creates the product number field altogether; just figure out what that field is called and populate it with a value.
posted by Dano St at 10:49 AM on May 9, 2012

If you can tease out the name of the function that picking a drop-down option invokes to enable the product number and submit button, you could do something like

IE.document.all.Item 
Call IE.document.parentWindow.execScript("<Name of Drop-down function>", "JavaScript") 

posted by ob1quixote at 10:50 AM on May 9, 2012

Response by poster: For what it's worth, here's my code:

Sub PrintAttributeSheet()
'
' PrintAttributeSheet
' print attribute sheets
'
' Reverend John 20120416
'

Dim myRange As Range
Dim sku As String
Dim myBrowswer As SHDocVw.InternetExplorer
Dim supplierEl As Object
Dim skuEl As Object
Dim goEl As Object
Dim printEl As Object

Set myRange = ActiveWindow.RangeSelection

sku = myRange.Value

Set mybrowser = New SHDocVw.InternetExplorer

mybrowser.Visible = True
mybrowser.Navigate "http://example.com/Product.aspx"

Do While mybrowser.Busy
Loop

Set supplierEl = mybrowser.Document.getelementbyid("ctl00_content_SupplierDropDown")
supplierEl.Value = "700"

Do While mybrowser.Busy
Loop

Set skuEl = mybrowser.Document.getelementbyid("ctl00_content_txtbxSrchItem")
skuEl.Value = sku

Do While mybrowser.Busy
Loop

Set goEl = mybrowser.Document.getelementbyid("ctl00_content_btnsrch")
goEl.Click

End Sub

The code was adapted from various website examples.
posted by Reverend John at 10:50 AM on May 9, 2012

Best answer: If you don't end up getting the IE automation through VBA working there are other screen scraping methods/libraries you can try. I tend to use Mechanize for Python to automate browser stuff (and BeautifulSoup for parsing if necessary). Mechanize doesn't actually use a real browser and doesn't support javascript, but in most cases I can figure out what the javascript is doing and simulate it (as Dano St mentioned for forms specifically you can save a lot of headaches by just sending the POST request directly with the correct data fields included). If you do need actual browser automation there's also Selenium, although I haven't used it myself.
posted by burnmp3s at 11:48 AM on May 9, 2012

Best answer: Also if it's not obvious what exact requests are being sent from the browser you can install a sniffer like Fiddler and look at the traffic directly. Generally no matter how convoluted the javascript on the page is, it usually ends up resulting in relatively simple POST data in the end.
posted by burnmp3s at 12:04 PM on May 9, 2012

Maybe try using IE as a COM object, not the ShDocvw option? This creates a full instance of IE, rather than the stripped down version used in controls, web queries etc?

e.g. http://www.excely.com/excel-vba/ie-automation.shtml
posted by Boobus Tuber at 11:54 AM on May 13, 2012