Wrox Programmer Forums
BOOK: Excel 2003 VBA Programmer's Reference
This is the forum to discuss the Wrox book Excel 2003 VBA Programmer's Reference by Paul T. Kimmel, Stephen Bullen, John Green, Rob Bovey, Robert Rosenberg, Brian Patterson; ISBN: 9780764556609
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel 2003 VBA Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 15th, 2010, 02:37 PM
Registered User
Join Date: Dec 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Test if Cmd prompt is open

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

On Error GoTo 0

IsFileFolderExist = retVal 'return value

Application.ScreenUpdating = True
End Function

Thank you for your help in advance,

Similar Threads
Thread Thread Starter Forum Replies Last Post
open source online test/exam system surendran PHP How-To 5 August 22nd, 2010 07:10 AM
Open CMD, NSLookup, enter name, return IP mmcdonal VBScript 3 December 19th, 2006 08:40 AM
TEST THE MYSQL IN THE COMMAND PROMPT jhan316 SQL Server 2005 0 June 25th, 2006 09:16 AM
how to call shell cmd to open exe on clientside? nana Javascript How-To 6 May 25th, 2006 03:23 AM
cmd.qry mohiddin52 Access 0 December 29th, 2003 08:49 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.