Excel 2007 Automate Opening, Running Macro, and closing Excel.
I'm running the following VBA part of code that runs through a Macro, saves it, and closes out of the workbook when completed. I need some modifications to it that will automate this process, and run it as a scheduled task:
Private Sub Workbook_Open() If ActiveSheet.Cells(1, 11) = "Custom Attribute 3" Then 'nothing to do as alrady done ActiveWorkbook.Close SaveChanges:=False Else 'fill extra columns FillColumnKLM 'now save and close ActiveWorkbook.Close SaveChanges:=True, Filename:="active-transform.xls" End IfEnd Sub
-If the filename "active-transform.xls" already exists at the destination location, I am getting a prompted that the filename already exists in the location, and if I want to replace it. I want to always overwrite the filename without the prompt.
-The destination worksheet "active-transform.xls" also has the macro run when I open it, which I don't want to do. I only want the macro to run when opening the source spreadsheet. When i open the active-transform.xls, it runs the macro, and overwrites the changes from the initial macro run.
-How do I set this up as a scheduled task, so it is completely automated and hands free. I have a dedicated machine that gets an xls file injected to it on a daily basis. I want to schedule the running of the macro at a set time, or essential, schedule excel to open the particular xls, which will essentially run the macro.
Open in new window
At for the timed event, you can use the "AT" command at a command prompt to create or use the Task Scheduler to do this. The application to run will be your .XLS file. You may have to either give the full path in your macro on where to save the current spreadsheet to avoid it being saved in the Windows current working folder, but I am thinking you should be good without mods.