fill in File Name and SaveAs .xlsm

[Wizcow]

Hello
My button is to open the SaveAs dialog box so the user can pick the path for the worksheet.
II would like the file name and file type pre-filled in on the SaveAs dialog box

here is my 'not working' code

Sub Button72_Click()

Dim fname As String
fname = Range("g9") & Range("h9")

       Application.GetSaveAsFilename , (fname)
      ThisWorkbook.SaveAs FileName:=fname, FileFormat:=52
   
End Sub

This code launches the Save dialog box but doesn't fill in the 'Save As' or 'File Format' boxes
If I click the 'Save' button on the dialog box it saves a file with the correct name but no .xlsm extension making the file unusable unless you manually add the extension.

Anyone see the issue?
Thanx
Tom

 

[I_know_nuffin]

try this

Sub saveasname()

Dim fname As String
fname = Range("g9") & Range("h9")
       
       saveName = Application.GetSaveAsFilename(InitialFileName:=fname, _
                                         fileFilter:="Microsoft Excel Worksheet (*.xlsm), *.xlsm")
      If saveName <> False Then
        ActiveWorkbook.SaveAs fileName:=saveName
      End If
      

End Sub

 

[Wizcow]

Thank you for your reply I_know_nuffin!
I tried your example but I get an error

Run-time error '1004':
Method 'GetSaveAsFilename' of object '_Application' failed

Any ideas what could cause this?
Thanks again!!
Tom

 

[Candyman8019]

try changing line 3 to this:

fname = Range("g9").value & Range("h9").value

 

[Wizcow]

Thank you Candyman8019

Dang!
Same error still

Thank you
Tom

 

[Candyman8019]

Are there any values in G9 or H9 that would be considered invalid characters for a filename?
Try adding:
msgbox fname

to make sure you're result is a valid path and filename.

 

[Wizcow]

Candyman8019
That part of the code seems to work fine.
I tested with a message box and it brings back the value of G9 and H9 perfectly
The debugger hangs on the SaveName part

MsgBox (fname)

 

[Candyman8019]

How about this:

Sub saveasname()

Dim fname As String
fname = Range("g9").value & Range("h9").value
       
       saveName = Application.GetSaveAsFilename(InitialFileName:=fname, _
                                         fileFilter:="Microsoft Excel Worksheet (*.xlsm), *.xlsm")
      If saveName <> False Then
        ActiveWorkbook.SaveAs fileName:=saveName, fileformat:=xlOpenXMLWorkbookMacroEnabled
      End If
      

End Sub

 

[Wizcow]

Dang it!
The same error on the saveName line
I sure appreciate your effort
Tom

 

[Candyman8019]

something odd seems to be going on. Any chance you could share a copy of your workbook using dropbox or onedrive so I can take a closer look? feel free to sanitize any data as required.