mardi 8 novembre 2016

Need advice on running a macro with VBS

I have VBS that runs and executes successfully IF my Personal.xlsb is not open. Often times I will have it open or leave it open if I have been using other macros within the xlsb file. I am wanting to do something like

If Personal.xlsb is open run macro else xlapp.run "macro" and see if that works but am not sure how to write that out. To the point, I want the macro to run regardless of whether or not the Personal.xlsb macro is open or closed. Below is my current script that runs only when Personal.xlsb is closed.

Option Explicit

On Error Resume Next

ExcelMacroExample

Sub ExcelMacroExample() 

Dim xlApp 
Dim xlBook

Set xlApp = CreateObject("Excel.Application") 
Set xlBook = xlApp.Workbooks.Open("U:\Pricing\Weekly_Price_Review\TestKester\Kester.xlsx", 0, True)
xlApp.Application.Visible = False
xlApp.DisplayAlerts = False 
xlApp.Run "'C:\filepath\PERSONAL.XLSB'!Macro1"
xlApp.DisplayAlerts = True
xlBook.Quit
xlApp.Quit

Set xlBook = Nothing 
Set xlApp = Nothing 

End Sub 

Aucun commentaire:

Enregistrer un commentaire