VBA to save as existing file name with current date appended

[The_Boov]

Hi All

New member here just starting to progress from Access macros to VBA.

I am trying to write the VBA to save a file as the current file name but appended with the current date. I guess the starting point is DoCmd.Save but with this I either have to ...

1. Rename the whole file
2. Use the current name but then cannot append with the date

What I would like to do is have the code read the current file name and then append it with the current date before saving to the original location.

Is this possible?

Any help much appreciated.

Thank you

 

[gemma-the-husky]

filename = foldername & "\" & filenamepart & format(now,"yyyy-mm-dd-hhnnss") & requiredextension 'eg .txt
msgbox filename 'to test it

if you format the date this way, the dates will sort correctly for similar named files. Note then nn is used for minutes in the time part, as mm is used to format months.

There's help on formatting dates in access but mmm gives you a 3 letter month, and mmmm gives you a full text month. The same with dd. if you use text though, the sorting won't be correct. You can change the format string to get what you want. You can even do quarters and things like that.

Note that if you are hard coding the bold bits, they need to be in " characters


To amend an existing filename you need to split the file name based on the extension, then insert the date, which is quite easy.

so newfilename = leftpart of oldfilename plus date plus rightpart of oldfilename

there is even a name command to do this

name oldfilename as newfilename

 

[arnelgp]

you can use Name ..As directive?
copy and paste this in a Module:

Public Sub addDateToFile(ByVal sPath As String)
    Dim sDrive As String, sDir As String, sFile As String, sExt As String
    Dim sNewFile As String
On Error GoTo Err_Handler:
    If Len(Dir$(sPath)) = 0 Then
        Err.Raise 53
    End If
    With WizHook
        .Key = 51488399
        Call .SplitPath(sPath, sDrive, sDir, sFile, sExt)
    End With
    sNewFile = Trim$(sDrive) & Trim$(sDir) & Trim$(sFile) & "_" & Format$(Date, "yyyy_mm_dd") & Trim$(sExt)
    Name sPath As sNewFile
Exit_Sub:
        Exit Sub
Err_Handler:
        MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
        Resume Exit_Sub
End Sub

To use:

Call addDateToFile("CompletePathPlusFilenameToRenameHere")

 

[Gasman]

A little over the top for naming a file? , but appreciate the WizHook reference. Never even heard of it.

http://ftp-developpez.com/cafeine/access/access_wizhook.pdf

 

[theDBguy]

Hi. Welcome to AWF!

What kind of file are we talking about? Is it an external file? Or, are you talking about the current database file?

 

[The_Boov]

Hi. Welcome to AWF!

What kind of file are we talking about? Is it an external file? Or, are you talking about the current database file?

The current .accdb file.

Rather than having to hit Save As and manually type _COMPLETE_20212111 at the end of the file name I'd like to automate it. One of my staff recently forgot to suffix the file name with this which caused mayhem further down the line.

 

[gemma-the-husky]

Why would you need to rename the Access database files?

 

[Gasman]

Here is something I offered up at another site.
They were explicitly asking to save the DB

Post 2

UtterAccess.com

www.utteraccess.com

 

[moke123]

If its a back up your trying to do you can try this from Brent Spaulding on @theDBguy 's site

Code Snippets

edit: that WizHook looks interesting.

You can achieve a similiar result with fso

Public Function AppendToFileName(strPath As String, AppendString As String) As String


    Dim fso As New FileSystemObject


    AppendToFileName = fso.BuildPath(fso.GetParentFolderName(strPath), AppendString & fso.GetBaseName(strPath) & "." & fso.GetExtensionName(strPath))


End Function

 

[Pat Hartman]

I am trying to write the VBA to save a file as the current file name but appended with the current date.

How do you determine the current file name of the file you want to save?
Are you just renaming the file or are you exporting new data with a new file name that includes the date?
How are you exporting the data?

 

[Gasman]

If its a back up your trying to do you can try this from Brent Spaulding on @theDBguy 's site

Code Snippets

edit: that WizHook looks interesting.

You can achieve a similiar result with fso

Public Function AppendToFileName(strPath As String, AppendString As String) As String


    Dim fso As New FileSystemObject


    AppendToFileName = fso.BuildPath(fso.GetParentFolderName(strPath), AppendString & fso.GetBaseName(strPath) & "." & fso.GetExtensionName(strPath))


End Function

It does, but if you try and copy any code from it, it is gibberish

 

[The_Boov]

Why would you need to rename the Access database files?

It's the renaming of the database itself once closed down. When it has been worked on the staff should Save As and suffix it with _COMPLETE_20212111 but if they forget, which happened recently, it causes significant problems. I just want to automate the process.

 

[The_Boov]

How do you determine the current file name of the file you want to save?
Are you just renaming the file or are you exporting new data with a new file name that includes the date?
How are you exporting the data?

Let's say the file that is being worked on is called 'Database1.accdb' ... when the staff member is finished working on it he/she should save it as 'Database1_COMPLETE_20211211.accdb'

This is so we can differentiate between the worked-on version and the original which is held in the project folder.

 

[theDBguy]

Let's say the file that is being worked on is called 'Database1.accdb' ... when the staff member is finished working on it he/she should save it as 'Database1_COMPLETE_20211211.accdb'

This is so we can differentiate between the worked-on version and the original which is held in the project folder.

Hi. What does "worked on" mean? Are you talking about form and report design changes? If you're just talking about adding or updating the data, it sounds like you don't have a split configuration. Do you?

 

[The_Boov]

Hi. What does "worked on" mean? Are you talking about form and report design changes? If you're just talking about adding or updating the data, it sounds like you don't have a split configuration. Do you?

By 'worked on' I just mean that the data in the tables has been edited. I don't know what a split configuration is so I'm assuming I don't have one.

 

[Gasman]

Right. You need to split the forms etc and the data.
The data, normally called the Backend (BE) is a file in it's own right with all the data.
The front end (FE) is where all the forms, queries,reports are stored.

You save the BE daily at least if not more.
You save the FE when it gets changed.
Every user needs their OWN copy of the FE, not a shared copy somewhere.

 

[The_Boov]

Right. You need to split the forms etc and the data.
The data, normally called the Backend (BE) is a file in it's own right with all the data.
The front end (FE) is where all the forms, queries,reports are stored.

You save the BE daily at least if not more.
You save the FE when it gets changed.
Every user needs their OWN copy of the FE, not a shared copy somewhere.

Ah, apologies ... in that case I believe I do have a split configuration.

So the original DB is stored on the BE. A copy of that DB is then provided to a data validator. The validator uses queries to manipulate the data in the tables (of the copy). Once this process is complete the file is saved as the original DB name but suffixed with _COMPLETE_20212111

Hopefully that makes sense.

 

[Gasman]

No, that does not sound split, merely a copy which is saved
Allowing/making users save data is not a good idea as you have already found

 

[theDBguy]

Ah, apologies ... in that case I believe I do have a split configuration.

So the original DB is stored on the BE. A copy of that DB is then provided to a data validator. The validator uses queries to manipulate the data in the tables (of the copy). Once this process is complete the file is saved as the original DB name but suffixed with _COMPLETE_20212111

Hopefully that makes sense.

If you had a split configuration, you can take a look at this function on how to create a backup copy of the BE (data file) with a date appended to the file's name.

Code Snippets

 

[CJ_London]

wizhook has some useful features, but it is undocumented. Colin has a number of links.
In addition to the splitpath used by Arnel, I've used the sortstringarray and twipsfromfont (in place of Stephen Lebans textWidth function) functions