Geek Answers Handbook

Import a table from the Internet into Excel using VBA

Wrote some basic VBA that connects to the website, enters the username and password on this website, logs in, then copies the table and pastes it into excel. Now I realized that my table contains graphics with links. Instead of copying the previously mentioned table, I want to follow these links differently and copy the tables referenced by this link into excel.

However, these graphs in the web spreadsheet do not contain separate direct hyperlinks. I looked at the source of the webpage and contains Javascript. (See below)

<a href="#" onclick="var a=function(){javascript:window.open('','ProcStatus','top=50,left=' + (screen.width - 750) + ',width=700,height=500,resizable,status,scrollbars');};var b=function() {if(typeof jsfcljs == 'function'){jsfcljs(document.getElementById('MainPage'), {'j_id202:319:j_id208':'j_id202:319:j_id208'},'ProcStatus');}return false};return (a()==false) ? false : b();"><img src="image.gif" alt="View Details" style="border: 0;" title="View Details" /></a>

I see the functions that they call in the source code, and bearing in mind that I know zero about Javascript, how will my VBA code start executing / opening these links, and then move on to the second row in the table, to the next link? Basically, how to populate the variables needed for an onclick event and then activate this hyperlink to open a new web page?

And then each subsequent link in this table?

+4
source share
1 answer

You can call the JavaScript function from VBA using the following:

 Call ie.document.parentWindow.execScript("functionName()", "JavaScript")

In your case, it will look like this:

 Call ie.document.parentWindow.execScript("var a=function(){javascript:window.open('','ProcStatus','top=50,left=' + (screen.width - 750) + ',width=700,height=500,resizable,status,scrollbars');};var b=function() {if(typeof jsfcljs == 'function'){jsfcljs(document.getElementById('MainPage'), {'j_id202:319:j_id208':'j_id202:319:j_id208'},'ProcStatus');}return false};return (a()==false) ? false : b();", "JavaScript")

Alternatively, you can also tell your macro to click the link using the DOM methods. You will have to dig through the source code to find out exactly where the element is located, but in general the code will look like this:

 ie.document.getElementsByTagName("a")[3].click

Where 3 is the index of the array of elements "a".

You do not need to know a lot of javascript to be effective in webscraping, but it helps to know some basic DOM methods.

  • document.getElementsByTagName - Finds elements with a given tag, such as table , a , td , tr , div , etc.
  • document.getElementsByName - Finds elements with the given name
  • document.getElementById - Finds elements with the given id
  • element.innerText - returns the text of the element
  • element.innerHTML - returns the HTML of an element
  • element.click - clicks on an element
  • element.getAttribute ("attribute") - returns the value of this attribute, for example href or style
+1
source

More articles:


All Articles