Shell Function Alternative?

[Waynest]

We have here a command line utility which converts files created in unix to dos format.

When importing files from unix into SQL I use the Shell function to convert them with the utility before the VBA code which loads in the data executes. I realise Shell is asynchronous and have coded in longer and longer wait periods, but sometimes the VBA runs before the shell command is finished and I only get part of the import data.

I should probably set up a solution using a DTS, but at the moment I just want a quick fix to make it work as is.

Any suggestions?

Thanks

 

[CajunCenturion]

First thing I would try is to use the Windows Shell which allows you to execute a program and wait for its completion.
First you need to add the Windows Host Scripting Object as a reference into the code. The you can try the following code:

Dim RetVal As Long
Dim WSH As IWshShell
Dim WaitForTerm as Boolean
Dim CommandLine as String

Set WSH = New IWshShell_Class
CommandLine = "notepad.exe" ' Insert your own cmdline
WaitForTerm = True
RetVal = WSH.Run(CommandLine, vbNormalFocus, WaitForTerm)
MsgBox "Process returned: " & RetVal ' this not necessary
Set WSH = Nothing

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[GTLoco99]

Or you can try this. Shell returns the task ID as a double if the application is launched successfully. If it is not, it returns a 0. You can try writing an if statement in a loop to check for this value. If it is not 0, then the app should be open

Good Luck
GTLoco

 

[Waynest]

Thanks for the replies.

I do not see the Windows Host Scripting Object listed in the available references?

 

[CajunCenturion]

Try looking for Windows Script Host Object Model.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein