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

 

[Wizcow]

Thank you Canyman8019

here is the link
excel

Thank you for your extra time
Tom

 

[Candyman8019]

This looks like a super simple issue. Your SAVE button is assigned a macro called 'SaveAs'. If you reassign it to the macro called 'SaveInvoice' it works straight away.

 

[Wizcow]

hmmm?
What the heck
I did as you said and I am still getting the same Run-time error 1004.

My future intention is to call SaveInvoice from the SaveAs macro

 

[Candyman8019]

I took a look at your SaveAs function...there is a 'End With' command near the end that doesn't belong. I commented that out, then uncommented your call to the SaveInvoice routine...and it works perfectly.

Try this: Invoice template v4.xlsm

 

[Wizcow]

Good catch!!

I have something going on here then, as that didn't change anything on my end.
I am using a Mac, I wonder if that has a bearing on it?
Thank you so much for the help!!!
Tom

 

[Skyybot]

I think you have to define your Range more specifically. i.e.

Dim sht as WorkSheet
Set sht = ActiveSheet ' or whatever your sheet name is
fName = sht.Range("g9") & sht.Range("h9")

 

[Wizcow]

Hi Skybot
Thankyou for your reply
I got rid of the range and just put a static name in and it still hangs on the saveName line.
Tom

Sub SaveInvoice()

Dim fname As String
fname = "test"

        'MsgBox (fname)
       
            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

 

[Candyman8019]

Ah, that's likely it. I don't know anything about the Mac so I can't speak as to what would require changes. I did a quick search and one solution indicated to use a ChDir command to set the path where you want to save the file. Maybe give that a try. Here's a link with some promising info: Mac: Excel 365 macro's SaveAs CSV gives "Run-time error '1004'" "Cannot access read-only document"

 

[Wizcow]

Awesome
! Thank you, I will give that a read

 

[Skyybot]

Try this:
Sub SaveInvoice()

Dim fname As String
fname = "test"

        'MsgBox (fname)
       
            saveName = ThisWorkbook.Name
      
      If saveName <> False Then
            ActiveWorkbook.SaveAs FileName:=saveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
      End If
      
End Sub