Automate downloading and read spreadsheets from an internet source and pull into excel

Hi guys,

I have been scouring over all sort of forums and tutorials etc, however i cannot seem to find the answer to my problem. Specifically i would like to:
- Use excel to automatically download data sitting within a website, a internet source such as (Table 1 for example): http://www.abs.gov.au/AUSSTATS/[email protected]?OpenDocument
- Upon downloading the sheet, I want excel to essentially 'read' this downloaded spreadsheet, and only include designated time series, for example i only want series number: A84423127L
- I essentially want to be able to do this for numerous downloaded sheets at the same time.
- From here i imagine that i would have a 'master spreadsheet', full of the most up-to-date data.
- Then linking to this sheet, i would have the relevant graphs etc

Is this possible?
Thanks in advance!

Hi,

This will compile the raw data into one master sheet as a starting point.

It opens the workbooks then copies the rows matching A84423127L into a master workbook. I've only included the links for the first four tables - you'll have to add the remaining links in.

If you need help developing further then let me know.

Code in the atatched file

thanks for that!

So at the moment that macro downloads the table of interest. When i am trying to cleanse the workbook however it seems to delete all rows (as opposed to columns). Is this just a glitch or am i running the code incorrectly?
Also, if i am opening multiple workbooks (tables 1, 3, 5 etc) am i able to pull out multiple series for example A8442XXXX and B845XXXXX. Whilst the series might not appear on all tables, will the code be able to 'read' through the relevant tables and return the relevant series?

Cheers,

Hi,

My apologies, I never even noticed the raw data in the Data sheets, I was working solely on the index sheets! I'll switch it to compile the data in the Data sheets.

Yes indeed you can pull out multiple series. We can add these as variables. Easiest way is to add these to a range somewhere - maybe on the front sheet. A slicker approach would be to create a userform with checkboxes for each series, although I'm doing this in my lunch break so time is tight! Depends how quick you need this.

I'll have a play and get back to you.

Ok, I've done what you needed. the index links to the raw data. You just have to input the series and download links on the front page and press the Run Extract button. Takes a while to open and download the links.

I'll have a look if there is a way to evaluate and extract the links from the webpage.

Hi,

I've got a better solution now. Not perfectly elegant but it works.

From the link you shared, download the .zip file with all the files in. Extract the files. Open the attached workbook. Click run extract and a file dialogue will open. Navigate to the folder where the extracted files are and click OK. The extract will complete.

Two caveats. 1, you'll need to update the column with the series' you need to extract. 2, the raw data has duplicates which are reflected in the cleased data. You can filter these out by deleting the raw data files you don't want.

If you need any further help please let me know

Rob