Transfer Downloads Automatically
A while ago we sent out a tip about making a program wait for an external process to complete. The tip included code for the wait. Now reader Robert F. Jene sends a tip about transferring text from a download after the wait is over.
Here is a technique that I use to automate the import of data into an Access database after copying it from its download location on a server. The code must wait for the copy to finish before starting the TransferText function.
Public Function importData()
Dim flgFle As String, srcFle As String, txtFle As String, tmpFle As String
Dim batFle As String, Rslt as Variant
Const dwnPth="bdc_serverDL"
On Error Goto impErr
' Set up file path/name variables
tmpFle = Environ("temp")
srcFle = dwnPth & "import.txt"
txtFle = tmpFle & "newdata.txt"
flgFle = tmpFle & "jobfin.flg"
batFle = tmpFle & "temp.bat"
' Make sure the flag file and batch file do not exist
If Dir(flgFle) <> "" then
Kill(flgFle)
End If
If Dir(batFle) <> "" then
Kill(batFle)
End If
' Create batch file
Open batFle For Output As #1
Print #1, "Copy " & srcFle & " " & txtFle
Print #1, "echo '" & txtFle & " has been copied' > " & flgFle
Close #1
' Run the batch file and wait for it to complete
Rslt = Shell(batFle, vbHide)
Do
Loop Until Dir(flgFle) <> ""
' Import the text file to a database table
DoCmd.TransferText acImportDelim, "NewData import specification", _
"tblImport", txtFle, False
' Delete the batch file, the flag file and the text data file
Kill(batFle)
Kill(flgFle)
Kill(txtFle)
Exit Sub
impErr:
Msgbox "Error importing data: " & Err.Number & " - " & Err.Description
End Sub
Thanks, Robert, for your interest in SearchVB.com. As a thank-you for your effort, we'll be sending out a SearchVB denim shirt.
Robert F. Jene has been in computer programming for over forty years after getting his feet wet on the SAGE air defense system. After several more military programming jobs he migrated back to the private sector where he has been an applications developer and/or maintenance programmer ever since. The last few years he has been working more on PC's with the client side of Oracle systems, stand alone Access systems with both Access and SQL Server backends and C++ applications.
