[AccessD] A2K: Using the shell() function

I deal with this probelm simply by having the batch file create an emptry
folder. Then it goes on and does whatever else it need to do. Meanwhile the
VBA code looks for the folder and keeps looping while it sees the folder.

At the end of the batch file I delete the sentinel folder and that then
allows the VBA code to proceed. The DOS Box closes automatically at the
termination of the batch file. Here's the code I use as an example...


Sub CreateFileInventory(ByVal sRootFolder As String)
' This sub creates and runs a batch file which builds a list of all the
files in all the subfolders
' of the root folder defined in sRootFolder. This is stored in a text file
for further processing

Const BATCHFILE = "C:\LSFINV.BAT"
Dim strComLine As String
Dim strCommand As String
Dim fHandle As Long
    DoCmd.Hourglass True
    fHandle = FreeFile
    Open BATCHFILE For Output As #fHandle
    strComLine = "DIR " & quote(sRootFolder & "*.*") & " /s /b >
C:\LSDOC.LST"
    Print #fHandle, "c:"
    Print #fHandle, "CD \"
    Print #fHandle, "MD LSFinv"
    Print #fHandle, strComLine
    Print #fHandle, "rd LSFinv"
    Close #fHandle
    strCommand = Environ("COMSPEC") & " /c " & BATCHFILE
    Shell strCommand, vbMinimizedNoFocus
    sSleep 1000 ' pause long enough to have the flag folder created
    While FileExists("C:\LSFinv")
        DoEvents
    Wend
    DoCmd.Hourglass False
    Kill BATCHFILE
End Sub

... and here are the three small helper routines that it uses...
'''''''''''''''''''''''''''''''''''''''''''

Function Quote(aString) As String
    Quote = """" & aString & """"
End Function

'''''''''''''''''''''''''''''''''''''''''''
Private Declare Sub sapiSleep Lib "Kernel32" _
        Alias "Sleep" _
        (ByVal dwMilliseconds As Long)

Sub sSleep(lngMilliSec As Long)
    If lngMilliSec > 0 Then
        Call sapiSleep(lngMilliSec)
    End If
End Sub

'''''''''''''''''''''''''''''''''''''''''''
Function FileExists(strFile As String) As Boolean
  ' Comments  : Determines if the file exists
  '             Works for hidden files and folders
  ' Parameters: strFile - file to check
  ' Returns   : True if the file exists, otherwise false
  Dim intAttr As Integer
  On Error Resume Next
  intAttr = GetAttr(strFile)
  FileExists = (Err.Number = 0)
End Function


HTH

Lambert

> -----Original Message-----
> From:	Steven W. Erbach [SMTP:serbach at new.rr.com ]
> Sent:	Thursday, April 29, 2004 10:52 AM
> To:	Access Developers discussion and problem solving
> Subject:	[AccessD] A2K: Using the shell() function
> 
> Dear Group,
> 
> I'm in the thick of writing a custom EDI purchase order processing
> application in Access 2000. The 850 purchase orders are downloaded from
> the VAN (Value Added Network) mailbox using Windows FTP commands in a DOS
> batch file, and the additional documents (855 PO Acknowledgment, 856
> Advance Ship Notice, 810 Invoice, and 997 Functional Acknowledgment) are
> uploaded to the mailbox with another DOS batch file using Windows FTP
> commands.
> 
> The process works, but I've had to insert MsgBox statements in the Access
> code alerting the user that he should wait until the DOS batch file has
> completed processing the FTP commands and is closed before continuing with
> the Access stuff.
> 
> My question is this: do any of you lot have any techniques for 1)
> determining when a DOS batch file has finished (that is, the title bar of
> the DOS window says "Finished"); and 2) closing those windows without user
> intervention?
> 
> I'm just about ready to tell my client that he should buy WS_FTP Pro so
> that we can set up a macro or whatever you do in WS_FTP to automagically
> download and upload and close the WS_FTP program. I'm still not sure about
> what to do in Access so that it knows that an external application has
> completed its operations. Is this a DDE thing?
> 
> I've been experimenting with the FMS Total Visual Sourcebook that I bought
> some time ago. It has a class library and some sample code for logging
> into an FTP site and selecting and downloading files. I just know,
> however, that my client will balk at the additional delay while I figure
> out an Access-only method for getting and sending EDI documents.
> 
> The DOS batch thing works but there are the additional clicks involved
> with closing the DOS window and then clicking the MsgBox OK button to
> continue processing. Any ideas?
> 
> Regards,
> 
> Steve Erbach
> Scientific Marketing
> Neenah, WI
> 920-969-0504
> 
> "One of the differences in the two parties is that the Republicans always
> have problems filling many of the offices that are political, because most
> competent people would rather do something other than work for the
> government..." - Jerry Pournelle
> 
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com