CODEWITHSUNDEEP
vbaexcelvbscript
Andy
Andy

Reputation: 1070

VBScript seemingly waiting for Excel Macro to finish?

I am fairly new to VBScript so would like to check my understanding with more experienced users :)

I have written a basic script to run an excel macro. The macro is called MainFlow and then a message box appears after saying finished. The code is below.

Now to my surprise it appears VBScript waits for the macro MainFlow to run in its entirety before then moving on to the next statement, in this case a message box saying finished. This really surprised me because from my experience I would have expected VBScript to move on to the next statement immediately. As this is going to be part of a wider batch process I wanted to check my understanding before I moved on. I know for running multiple VBScripts from one master script, I will need to use the waitonreturn boolean to ensure each script has finished before the next is started, and it is this that has caused me surprise regarding the apparent waiting for the macro to finish within the script itself.

If someone could confirm my understanding (and even explain why) that would be really appreciated. 

dim xlApp, xlBook 
Set xlApp = createobject("excel.application") 
xlApp.displayalerts = false 
Set xlbook = xlapp.workbooks.open("G:\Clients\Client\Reporting\Automated\Futures Expiry    Warning\All Futures Expiry.xlsm") 
xlapp.run "MainFlow" 
msgbox("Finished") 
xlbook.save 
xlbook.close false 
Set xlbook = nothing 
xlapp.quit 
Set xlApp = nothing

Upvotes: 1

Views: 2585

Answers (4)

Ritsch
Ritsch

Reputation: 11

Just setting the ReadOnly parameter to false in the Open Function already did the trick for me

e.g.

Set objExcelBook = objExcel.Workbooks.Open(xlsFile, false)

Upvotes: 1

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

VBA is not multitask, so i don't see why it would come back to your initial code before completing MainFlow.

Sometimes a DoEvents (in MainFlow) makes it look like VBA is doing two tasks at a time, but it is not. (in some case it will even slow the process , or make it faster, depending on the issue)

If you don't like Excel 'freezing' , make a progress bar, so that the user doesn't think Excel crashed, and wants to abort...

without seing MainFlowcode, i can't tell more.

Upvotes: 1

Nathan_Sav
Nathan_Sav

Reputation: 8531

Would this method work? (put in here for formatting)

fnShellExecuteVB
function fnShellExecuteVB()
        dim objShell
    dim o
        set objShell = CreateObject("shell.application")
    objShell.ShellExecute  "excel.exe", "C:\DUMMY\x.xlsx", "", "open", 1 

        set objShell = nothing
    end function

Upvotes: 1

MikeC
MikeC

Reputation: 958

VBScript will wait for the Excel VBA code to complete execution before proceeding.

Upvotes: 1

Related Questions