Download Multiple .xls files from a URL

The first URL doesn't resolve to a download link.

The VBA syntax would be pretty easy, post up a sample file with all these codes in one column and the default string in a reference cell, we can take a look. I need to be able to get one file to d/l before I'll be able to test the code after.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

1) create a sheet called Links in an empty workbook.
2) Drop your list of codes into column A
3) Add this macro to the workbook in an empty module (VBEditor: Insert > Module)

Please Login or Register  to view this content.

4) Be sure to correct the XXXXXXX in the code to the correct website
5) Save the workbook as a .xlsm workbook
6) Run the macro

That website is deathly slow responding, just the 3 sample codes in the attached file take over a minutes, so 150 is going to take.... maybe hours.

Sir,

Thank you very much. I am getting compile Error " Else Without If " I have tried both ways..I.e ur attached xlsm file and also inserting a new module in a blank workbook.

As the website is slow can the code be modified to only download the .xls files to a folder say temp? and then run another macro to bring the contents of all .xls files into a single sheet ?

Kindly help me as i am very much in need of this task to accomplish.

Thank you once again,

Zaska

The missing END IF has been added above.

Absolutely zero time would be saved downloading to a folder, the WEB SITE is slow, not the code.

Sir,

I am herewith attaching the sample file with desired output sheet. I have downloaded three files and then formatted them into one sheet .I have also provided the instructions in Links sheet. Kindly help me in automating this herculian task.


Thank you very much

Regards,

Zaska

Here's a separate macro to reformat the imported sheets. Rename that sheet to just Output and leave all the headers in the way you have them. Run this after you've imported all the sheets, for every sheet that is listed as "imported" on the Links sheet, it will copy the rows into the Output sheet and delete the individual sheet.

Please Login or Register  to view this content.

Sir,

I am sorry i failed to notice that .some of the url's had more than one page like

"http://subscribers.XXXXXXXXXXX.com/scoreboard/Scoreboard.asp?MenuOpt=010401&codeval=00000012&Print=Y&Excel=Y&page=5 "

So what changes should be made in the above code to download the subsequent pages if exists for a codevale i.e page 1,2,3,,,100.

URLstart = "http://subscribers.XXXXXXXXXXX.com/scoreboard/Scoreboard.asp?CodeVal="
URLend = "&Menuopt=010401&Print=Y&Excel=Y&page= 5"

Please kindly solve this issue as number of pages ranged from 0-100 ( I guess so)

Thanks and Regards,

Zaska

The macro we have created is constructing a specific URL using the information YOU provide. You will need to add additional information to your sheet to indicate when "codes" will have more than one page and what the URL would be in each situation. As long as you provide the up front info, we can tweak the d/l macro to use the URLs created by your specific info.


NOTE: After I tried to use this website twice, I would have given up and contacted them about getting the information another way. That lag is crazy.

Sir,

The Urlstart will not change in any case only the URLend will change if there are more pages as highlighted with Red colour in the below line.

URLend = "&Menuopt=010401&Print=Y&Excel=Y&page= 5" ( Indicating it to be the fifth page )

Is there any way if i provide the starting or ending page numbers in Links sheet beside the code value? Or can we make any code to check if subsequent page exists then download it or else skip to the next codeval?

For some of the "codevalues" there are 91 pages. This might increase next month . I have to go through the website again and again to check which codes are having more than one page.

Please kindly suggest me some way to over come this problem. I am very much in need of this data.

Your code hardly took 25 min to download the data of 109 sheets..so it's not a big problem. It's working fine.

Note : I have contacted them and they said that they don't have any alternative.

Regards,

Zaska

Sure, if you wanted to added a second column of "page numbers" to your sample data, we can give that a try. So if you entered a 5 for the page number for code 10401, then does that mean the macro would need to run 5 times, once to download page=1, then page=2, then page=3, etc...? Or does page=5 get all 5 pages in one download?

Pretty sure this will do it. For code "12" which has 3 pages, it manages to collect all 3 pages onto the one sheet called "00000012", so when you run the MergeSheets macro, all the data is there.

Please Login or Register  to view this content.

Sir,

Can you please interchange the columns in the output sheet as desired. I am herewith attaching a sample file with column numbers in desired order. Also i have a list of only few symbols for which i need the data..Kindly help me.


Regards,

Zaska

No, this has nothing to do with this thread.

The solution provided takes care of the thread topic AND then some, please select Thread Tools from menu above and set this topic to SOLVED.

Power outage? That's pretty out there...

Well, saving EACH time you finish an import of a page will absolutely make this slow macro even slower, several additional seconds per URL. Only two tweaks I can think of as needed, one to save the workbook and another to keep from processing the same rows again if you run the macro a second time, this should allow it to pick up where it left off...

Please Login or Register  to view this content.