Tip

Transfer Downloads Automatically

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.


This was first published in November 2000

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.