GetSaveAsFilename - No Help Found

[Derick63]

Hello to all you Guru's out there.
I know this isn't a new topic and I've done my due diligence looking for an answer but I'm getting an error I haven't seen before in other posts/questions.
I made a Tender Entry-Rev7.11.xlsm file so when the user opens the file it goes directly to the Save as dialog box to save as an Tender Entry-.xlsx file. I inserted the code in the "ThisWorkbook" section using the Workbook_Open event. I did this so the user cannot enter data in the original file. Yes, I know they could put the file into a template folder but the users lack of computer expertise is rampant.
Here is my code:

Option Explicit

Private Sub Workbook_Open()
    
Dim strDefaultPath As String
Dim strDefaultName As String
Dim strDialogBoxName As String
Dim strFilePath As String

strDefaultPath = ThisWorkbook.Path
strDefaultName = "Tender Entry-"
strDialogBoxName = "Tender Entry File Name & Location"

strFilePath = Application.GetSaveAsFilename(InitialFileName:=strDefaultName, fileFilter:="Excel Workbook (*.xlsx), *.xlsx", FilterIndex:=51, Title:=strDialogBoxName)
    
    If strFilePath = "False" Then
        Exit Sub
    Else
        ActiveWorkbook.SaveAs Filename:=strFilePath     'This is where the error is highlighted when I select Debug on the error message
    End If

    End Sub

And this is the error I'm getting:
Run-time error '1004':
This extension cannot be used with the selected file type. Change the file extension in the File name text box or select a different file type by changing the Save as type.

I did everything manually at first to see exactly what is happening at every step taking note on what pop-ups are opening, shown messages and what choices I have to make. I think I'm missing a step somewhere but because of my lack of VBA experience/knowledge, here I am wanting to learn more.
I appreciate any and all help.
Thanks,
Derick

 

[Joe4]

You cannot save a file with VBA code to the "XLSX" format - it must be saved to an "XLSM" or "XLSB" format.

But if your concern is them putting data in the original file, have you considered making the file Read-Only, so that they cannot save any changes to that file directly, and are forced to do a "SaveAs"?

 

[Derick63]

You cannot save a file with VBA code to the "XLSX" format - it must be saved to an "XLSM" or "XLSB" format.

But if your concern is them putting data in the original file, have you considered making the file Read-Only, so that they cannot save any changes to that file directly, and are forced to do a "SaveAs"?

Thanks Joe.
When I manually exported or Save as, I was given the option to continue saving as a macro-free workbook. If I selected "Yes" then it would save as an "XLSX". This is the step I'm missing in my code and I have no idea how to get that code.
I recorded the process and this is what was recorded:

    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\Derick\Documents\Heather\Medical Supply\Tenders\Tender Entry Spreadsheets\Tender Entry-.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

I need the user to select their own destination so I used strDefaultPath = ThisWorkbook.Path.
I supplied the prefix for the new file name with strDefaultName
Here is a snippet of the pop-up:

 

[Joe4]

You are still trying to save as xlsx - which you cannot do since you have VBA code in it!

Have a look at these file type options: XlFileFormat enumeration (Excel)

 

[Derick63]

You are still trying to save as xlsx - which you cannot do since you have VBA code in it!

Have a look at these file type options: XlFileFormat enumeration (Excel)

I totally understand your reference and point. The manual test I did, as above, using Export or Save as (from a XLSM), opened the newly created XLSX file and I saw the module I created. The next step was to add data and Save the file, close it then reopen said XLSX file, No VBA/Code/Modules.
So, as you're the MVP here you'd know more than I would and I'm not one to argue with anyone who knows more than I do, but in reality, what I'm physically seeing is exactly not what you're telling me, I can save and XLSM file to an XLSX without code because once saved and closed then reopened you'll see the code is not there. You can try it yourself.

 

[Joe4]

When you saved it "manually", was there any VBA code in your file at that time?
If there is, you should get that error message pop-up like you show at the bottom of post 3 (at least I do).
If your file is an "xlsm", but there is not VBA code in it, you can save it as an "xlsx".

However, once you introduce VBA code (your "Workbook_Open" event), you shouldn't be able to save as an "XLSX", either manually or with VBA, until you physically remove that VBA code.

By the way, have you given any thought to the suggestion I gave at the bottom of my first reply?

But if your concern is them putting data in the original file, have you considered making the file Read-Only, so that they cannot save any changes to that file directly, and are forced to do a "SaveAs"?

 

[RoryA]

You need to specify the fileformat argument when saving:

ActiveWorkbook.SaveAs Filename:=strFilePath, fileformat:=xlOpenXMLWorkbook

 

[Joe4]

You need to specify the fileformat argument when saving:

ActiveWorkbook.SaveAs Filename:=strFilePath, fileformat:=xlOpenXMLWorkbook

Ah yes, I was focusing on the second reply, in which the recorded code shows it, but it is missing from the original.

 

[Derick63]

You need to specify the fileformat argument when saving:

ActiveWorkbook.SaveAs Filename:=strFilePath, fileformat:=xlOpenXMLWorkbook

RoryA, That worked! That's the step(pop-up) I was missing in the code. Thank you

 

[Derick63]

Ah yes, I was focusing on the second reply, in which the recorded code shows it, but it is missing from the original.

Joe4, Thank you for enlightening me. I learned something today from your reply. Thanks!