Solved

Running Batch file to ro open Excel and run macro

Posted on 2014-01-03
11
4,292 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
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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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 46

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
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…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 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