How can I transfer text file with strange name?

[NewAccessCoder]

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.

[twinnyfo]

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?

[NewAccessCoder]

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.

[twinnyfo]

For (1), to capture your date accurately, try this:

Option Compare Database
Option Explicit
 
Private Sub GetDate(strFileName as String) as Date
On Error GoTo EH
    GetDate = CDate(Right(strFileName, 8))
    Exit Sub
EH:
    MsgBox Err.Number & " " & Err.Description
    Exit Sub
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.

[twinnyfo]

Forgot to include this: The easiest way to rename files from VBA is this:

 
Name OldName As NewName
 
 

Just be sure to include the entire directory path and full file name.

[NewAccessCoder]

Thanks for your help. I will work with the code you sent and let you know how it goes.

[NewAccessCoder]

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"

[NewAccessCoder]

Thanks again for your help!