Automate opening a spreadsheet and running a macro

I would like to schedule a task the automatically opens a spreasheet and runs a macro every day. Is this posible and if soi how?
MarkRichardStanleyAsked:
Who is Participating?
 
TommySzalapskiConnect With a Mentor Commented:
Actually you should use Workbook_Open not Auto_Open. Auto_Open only runs when the workbook is opened automatically.

Why would I need to use a BAT file and not just call the actual spreadsheet?
You can't point task schedular to open a worksheet (at least you couldn't last time I tried). Just make a text file and put the path of the Excel sheet in the file and save it as a .bat. It's super easy.

Also, according to Microsoft it is better to use VB Code
Right, you use VB code to run the macro when the Excel file opens, but you need Task Scheduler to tell it to open once per day or else you'll need to leave the excel sheet sitting open all day. Also with Task Scheduler, you can set it to run the task at a certain time, but also to run as soon as possible if it happens to be off at that time for some reason.

You could use a vbscript file instead of a .bat file and put VB code to open the sheet and run the macro but the .bat file is much simpler.
0
 
TommySzalapskiCommented:
Yes. It's easy. Just add a macro that runs on Workbook_Open and make a .bat file that just calls the spreadsheet. Then add that .bat file to a task in Task Manager.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TommySzalapskiCommented:
Now, you'll have to fix the macro security to allow the macro to run without prompting. If you made the macro on the machine, it should run as a trusted macro otherwise I suggest using digital signatures instead of using the lowest macro security setting (which would work).
Details here: http://office.microsoft.com/en-us/excel-help/digitally-sign-a-macro-project-HA001231781.aspx
0
 
TommySzalapskiCommented:
Yes, sorry. I meant Task Schedular not Task Manager.
0
 
MarkRichardStanleyAuthor Commented:
Why would I need to use a BAT file and not just call the actual spreadsheet? Also, according to Microsoft it is better to use VB Code. http://office.microsoft.com/en-us/excel-help/running-a-macro-when-excel-starts-HA001034628.aspx

What do you think?
0
 
MarkRichardStanleyAuthor Commented:
I was able to use the Task Scheduler to open the actual excel file directly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.