Macro to have SaveAS dialog box come up with file format already chosen

[rltidd27]

Wish to have users click on the button to make a copy of the spreadsheet in which they are working.
1. Would like to pause so the user can browse for directory and input the name
2. Would already like to have the file format chosen for them
3. After choosing the location and file name hit enter and a new copy is saved.

I have tried several things, but am missing something.
Below is what I am currently using.

'this will offer a save as to make a new cust copy
Sub Save_as()

'Application.GetSaveAsFilename
ActiveWorkbook.SaveAs FileFormat = 52

End Sub

 

[Micron]

Coming from Access mainly, I would approach this using the msoFileDialogFolderPicker (if you're trying to chose a folder) or msoFileDialogFilePicker to choose a file, or msoFileDialogSaveAs. File picker will allow you to specify the file extension to filter to, but folder picker won't show files. You also cannot filter folder picker, nor can you use multi-select. It is a 1 folder operation.

However, I don't see how picking a file to Save As for a sheet could fit your needs.

choosing the location and file name

Choosing a file name with a save as operation would overwrite an existing file?

 

[rltidd27]

Basically the workbook is like a template. Users would want to save a copy with a customer name, and choose the folder to which it is saved. I do not want them to have to know which file extension to use, would rather that come up automatically. Then they would just choose file location and name and save.
Not sure how to use what you sent to accomplish that.
Thanks
Rick

 

[Micron]

If the extension is always the same, then the code can build the filename and extension? Or don't specify the format and the format will be the same as the current version. Maybe look at
Workbook.SaveAs method (Excel) or

FileDialog object (Office)

Office VBA reference topic

learn.microsoft.com

The method you posted doesn't actually save a file - it only gets the name of the file to be saved.

Application.GetSaveAsFilename method (Excel)

Office VBA reference topic

learn.microsoft.com

 

[rltidd27]

Using this suggestion: Workbook.SaveAs method (Excel)
My macro gave this response: File name already exists in this location. Do you want to replace it?
Answering no, and I get the debug window.
All I am attempting to accomplish is opening the save as dialog, wit the format of .XLSM already chosen.
The other suggestions are a bit above my knowledge, but if you think they will accomplish the simple task I wish to achieve, I can dig in.
Thanks for the suggestions!


This is what I tried:
'this will offer a save as to make a new cust copy
Sub Save_as()

'Application.GetSaveAsFilename
ActiveWorkbook.SaveAs

End Sub

 

[Micron]

That will attempt to save the active workbook using the same name - not what you want. There are lots of code samples on how to save a sheet. Here's just one

How do I save a worksheet to a new workbook?

I am trying to save a single worksheet to a new workbook (VBA should create a new workbook). The code is running and the new workbook is popping up with the needed data and the save as dialogue wi...

stackoverflow.com

Here's code that I have that uses the dialog to pick a folder. You'd need to incorporate code that chooses the sheet and the file name (which you haven't stated where that comes from). Note: this should do steps 1 and 2. The result should be that strPath is the full path including the extension type hard coded (xls). Change that to suit. strFile line needs to be completed as well.

Sub SaveSheetAsFile
Dim fd as FileDialog
Dim strPath As String, strFile As String

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
   .InitialFileName = "C:\Users\Micron\etc\etc\" 'use if you want to set folder to start in. 
    'Could use ThisWorkbook.Path to get current folder as starting folder

   If .Show Then
      strFile = ???
      strPath = .SelectedItems(1) & "\" strFile & ".xls"
   End If
End With

End Sub

 

[rltidd27]

That will attempt to save the active workbook using the same name - not what you want. There are lots of code samples on how to save a sheet. Here's just one

How do I save a worksheet to a new workbook?

I am trying to save a single worksheet to a new workbook (VBA should create a new workbook). The code is running and the new workbook is popping up with the needed data and the save as dialogue wi...

stackoverflow.com

Here's code that I have that uses the dialog to pick a folder. You'd need to incorporate code that chooses the sheet and the file name (which you haven't stated where that comes from). Note: this should do steps 1 and 2. The result should be that strPath is the full path including the extension type hard coded (xls). Change that to suit. strFile line needs to be completed as well.

Sub SaveSheetAsFile
Dim fd as FileDialog
Dim strPath As String, strFile As String

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
   .InitialFileName = "C:\Users\Micron\etc\etc\" 'use if you want to set folder to start in.
    'Could use ThisWorkbook.Path to get current folder as starting folder

   If .Show Then
      strFile = ???
      strPath = .SelectedItems(1) & "\" strFile & ".xls"
   End If
End With

End Sub

Thanks for your help and suggestions. I still could not figure it out.
I ran across someone else and they wrote me a macro that did what I wanted.
Thanks again for the effort.

 

[Micron]

You're welcome. Glad you got a solution. If you think it might help someone else in the future, it's customary to post it. You can mark your own post as the solution if you want.

 

[rltidd27]

Here is the solution that another person provided for me and it works.

'this will offer a save as to make a new cust copy

Option Explicit
Sub saveFile()
Application.DisplayAlerts = False
Dim folder As FileDialog
Dim fItem As String
Dim xlfileName
Set folder = Application.FileDialog(msoFileDialogFolderPicker)
With folder
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then
        MsgBox "folder not selected. try again"
        Exit Sub
            Else
        fItem = .SelectedItems(1)
        xlfileName = Application.GetSaveAsFilename(InitialFileName:=fItem, FileFilter:="Excel Files (*.xlsm), *.xlsx")
        If xlfileName <> False Then
            ActiveWorkbook.SaveAs Filename:=xlfileName, FileFormat:=52
                Else
            MsgBox "Please enter file name and try again"
            Exit Sub
        End If
    End If
End With
Application.DisplayAlerts = True
End Sub

 

[Peter_SSs]

@rltidd27
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)