Wildcard in file Import 1

[Trudye]

I have a file named "NUyymmddhhmi.DAT" that I need to import into a table. Because the file is not created at the same time everyday, I tried a wildcard for the hhmmi portion of the filename. The problem is the acImport command requires an exact filename. Of course it did not work but I figured nothing ventured.....

Does anyone out there have any ideas, suggestions, evern WAGs are acceptable?

Thanx
Trudye

 

[Trudye]

Sorry about that I meant hhmm

Trudye

 

[rdandrade]

use a do loop for each file. Do them one at a time. That's the only way.

I haven't tested this, but it should go something like this:

dim strTmp as string
dim strDirectory as string

strDirectory = "C:\MyDirectory\NU051108*.dat"
strTmp = Dir(strDirectory)

do Until strTmp=""
   [your code here...strTmp contains the current file name, one at a time]
   strTmp = Dir
loop

Ray D'Andrade

http://www.raydandrade.com

 

[Trudye]

THank you so much rdandrade for responding so quickly.

Will the wildcard pull all files with that matching string? The reason I ask is I won't know the exact file name at run time. However if I have one file in the default folder with that date and that extension then I'm good to go.

 

[rdandrade]

I just tested it out and that should work for you using the wildcard. You don't have to know the exact file name. This code will give it to you using the wildcard mask, one at a time in the loop.


Ray D'Andrade

http://www.raydandrade.com

 

[Trudye]

Hi Ray, I am getting Run Time Error 2511. The action or method requires a Specification Name argument.

Here is the code I am running:
Dim strSPECS As String
Dim TodayIs As String
Dim strDirectory As String
Dim FullStr As String
strDirectory = "C:\DATA\RMKTG\"
strtmp = Dir(strDirectory)

TodayIs = "NU" & Format(Date, "yymmdd") & "*.DAT"

Do Until strtmp = ""
Lstrtmp = Left(strtmp, 8) & "*.DAT"
If Lstrtmp = TodayIs Then
FullStr = strDirectory & strtmp
DoCmd.TransferText acImportFixed, strSPECS, "CUST_STATE_CD_T", FullStr, 0
End If
strtmp = Dir
Loop

The variable FullStr is perfect, I verified using the Immediate Window (C:\DATA\RMKTG\NU0511090215.DAT).

 

[Trudye]

Sorry about the previous post. I figured it out, it's not the filename/wildcard it's the file extension. Access 2k does not accept *.DAT files. I have to change the extension to .TXT.

Thank you so much for your help.
Be well,
Trudye

 

[rdandrade]

Good...glad it worked. I had a crazy day today and I wasn't paying attention to this site to get back to you earlier.


Ray D'Andrade

http://www.raydandrade.com