asked on
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:
-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.
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 If
End 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.
Microsoft ExcelSpreadsheetsVB Script
Last Comment
Gregory Miller
Here is a good walk through KB on how to use the AT command. It is pretty simple...
http://support.microsoft.com/kb/313565
http://support.microsoft.com/kb/313565
For the save confirmation problem, check if the file exists, and delete it before doing the saveAs:
If Dir(FileToTest) <> "" Then
SetAttr "E:\tmp\test2.xlsx", vbNormal ' remove any read-only attribute
Kill "E:\tmp\test2.xlsx" ' delete file
For the rest of the problem, you may find it easier to use OLE automation so you're using the VBA in one .xls to edit another one.
Check this out: http://support.microsoft.com/kb/184974
If Dir(FileToTest) <> "" Then
SetAttr "E:\tmp\test2.xlsx", vbNormal ' remove any read-only attribute
Kill "E:\tmp\test2.xlsx" ' delete file
For the rest of the problem, you may find it easier to use OLE automation so you're using the VBA in one .xls to edit another one.
Check this out: http://support.microsoft.com/kb/184974
ASKER
When I replace the code, it seems to only stop at creating the Custom Attribute 3 heading, but never does the rest. Not sure if I have the macro formatted correctly. Here is how it currently is:
Also, can you please provide what I would need to enter for the AT command? am I using it in conjunction with the Task Scheduler? When I try to schedule a task with just the xls file, it gets stuck at running and then fails and never completes.
Private Sub Workbook_Open()
If ActiveSheet.Cells(1, 11) = "Custom Attribute 3" Then
'nothing to do as already done
ActiveWorkbook.Close SaveChanges:=False
Else
'fill extra columns
FillColumnKLM
'now save and close
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=True, Filename:="active-transform.xls"
Application.DisplayAlerts = True
End If
End Sub
Sub FillColumnKLM()
Dim WS As Worksheet, I As Long, RwCnt As Long
Set WS = ActiveSheet
RwCnt = WS.Cells(Rows.Count, 1).End(xlUp).Row
WS.Cells(1, 11) = "Custom Attribute 3"
WS.Cells(1, 11).Font.Bold = True
For I = 2 To RwCnt
On Error Resume Next
If InStr(1, LCase(WS.Cells(I, 1)), "Donut") Then
WS.Cells(I, 11) = "Jelly"
Else
End If
If InStr(1, LCase(WS.Cells(I, 1)), "Coffee") Then
WS.Cells(I, 11) = "Milk"
Else
End If
Next
Columns.AutoFit
End Sub
Also, can you please provide what I would need to enter for the AT command? am I using it in conjunction with the Task Scheduler? When I try to schedule a task with just the xls file, it gets stuck at running and then fails and never completes.
I changed the way you should do this so that the excel application can actually terminate after it runs. If this is not needed, then ignore this post. The previous method would close the sheet but will leave Excel running.
Private Sub Workbook_Open()
If ActiveSheet.Cells(1, 11) = "Custom Attribute 3" Then
'nothing to do as already done
ActiveWorkbook.Save
Else
'fill extra columns
FillColumnKLM
'now save and close
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
End If
Application.Quit
End Sub
ASKER
Where would i specify the destination file name in the script you provided?
It would be great if you created a second question for the new Sub you have added. This really does not pertain to the original question.
As for the AT command, I have changed to the SCHTASKS command for more granular control.
As for the AT command, I have changed to the SCHTASKS command for more granular control.
schtasks /create /tn "EE-Sample" /tr "C:\<path-to-file>\EE-Example.xlsm" /sc minute /mo 5
You do not need to specify the file name if you are opening, modifying and saving the same file over itself. You only need to specify a filename if you want to SaveAs another file name.
ASKER
That's what I want to do, save it as a different file name just like it was in my originally posted script. The excel sheet is closing, but its not completing the script for my conversions, where it was prior to the new code.
Just change this line
ActiveWorkbook.Save
Modified:
ActiveWorkbook.SaveAs active-transform.xls
ActiveWorkbook.Save
Modified:
ActiveWorkbook.SaveAs active-transform.xls
One last thing, I was having Excel 2013 bomb out on me when running from the TaskScheduler so I changed my command to launch a simple .BAT file instead
In the .BAT file, I put the single line:
C:\<path-to-file>\EE-Examp
This forces a new command processor to handle the launch of Excel and wait for it to end. This works perfectly.
schtasks /create /tn "EE-Sample" /tr "C:\<path-to-file>\EE.bat" /sc minute /mo 5
In the .BAT file, I put the single line:
C:\<path-to-file>\EE-Examp
This forces a new command processor to handle the launch of Excel and wait for it to end. This works perfectly.
ASKER
Thanks, 2 of my 3 questions have been answered and I am able to successfully run through the script and schedule the task successfully. I'll be opening another question for debugging the script to see why it's not completing. However, I still have a pending question:
Is there a way to have the macro only run on the file name called active.xls (the source file)?
When I open the active-transform (the destination/conversion file), it also runs, which is annoying when I want to verify, I have to disable the macro and re-enable each time I check.
Is there a way to have the macro only run on the file name called active.xls (the source file)?
When I open the active-transform (the destination/conversion file), it also runs, which is annoying when I want to verify, I have to disable the macro and re-enable each time I check.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks for all your patience and help!
Thanks..!
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.