How to import dynamically named file

[jennise]

Hello

I am using the DoCmd.TransferT ext to write a macro to import dynamically named file from a filepath.

My macro written as follows:

Sub TestTransferText()

DoCmd.TransferText acImportDelim, , _
   "tblportfolio", "M:\AACP\AACP PORTFOLIO 09-19-2008" & Format$(Date, "mm-dd-yyyy") & ".csv", True

End Sub

When I run the macro - Access prompt the below error:
"The Microsoft Jet engine could not find the object 'AACP PORTFOLIO 09-19-200809-233-2008.csv'. make sure the object exists and that you spells its name and path name correctly."

Checked the filepath - it is correct. I have checked the 3 files which I have named AACP PORTFOLIO 09-19-2008, AACP PORTFOLIO 09-22-2008 and AACP PORTFOLIO 09-23-2008; they are named correctly

I am not sure where I went wrong. Please advise

[Stewart Ross]

Hi jennise, and Welcome to Bytes!

You have rather answered your own question. You have TWO dates run together in the filename you are passing to TransferText, one a constant and the other a formatted date, as you show yourself:

DoCmd.TransferT ext acImportDelim, , _
"tblportfol io", "M:\AACP\AA CP PORTFOLIO 09-19-2008" & Format$(Date, "mm-dd-yyyy") & ".csv", True

This in turn leads to a correct error message as that file cannot be found. You again show this yourself:

"The Microsoft Jet engine could not find the object
'AACP PORTFOLIO 09-19-200809-233-2008.csv' (I am assuming that the 09-233-2008 part is just a typo).

If you remove the constant '09-19-2008' from your DoCmd string you should be able to assign the correct filename.

If you are trying to import a range of file names I would suggest you rethink how you are approaching this. Dates added as strings to other text do not order correctly unless you reverse them into YYYYMMDD form. Even then, you will not be able to import multiple files using TransferText simultaneously.

First step is to get the filename right, then worry about importing more than one after you can import the first one.

-Stewart

[NeoPa]

I suggest you check the name again more thoroughly.

The name in the error message has the variable date appended AFTER "09-19-2008". This certainly doesn't (wouldn't be expected to) match any of the names listed ;)

Welcome to Bytes!