Link to home
Create AccountLog in
Avatar of vdornan

asked on

Renaming a file with a not always current date contained within it

I am sure this has been asked many times, but is there a way within Access VBA to rename a file that has a date contained within it. The thing is that the date is not always a current date. My understanding is that you can't use wildcards while renaming a file.

I have attached the piece of code I use for renaming a file with the current date, but not sure how go about if its not a current date.

Any help would be great. Thanks.
Function Rename()
	Name "File Name " & Format(Now(), "M-D-YYYY") & ".xlsx" As "File Name.xlsx"
End Function

Open in new window

Avatar of rawinnlnx9
Flag of United States of America image

Figure out a way to call a .bat file from within .vbs and then use this as an example to get you going.
You have it backwards.
The syntax is Name [current filpath and name] As NewFilePathAndName
If you are not going to use the current date, what are you going to use?  User supplied entry?
Avatar of Nick67
There are a bunch of ways to do this.
I would use the FileSystemObject. (Look for it in the Help)
(But since the Access 2010 help is so useless, Google VBA FileSystemObject, or look in the Object browser for all the methods and properties)
After you get it pointed a a folder, you can obtain a Files collection

You can then loop through each File in Files, check the name and rename it whatever you'd like.
The file name is just a string after all, so any vaild string you can create can be used.
In each loop alter the string as required
Avatar of vdornan


The idea is to take the date off of the file. The filename needs to be consistent to be linked within the database. Since the date is not always current when the job runs, the job halts since it can't rename the file. I am not familiar with looping using VBA code. So any help would be appreciated. Thanks.
How is the file selected?  Is it always in a specific location, or does a user select the location?
Is the file name always the same? Is there a pattern to the name?
It might be possible to check if the last 8-10 characters (before '.xlsx') constitute a date, and then remove them from the string for renaming.
I am afraid your description of your problem is to vague for me to make sense of what you require.
You have a file that has a name something 8-12-2011.xlsx that your routine will rename properly because today is the 12th.
On occasion the file does not have today's date and so you have a problem.

How far back in time you may need to go is an open question.
Let's say 30 days for fun
Calling your function Rename is a bad idea--that may be taken by VBA already

Function MyRename()

Dim myPath As String
Dim myFilename As String
Dim x As Integer

myPath = "C:\windows\" '<-----------------set this to what you need

For x = 0 To 30
    'DateSerial creates a number that Office recognizes as a date
    'we can do math (ie subtract x from it) with that number
    'Format then converts it to a string we'd like to see
    myFilename = Dir(myPath & "FileName " & Format(DateSerial(Year(Now()), Month(Now()), Day(Now()) - x, "M-D-YYYY")))
    If myFilename <> "" Then 'we found a file, "" is returned when dir finds no match
        Name myFilename As "FileName.xlsx"
        Exit Function
    End If
Next x

End Function

Open in new window

This will look back sequentially 30 days from today for a file named with your format and rename it.
Avatar of vdornan


I have a zip file that is emailed to me most everyday. I have a script that will extract the file from the zip file and places it in the same location. The file always is named the same with the date contained within it. Ex. File name 8-12-2011.xlsx. I need to remove the date so the file is called File Name.xlsx.

I have no problem if the file comes in with the current date, but if I don't receive the file, then I need to use the previous days which can be anywhere up to a week old.

So I am looking for a job that can loop thru like

If "File Name " & Format(Now(), "M-D-YYYY") & ".xlsx" or "File Name " & Format((Now()-1), "M-D-YYYY") & ".xlsx" or  "File Name " & Format((Now()-2), "M-D-YYYY") & ".xlsx"
Name "File Name " & Format("M-D-YYYY") & ".xlsx" As "File Name.xlsx"

I know its a little confusing. Thanks again for all the help.
OK, so go through your folder,  check for today's date in the folder.  If it is not there, go back 1 day at a time until you get a file.
What is length of the filename? Without the date and .xlsx?
dim yourpath as string
dim ffile as string
dim ffile2 as string
dim i as integer
for i = 0 to 6
ffile = dir("yourpath\Filename" & format(now() + i, "M-D-YYYY"))
if not (ffile = "") then
ffile2 = "FileName.xlsx"
name yourpath & "\" & ffile as ffile2
exit function
next i
>>ffile = dir("yourpath\Filename" & format(now() - i, "M-D-YYYY"))

a little boo-boo
Oops, I did not read prior entries.  Disregard.
To clarify:  Nick67 has provided you with a solution.  I inadvertantly posted almost exactly the same solution.  Please disregard.
Avatar of Nick67
Flag of Canada image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of vdornan


Thank you for all your help. Worked perfectly.
Avatar of vdornan


I just wanted to thank everyone for helping me with this.