Macro to execute at a set time, pull data off of a web page, and e

[Roger Brown]

I am sending out nightly reports on data collected from a web page. I have a
spreadsheet set up so all I need to do is pull the data off line and drop it
into the page, but I would love to be able to run a macro to pull down the
data, break up the spreadsheet and email it out. If I could make it run
automatically this would be even better.

 

[Bob Bridges]

If you don't mind parsing the raw HTML, getting the page is pretty easy:

set net = CreateObject("InetCtls.Inet")
net.RequestTimeout=30
net.URL="http://www.altavista.com/"
sHTML=net.OpenURL

sHTML now equals a long HTML string, which you can munge to your heart's
content. If you need to, you can also access the document using the IE
document model; it's much more complicated but also gives you a lot more
control. But you don't need control, I expect, you just need to be able to
extract data from the HTML.

This is VBS code, so you should be able to schedule it to run after hours
too. (You could do it in VBA/Excel, but it would require you to leave a VBA
macro running when you go home, the macro to keep running until it sees that
the time has come and finish the job itself. But this you can set up in
VBScript and then have your Windows scheduler just run it at the proper time
of night; VBScript can then load up Excel, load the data into a worksheet and
save it, email it, whatever.