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:

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

Open in new window

-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.

Gregory Miller

I added the "Application.DisplayAlerts" option surrounding your save command which should suppress the alert notices.

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
         Application.DisplayAlerts = False
         ActiveWorkbook.Close SaveChanges:=True, Filename:="active-transform.xls"
         Application.DisplayAlerts = True

  End If
        
End Sub

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.

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

Beneford

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

fireguy1125

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:

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

Open in new window

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.

Gregory Miller

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

Open in new window

fireguy1125

ASKER

Where would i specify the destination file name in the script you provided?

Gregory Miller

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.

schtasks /create /tn "EE-Sample" /tr "C:\<path-to-file>\EE-Example.xlsm" /sc minute /mo 5

Open in new window

This runs the sheet every 5 minutes using the schtasks command.

Gregory Miller

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.

fireguy1125

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.

Gregory Miller

Just change this line
ActiveWorkbook.Save

Modified:
ActiveWorkbook.SaveAs active-transform.xls

Gregory Miller

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

schtasks /create /tn "EE-Sample" /tr "C:\<path-to-file>\EE.bat" /sc minute /mo 5

Open in new window

In the .BAT file, I put the single line:
C:\<path-to-file>\EE-Example.xlsm

This forces a new command processor to handle the launch of Excel and wait for it to end. This works perfectly.

fireguy1125

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.

ASKER CERTIFIED SOLUTION

Gregory Miller

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fireguy1125

ASKER

Thanks for all your patience and help!

Gregory Miller

Thanks..!