GetSaveAsFilename - No Help Found

Derick63

Board Regular
Joined
Apr 6, 2018
Messages
63
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:
VBA 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
 

Derick63

Board Regular
Joined
Apr 6, 2018
Messages
63
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"?
Joe4, This also worked very well and would be an easier solution than VBA but I needed to add the filename prefix. Thanks again
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,430
Office Version
  1. 365
Platform
  1. Windows
Joe4, This also worked very well and would be an easier solution than VBA but I needed to add the filename prefix. Thanks again
You are welcome.
Glad we were able to help.
 

Forum statistics

Threads
1,175,486
Messages
5,897,689
Members
434,673
Latest member
kktfc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top