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.Application")
  Set xlBook = xlApp.Workbooks.Open("C:\Users\Glen\Documents\PMS\Test.xlsm", 0, True)
  xlApp.Run "test"
  xlApp.DisplayAlerts = False
  xlApp.SaveAs "C:\Users\Glen\Documents\PMS\Test.xlsm"
  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
GPSPOWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alex [***Alex140181***]Software DeveloperCommented:
I cannot get it to run.

What's the error(s) / problem?!
GPSPOWAuthor Commented:
I do not get an error.

Excel does not open to run the macro.

Glen
R. Andrew KoffronownerCommented:
excel "C:\Users\Glen\Documents\PMS\Test.xlsm"

should launch it.

make the macro autorun, and it'll close itself
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

GPSPOWAuthor Commented:
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
Steven HarrisPresidentCommented:
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 

Open in new window


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?"
GPSPOWAuthor Commented:
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
Steven HarrisPresidentCommented:
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:

Private Sub Workbook_Open()
     If Workbooks.Count >= 2 Then
          Range("A1").Value = "Already Open!"
     End If
End Sub

Open in new window


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.
GPSPOWAuthor Commented:
I will delete this question.

I developed an alternative approach to the problem

Thank you

glen

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
Ok to cancel
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.