Saving Excel files through Access

[gsandy]

I am using Access to save an Excel file in a particular directory. The code was created about 10 years ago and hence saves the file with .xls extension and I would like to save with a .xlsm extension. If I just change the .xls to .xlsm, in the code below, the file is saved but the Compatibility Checker comes up (I would like to stop this) and when the file is reopened I get an error message stating there is a file extension problem and the file won't open. Any help would be appreciate, thanks Sandy.

strExt = ".xls"
              
            
            'If .txtFriendlyName <> "" Then
            '    strDestFileName = .txtFriendlyName
            'Else
            '    strDestFileName = Left(objFile.Name, Len(objFile.Name) - 4) & MakeFilePosfix(Now())
            'End If
            
            ' Make file name with mask [JOB|QUOTE]_ITEM_NUMBER_DD_MM_YY
             strDestFileName = Left(objFile.Name, Len(objFile.Name) - 4) & "_"  & strExportTo & "_" & strItemNumber & "_" &  MakeFilePosfix(Now())
            strTempPath = strFullPath & strDestFileName & strExt

 

[ByteMyzer]

Could you show us the line(s) of code you are using to save the file?

 

[SteveH2508]

Setting the DisplayAlerts property of the Application object to False seems to disable the compatibility checker.

 

[gsandy]

Hi ByteMyzer, the save code is

If strFullPath <> "" Then
        objWorkBook.SaveAs FileName:= _
        strFullPath, FileFormat:=xlTemplate, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    End If

Hi Steve, I can set the display alerts to false in Excel VBA but when the Excel template is opened through Access and saved, no Excel VBA is running when the alert appears.

Thanks Sandy

 

[ByteMyzer]

You might try the following revision:

If strFullPath <> "" Then
    objWorkBook.SaveAs FileName:= _
    strFullPath, FileFormat:=[B][COLOR="Red"]xlOpenXMLTemplateMacroEnabled[/COLOR][/B], Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
End If

 

[gsandy]

That worked, sort of! The Excel is saved with the correct name and as a .xls file. But when re-opened a message says that the file type and file extension don't match. But the file does opens and the macros within Excel work.
I manually did a saveas and changed the .xls to .xlsm and that worked.

I then changed

strExt = ".xls"

to

strExt = ".xlsm"

but that would not save.

 

[ByteMyzer]

How about:

If strFullPath <> "" Then
    objWorkBook.SaveAs FileName:= _
    strFullPath, FileFormat:=[B][COLOR="Red"]xlOpenXMLWorkbookMacroEnabled[/COLOR][/B], Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
End If

 

[gsandy]

Hi ByteMyzer, Thank you very much, the problem I have had for over a year has been solved. It now saves as an xlsm file, all the macros work and the file opens OK. Cheers Sandy