-1

I am trying to make a button that would show a save as dialog box and upon saving would save the current workbook as separate Excel Workbook that I enter a filename and location for.

What I have so far code wise is:

Private Sub Save_Button_Click()

Application.GetSaveAsFilename InitialFileName:="S0000.xlsx"

End Sub

Could someone, please, share a code for this operation?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Eduards
  • 68
  • 2
  • 20
  • Do you want to save the workbook as `xlsx` or `xlsm`? – Siddharth Rout Apr 13 '21 at 08:17
  • [Application.GetSaveAsFilename](https://learn.microsoft.com/en-us/office/vba/api/excel.application.getsaveasfilename?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(vbaxl10.chm133143);k(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue) – Siddharth Rout Apr 13 '21 at 08:25
  • [More Links](https://www.google.co.in/search?q=Application.GetSaveAsFilename+site:stackoverflow.com&sa=X&ved=2ahUKEwib5P-K5vrvAhWPbSsKHQxQB1MQrQIoBHoECBcQBQ&biw=1920&bih=937) – Siddharth Rout Apr 13 '21 at 08:28

1 Answers1

2

Try this:

Private Sub Save_Button_Click()
    dim fName as variant

    fName = Application.GetSaveAsFilename( _
        InitialFileName:="S0000.xlsx", _
        FileFilter:="Excel Workbook (*.xlsx), *.xlsx")
    ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlWorkbookDefault

End Sub

This is adapted from the example at Duplicate your workbook in seconds.

It saves the active workbook (i.e. the one currently open in the foreground of Excel). This may or may not be the workbook containing the code.

If you want to instead make sure the workbook containing this code is the one saved, change ActiveWorkbook to ThisWorkbook.

To trap for a filename not being provided by the user (after they delete your default one), you could add a conditional statement to check, for example like this:

Private Sub Save_Button_Click()
    dim fName as variant

    fName = Application.GetSaveAsFilename( _
        InitialFileName:="S0000.xlsx", _
        FileFilter:="Excel Workbook (*.xlsx), *.xlsx")
    If fName <> False Then ActiveWorkbook.SaveAs Filename:=fName, FileFormat:= xlOpenXMLWorkbook

End Sub
ed2
  • 1,457
  • 1
  • 9
  • 26
  • Thanks! But what is that fName? That whole line appears red for me – Eduards Apr 13 '21 at 06:53
  • Do you have `Option Explicit` set? If you do, add a line above `Do` that reads `Dim fName$` (edited answer accordingly) – ed2 Apr 13 '21 at 06:57
  • It does open a dialog box and format is "All Files" which is incorrect. It doesn't even let you change the format to save the file as – Eduards Apr 13 '21 at 06:59
  • Answer updated to include xlsx file format instead of "all files" (unspecified) – ed2 Apr 13 '21 at 07:00
  • Nope, this seems incorrect, I can't even cancel saving and the whole code is red – Eduards Apr 13 '21 at 07:05
  • Thanks, this almost works but gives type mismatch after saving on line Loop Until fName <> False – Eduards Apr 13 '21 at 07:16
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/231053/discussion-between-ed2-and-eduards). – ed2 Apr 13 '21 at 07:20
  • Updated example should now work – ed2 Apr 13 '21 at 08:10
  • 6 EDITS! :D Remove the "( )" from Msgbox. Also change `Dim fName$` as `Dim fName As Variant`. Did you not test your code before posting? :) – Siddharth Rout Apr 13 '21 at 08:21
  • Why remove `()`? Curious. Removed `Dim` (so, 7?) as it is unnecessary unless the OP has `Option Explicit` in the module. Code is tested but my context and OPs may differ. – ed2 Apr 13 '21 at 08:30
  • 1
    Now it is better :) But still `MsgBox "You did not provide a filename. File not saved.", vbExclamation, "File save error"` is better than `mAns = MsgBox("You did not provide a filename. File not saved.", vbExclamation, "File save error")` – Siddharth Rout Apr 13 '21 at 08:39
  • Ok, updated accordingly regarding not trapping message box result. Sorry made previous edit before seeing suggested edit. Still, declaring the variable is 'just in case' the OP has `Option Explicit`; I would not have removed that if the question included OE or a requirement to declare. It is reinstated in any event, for posterity in application to different circumstances ahead of the simplicity of omitting the (possibly unnecessary) declarations. Thanks for steering! – ed2 Apr 13 '21 at 08:46
  • I posted it in the discussion – Siddharth Rout Apr 13 '21 at 08:50