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

Microsoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon

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?

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

>>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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Thank you for all your help. Worked perfectly.

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