J
Jim Gordon MVP
I am trying to import the “Half Hourly Exchange Volume Report” (first table) from a website into Excel This data refreshed every 30 minutes, so I need the xls to be able to refresh, when needed (which I can do).
You can choose to limit the query result to tables, but not just the
first table. This can be done pro grammatically with a VBA macro as
described here:
http://support.microsoft.com/kb/213730
If you don't want to bother with making your own VBA code, you can
download a free template that lets you paste a URL and limit the results
to Tables only. The template for Excel 2004 is here:
http://www.acsu.buffalo.edu/~gordonj/XL/DownloadPage.htm
However, that is only one part of the solution you are seeking...
You can tell Excel to not mess with the column widths when refreshing
the data by using the External Data toolbar. Select any cell in the data
range, then click the Data Range Properties button on the External Data
toolbar. Uncheck the Adjust column width checkbox to prevent the columns
from resizing when you refresh the data.
You can hide most of the unwanted rows and columns, but web page makers
love to use nested tables, which look nice in web browsers but are
useless when it comes to making data accessible for analysis. Nested
tables will make it hard to hide all of the rows and columns you might
wish to hide.
If formatting is important, use cell formulas (on a different sheet)
that are linked to the data range, or make a macro that copies the range
and use paste special > values into a formatted range on another sheet.
You can choose to limit the query result to tables, but not just the
first table. This can be done pro grammatically with a VBA macro as
described here:
http://support.microsoft.com/kb/213730
If you don't want to bother with making your own VBA code, you can
download a free template that lets you paste a URL and limit the results
to Tables only. The template for Excel 2004 is here:
http://www.acsu.buffalo.edu/~gordonj/XL/DownloadPage.htm
However, that is only one part of the solution you are seeking...
You can tell Excel to not mess with the column widths when refreshing
the data by using the External Data toolbar. Select any cell in the data
range, then click the Data Range Properties button on the External Data
toolbar. Uncheck the Adjust column width checkbox to prevent the columns
from resizing when you refresh the data.
You can hide most of the unwanted rows and columns, but web page makers
love to use nested tables, which look nice in web browsers but are
useless when it comes to making data accessible for analysis. Nested
tables will make it hard to hide all of the rows and columns you might
wish to hide.
If formatting is important, use cell formulas (on a different sheet)
that are linked to the data range, or make a macro that copies the range
and use paste special > values into a formatted range on another sheet.