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

TransferText Import with .txt file name variable

P: 7
I have an access database that uses vba to retrieve .txt files from a specific location and places them in specific tables within the access database. Previously (many years), the .txt file names have always been static (e.g.: ErrRpt.txt). Now, the audience that uses this database has changed the .txt file names to include a date (YYYMMDD) that is appended daily (e.g.: ErrRpt_20090520.txt).

Can someone help me change the following line of code (an example of many lines that I need to change) to import the .txt file with varying dates?
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferText acImportDelim, "ErrRptSpec12", "ErrRpt_Feed", ImportDIR & "ErrRpt.Txt"
Thank you in advance for any help!
May 20 '09 #1
Share this Question
Share on Google+
10 Replies

Expert 100+
P: 1,356
Something like this should work
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferText; acImportDelim, "ErrRptSpec12", "ErrRpt_Feed", ImportDIR & "ErrRpt" & Format(Date, "yyyymmdd") & ".Txt"
May 20 '09 #2

P: 7
nice! let me take it for a spin. brb. thank you
May 20 '09 #3

P: 7
Great help Denburt!!!! If i needed to had time (HHMM) to the format how would that look?

like this maybe?

& Format (date, "yyyymmdd", time "hhmm")
May 20 '09 #4

Expert 100+
P: 1,356
Glad I could be of help.

Date() gives just the date now() will give you the date and time. I used a - for the separating character if it is a different character simply replace it and you should be good.

Format (now, "yyyymmdd-hhmm")
May 20 '09 #5

P: 7
Good Stuff! i'll give it a try. Thanks again!
May 20 '09 #6

P: 7
is there a website out there that i could use (instead of bugging you) to lookup codes and what they do (e.g.: date () and now())??? thoughts?
May 20 '09 #7

Expert 100+
P: 1,356
Not that I can think of off hand although I am having trouble thinking period right now lol long day ya know. Fire away we don't mind that's why we are here.

Tip:1. in your VBA window type a command such as Now() then highlight it hit F1 the help window should popup and give you places to look for plenty of more info.

2. in the VBA window F2 will bring up the Object browser and may give you some ideas on things.

Good luck and Happy Coding
May 20 '09 #8

P: 7
Excellent! You will probably hear from me again soon. Thanks again!
May 21 '09 #9

Expert Mod 15k+
P: 31,709
The Help system gives access to web based help as well, should you feel that's necessary, from version 2003 onwards I believe. Context-sensitive help can answer most of your questions though.

I believe there is something on the F2 (Object Browser) option in Debugging in VBA. This is a gem that not every Access user ever finds. A good tip.
May 25 '09 #10

P: 7
Thanks for all the help, I think I'm almost there..Here what my problem is:

File names are consistent but the date (YYYYMMDD) and time (HH) change. We get data in at six times a day 2AM, 6AM, 10AM, 2PM, 6PM, and 10PM (these time are approximate and not precise or consistent at all). Here is the file name (for reference): ErrRpt_20090520_13.txt.

Here is an example of what I cannot seem to figure out on my own:
Let's say we get files at 6pm & 10pm on a Friday. Then I come in on Monday at 7am, I will want to pull in the data from the files for Friday at 6 & 10pm as well as the Monday file at 6am.

Right now Iím using the following code: DoCmd.TransferText acImportDelim, "ErrRptSpec12", "ErrRpt_Feed", ImportDIR & "ErrRpt_" & Format(Date, "yyyymmdd_hh") & ".txt"

I've used Now, "yyyymmdd_hh" etc... but not quite it either.

Sorry to be such a pain obviously Iím not a VBA person. Any help would be great!
May 27 '09 #11

Post your reply

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