Microsoft Access VBA - Using SaveAs Function, but periods change the fileformat-CodePudding

I am Trying to open an excel file, fill some cells and then save it in a new folder.

The problem is, that the generated files include todays date, and the dates include periods. If the filename for example is "Template_Name_01.01.2022" the fileformat changes to .2022

My Code looks like this:

Dim OriginalFileName As String
fileName = "Template_" & Nz(rs!Street, "Address") & "_" & Date
    
OriginalFileName = fileName
Dim fileNumber As Integer
fileNumber = 1

Do Until nameFree = True
    
    nameCheck = Dir("G:\Argus\_Deal Tracker 3.0\Deals_Inv Mgmt\" & fileName)
    
    If nameCheck = "" Then
        xlBook.SaveAs fileName:="G:\Argus\_Deal Tracker 3.0\Deals_Inv Mgmt\" & fileName, FileFormat:=xlOpenXMLStrictWorkbook
        nameFree = True
    Else
        fileName = OriginalFileName
        fileName = fileName & " (" & fileNumber & ")"
        fileNumber = fileNumber   1
    End If

Loop

Even though I determine the fileFormat it still saves the file as .2022

Saved files

If I add an ".xlsx" Extension to the filename it works for me but not on other PCs, I am guessing it is because they have file extensions hidden.

If they run the function they get this error.

Error Message

So is there a way to prevent the file format to change if periods appear in the name?

Thanks

CodePudding user response：

You need to format the Date to remove the forward slashes / from the file name as they're not allowed. You also need to supply the file extension in the path.

So, change this:

fileName = "Template_" & Nz(rs!Street, "Address") & "_" & Date

to this:

fileName = "Template_" & Nz(rs!Street, "Address") & "_" & Format(Date, "dd.mm.yyyy") & ".xlsx" 'change to your extension

CodePudding user response：

Performing the Save As from script, you have to contruct the whole filename, including the extension.

Basically you have to add something like:

fileName = "Template_" & Nz(rs!Street, "Address") & "_" & Date & ".csv"