By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

How to import dynamically named file

P: 1
Hello

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

My macro written as follows:
Expand|Select|Wrap|Line Numbers
  1. Sub TestTransferText()
  2.  
  3. DoCmd.TransferText acImportDelim, , _
  4.    "tblportfolio", "M:\AACP\AACP PORTFOLIO 09-19-2008" & Format$(Date, "mm-dd-yyyy") & ".csv", True
  5.  
  6. 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
Sep 23 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
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.TransferText acImportDelim, , _
"tblportfolio", "M:\AACP\AACP 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
Sep 23 '08 #2

NeoPa
Expert Mod 15k+
P: 31,347
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!
Sep 23 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.