VBA Web Automation

Question

I'm trying to write a macro to access data from a web portal, where I need to launch the website and click an 'open in excel' button.

I've launched the webpage fine, but I can't not click the link. My understanding is I should be able to do something similar to this:

BrowswerObject.Document.ElementID.Click

but I'm not sure what the correct element and coding would be. One the webpage when I inspect the element it has this code:

<A onclick=ur_Button_click(event); tabIndex=0 id=BUTTON_TOOLBAR_STANDARD_btn7_acButton title="Open in Excel" class=urBtnStd onkeydown=ur_Button_keypress(event); style="OVERFLOW: visible; TEXT-ALIGN: center" href="javascript:void(0);" ct="B" ti="0" st="" ocl="sapbi_page.sendCommandArray([['GUID','11',0],['NOUILOCK','X',0],['BI_COMMAND_TYPE','ABSTRACT',0]],event);">Open in Excel</A>

Could anyone pinpoint what from that I should focus on for this?

Edit: I've tried the getElementByID but I'm running in to this error: Method ‘Document’ of object ‘IWebBrowser2’ failed

by sample code is:

Dim ie As Object
    Set ie = CreateObject("Internetexplorer.Application")
    ie.Visible = True
    ie.Navigate *Link*

    ie.document.getElementById("BUTTON_TOOLBAR_STANDARD_btn7_acButton ").Click

You can you getElementbyID. Browser.Document.getElementbyID("BUTTON_TOOLBAR_STANDARD_btn7_acButton").click — Ryan Wildry
– Ryan Wildry, Commented Jul 6, 2016 at 11:57
thank you, I've tried this but I'm running in to an error with the document method not being recognized. I've updated to include my sample code, do you know how to solve this? — Erik Rasmussen
– Erik Rasmussen, Commented Jul 6, 2016 at 16:49
(1) add the "Microsoft Internet Controls" reference per this and Dim ie as SHDocVw.InternetExplorer: set ie = new SHDocVw.InternetExplorer rather than using generic Object. (2) Use a NavigateError event to see if Navigate actually succeeds. (3) In the VB editor, set a breakpoint on the getElementById line and use the Watch window to see if ie.Document is actually a valid Object when you try to call getElementById. — cxw
– cxw, Commented Jul 6, 2016 at 17:05
Add delay after .Navigate untill IE is not busy, docunent is ready and target element is not null. — omegastripes
– omegastripes, Commented Jul 19, 2016 at 7:00

QHarr · Accepted Answer · 2018-06-08 12:43:18Z

1

Depending on how many similar elements there are on the page you could try a CSS selector

The following would select your bit of HTML

a[onclick*='ur_Button_click(event)']

CSS query

VBA:

ie.document.querySelector("a[onclick*='ur_Button_click(event)']").Click

If there is more than one matching item you would use:

ie.document.querySelectorAll("a[onclick*='ur_Button_click(event)']")

to return a nodeList of matching items and then click the appropriate item by accessing through index.

Info on CSS selectors

answered Jun 8, 2018 at 12:43

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ASH · Accepted Answer · 2016-07-14 11:47:26Z

You are right, it should be something like this . . .

For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next

Read more at http://vbadud.blogspot.com/2009/08/how-to-login-to-website-using-vba.html#gurMeXG3KmWvFUMx.99

The best way to see the ID name of the button is to hit F12 when you are on the site. If you use Firefox and F12 does nothing, download Firebug.

https://addons.mozilla.org/en-US/firefox/addon/firebug/

If you use IE and nothing happens when you hit F12, download and install the IE Developer Tool.

https://msdn.microsoft.com/en-us/library/bg182326(v=vs.85).aspx

These tools will allow you to easily see the code behind the page and everything is highly interactive too, so these things are really easy to use.

Good luck!!

Collectives™ on Stack Overflow

VBA Web Automation

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related