imported text file with variable name (1 Viewer)

[diversoln]

I'd like to use a macro to import a text file that has the date as an integral part of the name such as WL-Jul13.txt or WL-Jul14.txt Each day, I want the user to start Access, have the user select the file to be imported (usually the for current date - but not always) and then Access will manipulate the delimited data file and print a customized report.

The users do not know Access so this needs to be user friendly and secure from user mistakes.

Once the user has entered the report date, I'd like to build a variable for the report name from the date. But it doesn't seem like I can use a variable for the filename in the macro design view. I thought maybe I could do this in SQL code but I'm not sure how to make it part of the macro.... Any ideas? THANKS!

 

[namliam]

If you use vba you can use the filename, no problem....

Greetz

 

[diversoln]

Mailman,


Your post, while providing encouragement that what I need to do can in fact be done, doesn't really tell me a whole lot. Please offer some advice to point me in the right direction......

Where do I hook in the VBA? I am currently using the macro to import the file and perform a variety of clean up issues. Can I still use the macro design view interface and simply call the VBA routine to import the file. What would the code look like?

 

[namliam]

sub Test()
    Dim myFile as string
    dim myfolder as string
    myFolder = "C:\"
    myfile = dir(myfolder & "*.txt") 'this receives the filename ONLY
    do while myfile <> ""
        DoCmd.TransferText acImport, "specname", "tableName", myfolder & myfile, , 'Check out  the help for the precise syntax
        'do more cleaning and stuff here
        myfile = dir 'no need to add () or anything
    loop
end sub

Above code will import all files in a folder. but with some imagination you can build the filename yourself.....

Greetz & GL