Solved

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

Posted on 2013-12-23
15
493 Views
Last Modified: 2013-12-24
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
Comment
Question by:fireguy1125
  • 9
  • 5
15 Comments
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39736430
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
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39736433
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
 
LVL 7

Expert Comment

by:Beneford
ID: 39736458
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
 
LVL 1

Author Comment

by:fireguy1125
ID: 39736742
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
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39736792
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
 
LVL 1

Author Comment

by:fireguy1125
ID: 39736868
Where would i specify the destination file name in the script you provided?
0
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39736912
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 11

Expert Comment

by:Technodweeb
ID: 39736915
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
 
LVL 1

Author Comment

by:fireguy1125
ID: 39736919
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
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39736924
Just change this line
     ActiveWorkbook.Save

Modified:
     ActiveWorkbook.SaveAs active-transform.xls
0
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39736933
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
 
LVL 1

Author Comment

by:fireguy1125
ID: 39738333
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
 
LVL 11

Accepted Solution

by:
Technodweeb earned 500 total points
ID: 39738346
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
 
LVL 1

Author Closing Comment

by:fireguy1125
ID: 39738359
Thanks for all your patience and help!
0
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39738364
Thanks..!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now