How To Pull Internet Explorer Link Report Into Excel

[tjeacret]

Hello. I have a question. At work, we have a site that stores a bunch of reports. You click the link on the website, it opens the report in Excel. I am trying to figure out how to make an Excel workbook pull that data from the website automatically and be able to be refreshed several times in a day. So, here is what I'm needing.

The workbook I'm using needs to pull this report, copy the entire contents of the second tab and paste it into the workbook. I also need it to be refreshed automatically every half hour or have a way to make it refresh. I tried pulling external web content, but since the link opens an Excel report, I can't get that to work. How do I go about this?

Thanks!

 

[tjeacret]

Anyone?

 

[Ruddles]

If you're using 2007, go Data > From Web, and you're guided through the process of selecting a URL, identifying a table of data and then importing it into your worksheet. If you do this with the macro recorder enabled, you should end up with some VBA code will import the current version of
the page. You can then write a simple loop to call that code at timed intervals.

Any use?

Alternatively, if the Web page is getting its data from a file, you may be able to open that file directly in Excel using it's real server/path/name rather than using IE and going through the Web server. As the data is changing during the day, this suggests that it's an active server page of some sort (ASP/PHP), so it must be getting its data from somewhere. If it's a file and you can get to it, you have an easy solution.

However if the page is obtaining its data from an SQL server, that's going to be a lot trickier or even impossible.