Downloading web content using form

[paul_taylor]

I need to download web content into a spreadsheet. I'm using a form and the web browser control. (The reason is that I have to pass variables to get to the page I need--can't use Excel web query).

This is the last piece of code I'm using after I load the document.

Sheets(1).Cells(1, 1) = doc.DocumentElement.innerText

It's downloading all the content, but it's all going into cell A1. What I'd like it to do is populate down to row 100 1,000 or however far it has to go with one line of text per row. The page I'm downloading is thousands of rows long, hence this method cuts off everything below the amount of text that can fit in one cell.

 

[paul_taylor]

FYI, what I decided to do on this was
1) save the webbrowser's text as a text file
2) load the text file into Excel

 

[Norie]

Why can't you use a web query?

What 'variables' are you passing?

Perhaps it could be possible to incorporate them into the web query?

 

[paul_taylor]

Why can't you use a web query?

What 'variables' are you passing?

Perhaps it could be possible to incorporate them into the web query?

You can't just send the url and get there. You have to go to the url, then fill out their form and query it on their site to get the results.

 

[Norie]

That's slightly different to passing variables.

Have you considered automating IE?

You are sort of half-way to doing that using the WebBrowser control but you would get more control if you automated IE outwith a form.

 

[paul_taylor]

That's slightly different to passing variables.

Have you considered automating IE?

You are sort of half-way to doing that using the WebBrowser control but you would get more control if you automated IE outwith a form.

Another programmer handled the heavy lifting part of this. He chose webbrowser over IE--can't remember exactly why.

I don't think it would be any different, though? The info i'm reading on how to bring back results are the same webbrowser vs IE.

 

[Norie]

Paul

I don't know anything about the page/URL you are interested in so can't really comment, sorry.

One thing though, is that all the code for getting stuff from the page?

Is there then further code to parse that or perhaps access the DOM (document object model) to extract tables, use forms etc?

 

[paul_taylor]

Paul

I don't know anything about the page/URL you are interested in so can't really comment, sorry.

One thing though, is that all the code for getting stuff from the page?

Is there then further code to parse that or perhaps access the DOM (document object model) to extract tables, use forms etc?

Yes, the web page is a huge page with various tables and lists. There is a long macro that will run to manipulate and move around that data once it's in Excel.

 

[Norie]

It could be possible to extract the data, in table/list format, from the webpage rather doing it in Excel.

For example you can use something like this to get a collection of all the tables on the page:

Set tbls = document.GetElementsByTagName("TABLE")

Sort of like a table in Access/Excel each table has rows/column/cells which can be looped through to get the data and transfer it to worksheet in tabular format.

If there are specific tables, or other elements, on the page you are interested then you can use GetElementByID to create a reference to them.

strTableID ="IDofTheTable"
 
Set tbl = document.GetElementByID(strTableID)

Again you could loop through the rows etc of the table and transfer the data to Excel.

I prefer using these methods to parsing the data out of a whole bunch of text, but that's just me I suppose.

 

[paul_taylor]

It could be possible to extract the data, in table/list format, from the webpage rather doing it in Excel.

For example you can use something like this to get a collection of all the tables on the page:

Set tbls = document.GetElementsByTagName("TABLE")

Sort of like a table in Access/Excel each table has rows/column/cells which can be looped through to get the data and transfer it to worksheet in tabular format.

If there are specific tables, or other elements, on the page you are interested then you can use GetElementByID to create a reference to them.

strTableID ="IDofTheTable"
 
Set tbl = document.GetElementByID(strTableID)

Again you could loop through the rows etc of the table and transfer the data to Excel.

I prefer using these methods to parsing the data out of a whole bunch of text, but that's just me I suppose.

Maybe. The macro is already built that manipulates the data and so forth.

I'm trying to cut out a step of having the user click the website and download the page first. The solution I posted in second post works. Save innertext as text and then load the text into Excel. I'd still like to know if there was a way to bring the results straight into Excel without doing that, but it works now so i'm satisfied.