GPSPOW
asked on
Running Batch file to ro open Excel and run macro
Here is the VBS code I have to open and Excel file and run my Macro named 'test'.
Option Explicit
On Error Resume Next
ExcelMacroExample
Sub ExcelMacroExample()
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Applic ation")
Set xlBook = xlApp.Workbooks.Open("C:\U sers\Glen\ Documents\ PMS\Test.x lsm", 0, True)
xlApp.Run "test"
xlApp.DisplayAlerts = False
xlApp.SaveAs "C:\Users\Glen\Documents\P MS\Test.xl sm"
xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
I cannot get it to run.
How can I convert this to a 'BAT' file?
Thanks
Glen
Option Explicit
On Error Resume Next
ExcelMacroExample
Sub ExcelMacroExample()
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Applic
Set xlBook = xlApp.Workbooks.Open("C:\U
xlApp.Run "test"
xlApp.DisplayAlerts = False
xlApp.SaveAs "C:\Users\Glen\Documents\P
xlApp.ActiveWorkbook.Close
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
I cannot get it to run.
How can I convert this to a 'BAT' file?
Thanks
Glen
ASKER
I do not get an error.
Excel does not open to run the macro.
Glen
Excel does not open to run the macro.
Glen
excel "C:\Users\Glen\Documents\P MS\Test.xl sm"
should launch it.
make the macro autorun, and it'll close itself
should launch it.
make the macro autorun, and it'll close itself
ASKER
The macro appends a spreadsheet tab from a revised SQL table. The data only needs to be updated once a day, so the macro cannot be an AUTORUN. Its made to update a log that a clerk maintains with comments.
So my question is, how can I make the macro run (similar to the way Access does with x/ command)? Is this available for Excel?
Thanks
Glen
So my question is, how can I make the macro run (similar to the way Access does with x/ command)? Is this available for Excel?
Thanks
Glen
I do not get an error.
Excel does not open to run the macro.
Are you sure? The workbook should be hidden but you can see it through Task Manager.
You may want to change it to:
Sub ExcelMacroExample()
Dim xlApp
Dim xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Users\Glen\Documents\PMS\Test.xlsm", 0, True)
xlApp.Run "test"
xlApp.DisplayAlerts = False
xlapp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close
xlApp.Quit
End Sub
This uses xlapp.ActiveWorkbook.Save instead of SaveAs which may be your issue since you are suppressing notifications from the SaveAs dialogue.*
Alternatively, remove xlApp.DisplayAlerts = False and see what happens...
*"This file already exists. Are you sure you want to overwrite this file?"
ASKER
Let me repharse my original question:
I want to create a batch file that will open an excel xlsm file "C:\Test.xlsm"
Run a MACRO named "TEST" that updates two of the tabs in the workbook from a SQL server table.
Save the workbook
Quit Excel
Close the batch file.
The MACRO cannot be the type that Runs when the workbook is open. I want to schedule the batch file to run once a day so it updates the LOG tabs so a clerk can manually update the LOG with comments on the data that has been appended from the MACRO.
Thanks
Glen
I want to create a batch file that will open an excel xlsm file "C:\Test.xlsm"
Run a MACRO named "TEST" that updates two of the tabs in the workbook from a SQL server table.
Save the workbook
Quit Excel
Close the batch file.
The MACRO cannot be the type that Runs when the workbook is open. I want to schedule the batch file to run once a day so it updates the LOG tabs so a clerk can manually update the LOG with comments on the data that has been appended from the MACRO.
Thanks
Glen
As far as I am aware, a .bat script will not be able to manipulate the Excel file in order to call the Macro without Workbook_Open. I am sure that is where you came up with the VBS script to begin with.
With that said, have you tried the suggestion I posted with the VBS modification?
Alternatively, you don't have to rule out the Workbook_Open idea. Just an idea -
Is this workbook normally open at the time you want the macro to run?
If it will already be open, set a statement to check the number of times it is open:
If it is opened the first time by a user, nothing happens. If it is opened a second time (by BAT), the macro runs. Of course this will not work if it will be the first to open the file.
With that said, have you tried the suggestion I posted with the VBS modification?
Alternatively, you don't have to rule out the Workbook_Open idea. Just an idea -
Is this workbook normally open at the time you want the macro to run?
If it will already be open, set a statement to check the number of times it is open:
Private Sub Workbook_Open()
If Workbooks.Count >= 2 Then
Range("A1").Value = "Already Open!"
End If
End Sub
If it is opened the first time by a user, nothing happens. If it is opened a second time (by BAT), the macro runs. Of course this will not work if it will be the first to open the file.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok to cancel
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
What's the error(s) / problem?!