Hello everyone,

I humbly need help...I need to know how to test if the cmd prompt is still open or execute the next instruction when the cmd prompt is closed.

The situation is that I created a macro that would create 2 files - bat file and script file, which is read by the bat file. The bat file extracts data from a host and it takes some time before it finishes the extraction. My code logically deletes those 2 files AFTER the SHELL is invoked but it displays an error since the bat file is not yet done extracting.

I am tempted to increase the Application.Wait Time by this is not efficient since the extraction may vary.

Here's my code (NOTE: I' a newbie and I only use what others has posted online...I thank them all. I modify a little bit for me to use...):

'Subroutine ExtractDataSourceMacro calls other subroutines related to CMD operation only
Public Sub ExtractFileFromCSGU(ByVal ppUserName As String, _
ByVal ppPassWord As String, _
ByVal ppMember As String, _
ByVal ppFilename As String, _
ByVal ppPath As String)
Application.ScreenUpdating = False


'-----STEP 01: create batch file and run in command prompt using ftp
'-----prepare ftp command
quoteSign = """"
ftpInstruction = "HostToPCTransfer.txt"

ftpCommand = "ftp -n -s:" & _
quoteSign & ThisWorkbook.Path & "\" & ftpInstruction & quoteSign _
& " mywebsite.ca > " & _
quoteSign & ThisWorkbook.Path & "\" & "HostToPCTransferLog.txt" & quoteSign

'-----create temp bat file
myFile1 = ThisWorkbook.Path & "\" & "FTP_Extract.bat"
If IsFileFolderExist(myFile1) Then Kill (myFile1)

Open myFile1 For Output As #1
Print #1, ftpCommand
Close #1
Application.Wait Time + TimeSerial(0, 0, 1)

'-----create ftp instructions file and ftp file
'---check and delete if the file to be generated exists
If IsFileFolderExist(ppFilename) Then Kill (ppFilename)

myFile2 = ThisWorkbook.Path & "\" & ftpInstruction
If IsFileFolderExist(myFile2) Then Kill (myFile2)
Open myFile2 For Output As #2
Print #2, "user " & ppUserName
Print #2, ppPassWord
Print #2, ""
Print #2, ""
Print #2, "quote site lrecl=80 recfm=fb tr pri=100 sec=50"
Print #2, "ascii"
Print #2, "get"
Print #2, "'" & ppMember & "'"
Print #2, quoteSign & ppPath & "\" & ppFilename & quoteSign
Print #2, ""
Print #2, "Quit"
Close #2
Application.Wait Time + TimeSerial(0, 0, 1)

'-----execute generated batch file

Dim retVal As Integer
retVal = 0
retVal = Shell(myFile1, vbReadOnly)
Application.Wait Time + TimeSerial(0, 0, 1)

'-----delete temp files
If IsFileFolderExist(myFile1) Then Kill (myFile1) '.............error here
If IsFileFolderExist(myFile2) Then Kill (myFile2) '.............error here

Application.ScreenUpdating = True
End Sub

'This function returns true if the folder or file exists, otherwise false
Public Function IsFileFolderExist(ByVal ppFullPath As String) As Boolean
Application.ScreenUpdating = False

'declare and initialize returning value
Dim retVal As Boolean
retVal = False

On Error GoTo EarlyExit
If Not Dir(ppFullPath, vbDirectory) = vbNullString Then retVal = True

EarlyExit:
On Error GoTo 0

IsFileFolderExist = retVal 'return value

Application.ScreenUpdating = True
End Function

Thank you for your help in advance,
Dor