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.