Macro to download and import data

[chisox721]

I'm currently looking for a way for Excel to automatically download an excel file and import the contents into my spreadsheet. In the past I have just used copy/paste, but that gets old very fast. Unfortunately I don't think web query is useful in this particular situation since the page only shows a fraction of all the data I need. Is it possible to use a macro that will import the download? I'm somewhat familiar with macros but unsure of where to start here.

Here is an example of the data I need. The link to download is "Export Data" near the top.

http://www.fangraphs.com/leaders.asp...on1=2014&ind=0

Any ideas would be greatly appreciated.

[Tsjallie]

I don't think you can directly dowload a file from that website. The link behind "Export Data" runs a javascript generating the csv-file.
I'm not sure whether it's possible to run that javascipt from a workbook. Guess the website's security won't allow you to.
But if you can live with generating that csv-file, then the rest can be automatic.
Importing teh csv can be done easily with a data connection.

[ducky_yeng]

I was recently learning about downloading files from websites. I was able to automate IE to download the file from your given website. (However, I have yet to successfully create the same result by implementing resources such as XMLHTTP etc). chisox721, I hope I understand you correctly that this is part of a process you require in your macro.

I have not implemented import as Tsjallie has answered it.

Here is code. I hope it helps take you one step closer to your end solution. (BTW I'm still learning about CUIAutomation so I won't be much help on that.)

Please Login or Register  to view this content.

[Tsjallie]

Nice piece of code and it works.
Assumed website security wouldn't allow it, but jumped to conclusions too soon.
Now the download is automated, further importing the data can easily be done with a data connection (import from text)

[chisox721]

Hey guys-First off sorry for the delay in response, haven't had time to really dig into this until today. Thank you both for you suggestions, the code worked great Ducky. Now I just need to finish it off with the data import.

Thanks again!

[chisox721]

One last question. Since I have multiple files that I need to import on a daily basis ie "Pitching", "Hitting", could there be an addition to the macro that would rename the downloaded file?