Automatically Start Excel, Run Macro, and Save-As Each Day at a Specified Time

[bkanne]

I have a come across a fantastic macro that allows users to download a great amount of stock market data. It is the first of its kind that I have come across, and I can foresee a lot of uses for it.

It can be downloaded here: http://investexcel.net/download-finviz-data/

Unfortunately, in order to build a database from this macro, it needs to be updated and saved each day, as it does not specify any kind of date ranges to pull the data from. In order to build an effective database without automation, one would have open Excel manually, run the macro, and save the finally manually with the current date.

Can anyone provide guidance for solving this problem? I believe I can use Task Scheduler in Windows 7 to open Excel automatically after 4:30 pm EST each weekday (has to be after the close of the stock market), but I still need to find a way to:

1) Run the Macro automatically to update the data after Excel opens automatically with Task Scheduler
2) Automatically do a Save As that saves the new file into a specified folder with the date it was downloaded in the title

Ideally, it would be fantastic if someone could figure out how to task on the a third step where,

3) The data tab from each separately downloaded file could then be added back into a Master Workbook with each tab labeled with it's respective date of data extraction

Can anyone provide guidance on how to go about setting up this system? I will be extremely appreciative! Thank you so much.

[Jack7774]

You are on the right track. Use task scheduler to open excel at a given time. Create a new workbook and save it under.

C:\Users\....\AppData\Roaming\Microsoft\Excel\XLSTART

So that when excel opens it will open that file at that given time. Put your code in this workbook and use this and put your code in between

Please Login or Register  to view this content.

[Jack7774]

Did this work for you?

[bkanne]

Jesse,

Thanks so much for responding and staying on top of this. I added your additional code to my workbook and set up Excel to open using the Task Scheduler. Getting the file to open in Task Scheduler works fine, but I get an error when trying to add your code to my mine to run the macro. This is what my code looks like:

Please Login or Register  to view this content.

The error says: Compile error: Expected End Sub.
I am an amateur with VB, but I tried rearranging the code several times and didn't really get anywhere. Just an FYI, the code I am using already had an "End Sub" at the end.... I am not sure if there is a conflict with trying to add an additional "EndSub" as you directed in your reply. Any additional guidance would be greatly appreciated!

[Jack7774]

Take out the double sub at the top

Please Login or Register  to view this content.

Sub is the begining and end sub is the end. If you have two subs then it thinks you have two beginings.

[bkanne]

Thanks. The Macro runs now without an error, but it doesn't run automatically after Task Scheduler opens the file. I still have to run it manually. Am I missing something?

[Jack7774]

Did you save the macro to a workbook in VBA under tab "this workbook" and put it in this location "C:\Users\....\AppData\Roaming\Microsoft\Excel\XLSTART"? If not excel will not open the file with the macro in it. The macro is correct if you have taken out the sub getfinvizdata and left the Private Sub Workbook_Open() it should open and run the macro when the task manager opens excel at the time you setup.

[bkanne]

It seems to be working now, thank you. Now all I need to figure out how to do is automatically have the file "Save As" with the current date after the data downloads.

[Jack7774]

Make sure to put your directory before where the C:/ is in the code otherwise it won't save anywhere and it will give you an error.

Please Login or Register  to view this content.

[bkanne]

Works fantastically, with one exception. I had to change the Date formatting during the Save As from "mm/dd/yy" as you had listed to "mm-dd-yy" because the slash was being read as part of the destination directory and causing an error.

The final code looks like this:

Please Login or Register  to view this content.

I also had to add the "Application.DisplayAlerts = False" to get a warning box to stop popping up each time the macro ran. Thanks so much.

[Jack7774]

Awesome. I am glad it worked for you. Great job putting on the final touches. Thank you for the rep. Don't forget to mark your thread as solved please.