Solved

Running Batch file to ro open Excel and run macro

Posted on 2014-01-03
11
3,891 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
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…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now