Webscraping QueryTables to variable

Hi,

I have been trying to scrape a webpage without any success using xml and so have reverted to the basic QueryTable. The code below works, but I would like to speed it up by saving the results to a variable rather than writing it to the sheet then converting the range to a variable.

Please Login or Register  to view this content.

Also would a connection to something like ADO be a better option?

abousetta

*** bump ***

*** bump ***

Hello Doc,

Do you want the text only or do you also want the link URLs? Will this be stored to an array?

Hi Leith,

I'm trying to extract the text of Entry Terms (i.e., between "Entry Terms:" and "Previous Indexing:"). I tried using xml, but the response text didn't contain the entry terms, but when viewed in IE or the querytable, the terms come up.

Thanks.

abousetta

Hello Ahmed,

This is a macro I wrote last month to scrape web pages quickly. It scrapes the data between the tag name you supply. In this case, your data in a List. The macro will return all text in all List elements. The Lists your are after are embedded in Paragraph elements.

The macro ParseWebPage is set to the URL you posted. This macro can be modified to be more specific by not using recursion but its results would depend on the web page layout being consistent. I hope it will be of some use to you.

Please Login or Register  to view this content.

Hi Leith,

Yes, this is great. This is my first time to see someone use xml to create a DOM from the html text.

I just commented out Case 1 and it works well.

Thanks again.

abousetta

Hello Ahmed,

You're welcome. Well I am glad it was an easy fix.

Hi Leith,

Yes, it was very helpful. Below is the final code I settled on.

abousetta

Please Login or Register  to view this content.

Hello Ahmed,

Like you, it was making crazy that there weren't any examples or information on how to convert the HTML source text into HTML DOM objects. It took me about a month of research an testing to "discover" how to to it. I was beginning to believe it wasn't possible. This discovery has been a game changer and time saver for me in web data retrieval.

I can only imagine. In theory, I can see myself using this instead of IE for all my webscraping.

I'm really glad you discovered this and shared it with us. If you have time, maybe start a thread in the tip and tutorials on this topic because I'm sure a lot of people will benefit from it.

Recently I was also surprised to see that you can use Excel's webbrowser just like IE to input usernames/ passwords, click on buttons, etc. It's really depressing that no one at MS has spent the time and effort to document all of this.

abousetta