troubleshooting Question

Excel 2007 Automate Opening, Running Macro, and closing Excel.

Avatar of fireguy1125
fireguy1125 asked on
Microsoft ExcelSpreadsheetsVB Script
15 Comments1 Solution632 ViewsLast Modified:
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
         'fill extra columns
         '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.
Gregory Miller

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros