Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 15th, 2010, 01: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

EarlyExit:
On Error GoTo 0

IsFileFolderExist = retVal 'return value

Application.ScreenUpdating = True
End Function

Thank you for your help in advance,
Dor
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 07: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 07:49 AM



All times are GMT -4. The time now is 03:25 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.