By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,983 Members | 1,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,983 IT Pros & Developers. It's quick & easy.

Trying to get Excel Macro to pause will .bat file runs

P: 63
I am trying to get an excel macro to pause while a .bat file runs. Here is my code so far:

Call Shell("Q:\IT\Quantum\XXXX.bat", vbNormalFocus)

Workbooks.Open Filename:="C:\Filename.xls"

The XXXX.bat file generates the Filename.xls file - so I need the macro to pause until the .bat file has finished running.

Any ideas?!!

May 9 '08 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,445

After Shell, Sleep for few seconds, do events..

Expand|Select|Wrap|Line Numbers
  1. 'Declare This API in Form Level 
  2. Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
  5. Call Shell("Q:\IT\Quantum\XXXX.bat", vbNormalFocus)
  6. Sleep 10000   'Sleep for 10 seconds
  7. DoEvents
  8. DoEvents
  9. Workbooks.Open Filename:="C:\Filename.xls"
May 9 '08 #2

P: 63
Thanks Veena - but I need the macro to wait until the file has actually be saved down (the one that is generated from the XXXX.bat process) - this can take 5 minutes or it could take 3 minutes - so rather than a pause I guess I need a wait command.

I tried the command you added - but I am not too hot on the 'declare' stuff. How do I enter this API in form level

apologies for the stupid questions!

'Declare This API in Form Level
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Call Shell("Q:\IT\Quantum\XXXX.bat", vbNormalFocus)
Sleep 10000 'Sleep for 10 seconds
Workbooks.Open Filename:="C:\Filename.xls"

May 9 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.