Help with VBS running an excel macro

Good Afternoon,

I have an outlook job that downloads an excel file then runs a VB Script whenever a particular email arrives in my inbox. The VBS in turn runs a macro from my PERSONAL.xlsb file that modifies the downloaded xls file. I use the PERSONAL.xlsb as my universal macro housing unit and I often have it open as I run macros from it manually and daily.

My dilemma is that, so long as my PERSONAL.xlsb file is closed, the outlook/vbs/vba process runs fine. If I have PERSONAL.xlsb open at the time the outlook job runs, the VBS will execute but won't complete the VBA because the xlsb file is open and it prompts for a readonly, at which case, if I am at my PC I can click it and the process will finish, but if I happen to be afk for a while, it will not run and process the changes made in the VBA macro.

How can I have this run regardless of whether or not my PERSONAL.xlsb file is opened or closed? Below are the scripts I have in each process.

VBS that works as long as PERSONAL.xlsb is closed.
<sub>Option Explicit
On Error Resume Next

ExcelMacroExample

Sub ExcelMacroExample()

Dim xlApp

Set xlApp = CreateObject("Excel.Application")

xlApp.Application.Visible = False
xlApp.DisplayAlerts = False
xlApp.Run "'C:\FILEPATH\PERSONAL.XLSB'!Macro1"
xlApp.DisplayAlerts = True
xlApp.Quit

Set xlApp = Nothing

End Sub</sub>

This is the beginning of the excel macro that edits the downloaded file.
<sub>Sub Macro1()
'
' Macro1 Macro

Dim xlBook
Set xlBook = Workbooks.Open("U:\FILEPATH\Kester.xlsx", 0, True)

xlBook.Application.Visible = False
Workbooks.Application.DisplayAlerts = False
xlBook.Application.DisplayAlerts = False

"Do All Edits and Such"</sub>

Your help is much appreciated!

Thanks for the reply Alphafrog. So while I was able to incorporate your suggestions into my excel macro and get that working, the VBS recommendation you have wont call the macro when the PERSONAL.xlsb is opened or closed. Using my VBS I can call the xls macro (with your inclusions) and it will run fine so long as the xlsb file is closed. It seems the trouble may be pointed to mine and your VBS. Neither of them call the xls macro when xlsb is opened. Any other suggestions?

For reference, this is your suggestion that is not working at all.

Please Login or Register  to view this content.