Solved

Running Batch file to ro open Excel and run macro

Posted on 2014-01-03
11
4,496 Views
Last Modified: 2014-10-24
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
0
Comment
Question by:GPSPOW
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39755188
I cannot get it to run.

What's the error(s) / problem?!
0
 

Author Comment

by:GPSPOW
ID: 39755191
I do not get an error.

Excel does not open to run the macro.

Glen
0
 
LVL 16

Expert Comment

by:R. Andrew Koffron
ID: 39755198
excel "C:\Users\Glen\Documents\PMS\Test.xlsm"

should launch it.

make the macro autorun, and it'll close itself
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:GPSPOW
ID: 39755242
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
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39755270
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?"
0
 

Author Comment

by:GPSPOW
ID: 39756252
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
0
 
LVL 18

Expert Comment

by:Steven Harris
ID: 39756450
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.
0
 

Accepted Solution

by:
GPSPOW earned 0 total points
ID: 39770990
I will delete this question.

I developed an alternative approach to the problem

Thank you

glen
0
 

Author Comment

by:GPSPOW
ID: 40390795
Ok to cancel
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40401600
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

730 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