Solved

Running Batch file to ro open Excel and run macro

Posted on 2014-01-03
11
4,137 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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,…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

777 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