Search code examples
excelvbams-accessfile-formatsave-as

Using SaveAs Function, but periods change the fileformat

I open an Excel file, fill some cells and then save it in a new folder.

The generated files include today's date that includes periods.

If the filename for example is "Template_Name_01.01.2022" the fileformat changes to .2022

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 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.
enter image description here

Is there a way to prevent the file format changing if periods appear in the name?

Solution

  • 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