[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 564
  • Last Modified:

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.
0
fireguy1125
Asked:
fireguy1125
  • 9
  • 5
1 Solution
 
Gregory MillerGeneral ManagerCommented:
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.
0
 
Gregory MillerGeneral ManagerCommented:
Here is a good walk through KB on how to use the AT command. It is pretty simple...
http://support.microsoft.com/kb/313565
0
 
BenefordCommented:
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
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
fireguy1125Author Commented:
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.
0
 
Gregory MillerGeneral ManagerCommented:
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

0
 
fireguy1125Author Commented:
Where would i specify the destination file name in the script you provided?
0
 
Gregory MillerGeneral ManagerCommented:
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.
0
 
Gregory MillerGeneral ManagerCommented:
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.
0
 
fireguy1125Author Commented:
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.
0
 
Gregory MillerGeneral ManagerCommented:
Just change this line
     ActiveWorkbook.Save

Modified:
     ActiveWorkbook.SaveAs active-transform.xls
0
 
Gregory MillerGeneral ManagerCommented:
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.
0
 
fireguy1125Author Commented:
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.
0
 
Gregory MillerGeneral ManagerCommented:
You are using the Workbook_Open() trigger which will fire anytime this macro is present when you open the workbook. To solve this, put your code into a workbook by itself, have it open the workbook where you want the work done and data manipulations, do the manipulations, close and save the second workbook, close the current workbook, close excel.

OR

If you open Excel open the developer tab. Click on Macro Security and disable Macros so you can open the file to edit. I would suggest this option as the other option adds a big layer of complexity for debugging.
0
 
fireguy1125Author Commented:
Thanks for all your patience and help!
0
 
Gregory MillerGeneral ManagerCommented:
Thanks..!
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now