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

How can I transfer text file with strange name?

P: 5
How can I transfer text with the following filename in Access 2002?:

somefilename.ccyymmdd (where "ccyymmdd" is the current date)

I would be able to do if the file name was: somefilename_ccyymmdd.txt

Thanks ahead of time.
Jul 27 '12 #1
Share this Question
Share on Google+
7 Replies

Expert Mod 2.5K+
P: 3,205
Can you change the file name extension so that it is a .txt file? If it is a text file, I don't think Access cares what the extension is, as long as it is in an appropriate format.

Is the filename required for anything, i.e., do you need to extract the date for any reason?
Jul 27 '12 #2

P: 5
Thanks for replying. The file name extension cannot be changed. The date stamp is needed for archiving purposes. So I'm stuck with the strange and dynamic file name. I changed the extension to .txt and ran the transfer. MS Jet Engine is having a problem with the two periods in the file name. So I have 3 issues: (1) "capturing" the right date, (2) changing the file extension to something that is valid, (3) the two periods. Was thinking of changing the first "." to "_". Appreciate any advice.
Jul 27 '12 #3

Expert Mod 2.5K+
P: 3,205
For (1), to capture your date accurately, try this:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Sub GetDate(strFileName as String) as Date
  5. On Error GoTo EH
  6.     GetDate = CDate(Right(strFileName, 8))
  7.     Exit Sub
  8. EH:
  9.     MsgBox Err.Number & " " & Err.Description
  10.     Exit Sub
  11. End Sub
You might have to play with the syntax you send into the CDate function, because it may or may not fully recognize the text of the file name as a date.

Concerning (2) and (3), how are the files saved in the fist place? If you change how they are saved initially, i.e. if, when the files are generated, they are saved as somefilename_ccyymmdd.txt, then you are golden. Otherwise, there are ways to rename files (or copy, paste under a new name and delete).

I think your first step of capturing the date is key, though.
Jul 27 '12 #4

Expert Mod 2.5K+
P: 3,205
Forgot to include this: The easiest way to rename files from VBA is this:

Expand|Select|Wrap|Line Numbers
  2. Name OldName As NewName
Just be sure to include the entire directory path and full file name.
Jul 27 '12 #5

P: 5
Thanks for your help. I will work with the code you sent and let you know how it goes.
Jul 27 '12 #6

P: 5
It works. I forgot to mention that the Access job will run only at the beginning of the month, so I used the Date() function to get the current date, which will always be the file date. Did not know about the VBA Name statement. That did the trick.

I created a hidden form. "On Open" the form executes this code:

Private Sub Form_Open(Cancel As Integer)
Dim OldName, NewName
OldName = "\\servername\users$\myname\My Documents\testfolder\somefilename." & Format(Date(), "yyyymmdd"): NewName = "\\servername\users$\myname\My Documents\testfolder\somefilename_" & Format(Date(), "yyyymmdd") & ".txt" ' Define file names.
Name OldName As NewName ' Rename file.
End Sub

Then when I import the file ("TransferText"), this is the file name location:

="\\servername\users$\myname\My Documents\testfolder\somefilename_" & Format(Date(),"yyyymmdd") & ".txt"
Jul 27 '12 #7

P: 5
Thanks again for your help!
Jul 27 '12 #8

Post your reply

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