Daily import Automation (download, unzip, copy to worksheet, repeat for other files)

[Hawk11ns]

Good Morning/Afternoon-

I am seeking guidance on process scheduling and automation, specifically, downloading/extracting/importing multiple .csv files into a single worksheet. The .csv files are all posted online as .zip files, and the latest files are posted near the top of the page. Here are the static staging links to the files for reference:

ERCOT MIS

ERCOT MIS

ERCOT MIS

Here are the process steps I envision for each of the files:
- download the most recent (top) file with '_csv.zip' in the name
- extract file to pre-specified directory for staging
- open .csv file
- copy data beginning in specific cell, all the way down to last cell populated (sometimes will span multiple columns)
- paste data in macro worksheet on specific worksheet, in specific cell
- close .csv file without saving
- repeat process for remaining reports

Any guidance - or references - on how to move forward with this is very much appreciated - thank you!

 

[Rijnsent]

All that you mention is possible with VBA. I don't know if you know some VBA?
You list would start with scraping the data, e.g.: VBA Web Scraping | How to Scrap Websites using Excel VBA?
And next something like: VBA code to Zip and Unzip files and folders - Excel Off The Grid to unzip
And after that some "normal" VBA code to import the CSV data.
Hope that helps a bit,
Koen