Automatic import using import Spec

[Arry]

I have written some VBA code to import a file with a click of a button from a CD. The file is not always the same name but always follows the following:

D:\????f3??.vp

Here is the code.

DoCmd.TransferText acImportDelim, "550 Invoice Records Import Specification", "Tab 550 Current Month", Dir("D:\????f3??.vp"), False, ""

The problem with this is that it cannot find the specified wildcard path unless i manually use the Get External Data function, select my import Spec, cancel it and then run the above code.
Likewise if i replace Dir("D:\???f3??.)vp with the actual file location, ie D:\2170f323.vp, it also works. Any ideas what i have missed.

Cheers

Arry

 

[Mile-O]

Use the InStr() function...not wildcards...

 

[Arry]

I have had a quick llok at the instr() function but don't understand how i incorporate it. Could you explain further.

Cheers

 

[Mile-O]

If you have one file on a CD with the following name: ????f3??.vp where ? is a single character wildcard then you can loop through the files on the CD, find one that matches.

Actually, forget the InStr() function; Mid() is what you want...


i.e. (and I don't do file coding, so this is just a suggestion, not actual code)

For Each File In CD
   If Len(File.Name) = 8 Then
       If Mid(File.Name, 5, 2) = "f3" Then
            strMyFile = File.Name
       End If
   End If
Next

DoCmd.TransferText acImportDelim, "550 Invoice Records Import Specification", "Tab 550 Current Month", Dir("D:\" & strMyFile & ".vp"), False

As long as you get the idea...maybe someone has a better idea or just knows....