Excel VBA Automation with the use of .BAT File - Task Scheduling your work

[DavidLee91]

Hi Folks,

I am currently facing a very complex problem and is pretty advance. I would really appreciate if anybody can point me to the right direction! I will try my best to explain my situation and thanks everybody for reading my lengthly post and problems!

Process:
1. I am currently doing a full automation Project by using Window Task Scheduler to open Excel VBA File, say, every Monday 7am.

2. Once Excel File is opened, it will automatically run a .bat file that will call and run another program. This .bat file opens SAS Program (Statistical Analysis System).

Excel run the following code:

    
Dim x as variant
x = Shell("c:\Users\Desktop\ReportWeekly.bat")

Which then open .bat file and run the following code. The bottom code is saved in ReportWeekly.bat format.

"C:\Program Files\SASHome\SASFoundation\9.3\sas.exe" -CONFIG "C:\Program Files\SASHome\SASFoundation\9.3\nls\en\sasv9.cfg" -SYSIN "c:\Users\Desktop\Master.sas" -ICON -LOG "c:\Users\Desktop\TestingCodeSAS1.LOG"

This code opens up SAS program and run some codes which will take very long to complete.

3. Now, here come the major problem. Since .bat run a seperate program, my Excel file will never knows if my .bat has completed running SAS program. I need to do something to let my excel knows that .bat has finished running its program so that it can continue to proceed to the next step.

In a nut shell: Window Task Scheduler -> Excel -> .bat and run other program -> back to excel (tell exel that the program has completed)



Please let me know if anybody require further information or explaination. THANKS everyone for reading my post and I would really appreciate if anybody can solve my problem!

Thanks a million folks!

Cheers,
David

[snb]

I don't think you need a BAT-file (nor task scheduler)

Sub M_snb()
 shell "C:\Program Files\SASHome\SASFoundation\9.3\sas.exe -CONFIG C:\Program Files\SASHome\SASFoundation\9.3\nls\en\sasv9.cfg -SYSIN c:\Users\Desktop\Master.sas" -ICON -LOG c:\Users\Desktop\TestingCodeSAS1.LOG" ,0
 ' more code to start when SAS has finished

End Sub

[Kenneth Hobs]

A ShellAndWait() routine such as Chip Pearson's should meet your goal. See: http://www.vbaexpress.com/forum/showthread.php?t=34604
It is similar in concept to the Shell() method that snb suggested but waits for the shell process to end. How well that would work with SAS, I don't know. When using either the Shell() or ShellAndWait() routine, you may need to delimit some parts with double quotes. To see if that is needed, just try snb's routine and see if that is going to work. Or, just replace your Shell() with ShellAndWait() after adding Chip's Module.

If it were me, I would put the Excel file into Windows Scheduler. I have used a MsgWait() routine to give me time to abort the autorun routine or On Open event.

[DavidLee91]

Hi Kenneth and snb,

Firstly, thank you all for taking your time to reply my post! Thanks soo much!


The problem with not using .BAT file and use Shell directly in VBA is that I faced with error due to quotes and etc. This is driving me crazy as I am still relatively new in VBA.

The error I faced:

Compile error:
Expected: list seperator or )

public sub TestingShell()
Dim x As Variant
x = ShellAndWait("C:\Program Files\SASHome\SASFoundation\9.3\sas.exe" -CONFIG "C:\Program Files\SASHome\SASFoundation\9.3\nls\en\sasv9.cfg" AUTOEXEC "C:\mis\SAS 9.3\Autoexec_RC.sas" -SYSIN "c:\Users\Desktop\Master.sas" -ICON -LOG "c:\Users\Desktop\TestingCodeSAS1.LOG", 0)
 
End Sub

I would really appreciate if you could let me know how or where to insert quotes or delimit.

Thanks so much!

Regards,
Leo

[snb]

Did you test the string in #2 ?

[DavidLee91]

Hi snb,

Yes, I did. The error I encountered was:

Compile error:
Expected: end of statement

while highlighting:

shell "C:\Program Files\SASHome\SASFoundation\9.3\sas.exe -CONFIG C:\Program Files\SASHome\SASFoundation\9.3\nls\en\sasv9.cfg -SYSIN c:\Users\Desktop\Master.sas"

[DavidLee91]

Hi Ken,

I was reading the post you have directed to me and come across Chip Pearson ShallandWait code.

I was wondering, how can I use his code to open my complex code.

Public Function ShellAndWait(ShellCommand As String, _
                    TimeOutMs As Long, _
                    ShellWindowState As VbAppWinStyle, _
                    BreakKey As ActionOnBreak) As ShellAndWaitResult

Credit: Chip Pearson.

The biggest problem I am facing is not opening my program. It is to open my program with many requirements, such as configuring my program after opening SAS, where to print SAS output and even logging in requirement. Thus, .BAT file seems to be an easy way out without having to code all the requirements into VBA. Which can prove to be quite tricky as can be seen by the .BAT file below:

"C:\Program Files\SASHome\SASFoundation\9.3\sas.exe" -CONFIG "C:\Program Files\SASHome\SASFoundation\9.3\nls\en\sasv9.cfg" AUTOEXEC "C:\user\desktop\Autoexec.sas" -SYSIN "c:\Users\Desktop\Master.sas" -ICON -LOG "c:\Users\Desktop\TestingCodeSAS1.LOG"

I would really appreciate if you could advise me what else I can do.

[Paul_Hossler]

When you want a quote (") as a character in a string you need to double it up, but not the "string" bracketing quotes

So the 'ShellCommand' would probably look something like this

("C:\Program Files\SASHome\SASFoundation\9.3\sas.exe"" -CONFIG ""C:\Program Files\SASHome\SASFoundation\9.3\nls\en\sasv9.cfg"" AUTOEXEC ""C:\mis\SAS 9.3\Autoexec_RC.sas"" -SYSIN ""c:\Users\Desktop\Master.sas"" -ICON -LOG ""c:\Users\Desktop\TestingCodeSAS1.LOG"

Public Function ShellAndWait(ShellCommand As String, _
                        TimeOutMs As Long, _
                        ShellWindowState As VbAppWinStyle, _
                        BreakKey As ActionOnBreak) As ShellAndWaitResult

[snb]

You are introducing a new version of the bat file
try

Sub M_snb()
   Shell "C:\Program Files\SASHome\SASFoundation\9.3\sas.exe -CONFIG C:\Program Files\SASHome\SASFoundation\9.3\nls\en\sasv9.cfg AUTOEXEC C:\user\desktop\Autoexec.sas -SYSIN c:\Users\Desktop\Master.sas -ICON -LOG c:\Users\Desktop\TestingCodeSAS1.LOG"
End Sub

or

Sub M_snb_001()
   Shell """C:\Program Files\SASHome\SASFoundation\9.3\sas.exe"" -CONFIG ""C:\Program Files\SASHome\SASFoundation\9.3\nls\en\sasv9.cfg"" AUTOEXEC ""C:\user\desktop\Autoexec.sas"" -SYSIN ""c:\Users\Desktop\Master.sas"" -ICON -LOG ""c:\Users\Desktop\TestingCodeSAS1.LOG"""
End Sub

[DavidLee91]

Hi Paul,

That was a stroke of genius in explaining quotes (") to a new VBA programmer like me! Thanks I got it all worked out!

To snb,

It works!!! Thanks so much!!

To Ken,

Thanks you for pointing out to Chip Pearson ShallandWait() routine. It is really very useful!

To All Reader,

Please check out Ken Post #3 for Shell Command Post and Chip Pearson's ShellAndWait() routine.


Thank you for all your help guys!

Regards,
David