VBA for File save-as XLSM with user folder selection

[DutchKevin]

Hi,
I have a large macro that asks the user to save the file vbYes/vbNo

File name is coming from combo of cells in the file
I want the user to select a folder on the network.
It should ask to overwrite if file exist.
It must save as XLSM.

I've been trying to get ActiveWorkbook.SaveAs going but without much success.
The filename generation with full path works, but the actual save action fails..??
Any tips, general approach, a page that explains best practice?

Thanks in advance , I'll keep struggling in meantime
Kevin

 

[Joe4]

Can you post the code that you do have to do this?
We may be able to take what you have and tweak it to get it to work.

 

[DutchKevin]

There was a weekend in between so hence the delay in response.
This is the code I currently have.

Sub MrExcelSaveAs()

        Dim answer As Integer
        Dim FullPathFile As String
        Dim newfilename, environment As String
        
        answer = MsgBox("Save the file?", vbQuestion + vbYesNo + vbDefaultButton2, "What to do....?")
        
        If answer = vbYes Then
            newfilename = "name - "
                If Sheets("sheet1").Range("M3").Value = " (Test Environment)  " Then
                   environment = "Test - "
                Else
                   environment = "Prod - "
                End If ' prod / test


            newfilename = newfilename & environment
            newfilename = newfilename & Sheets("sheet2").Range("B5").Value & " - "
            newfilename = newfilename & ".xlsm"
            Debug.Print newfilename


            FullPathFile = Application.GetSaveAsFilename(newfilename)
                If newfilename <> False Then
                    ActiveWorkbook.SaveAs FullPathFile
                End If 'sFileSaveName <> False
            Else 'Not vbYes
            MsgBox "Not saved"
        End If 'vbYes/vbNo
 End Sub

 

[Joe4]

Usually, when saving as something other than the default "xlsx", it is not enough to just have the file name, you must also specify the File Format.
See here: https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas

The good thing is, you don't need to come up with this code on your own. Just turn on your Macro Recorder, and record yourself doing an example where you save the file in the format you desire.
Then, stop the VBA Recorder and view your code, and then you can see what additional arguments and values that you need for your code.

 

[DutchKevin]

Thanks,
Few things learned. And the macro is working now
GetSaveAsFilename does not accept a dot "." in the filename, which I have as revision separator in the filename.
This had so far caused an empty filename field and thus the problems.

[COLOR=#574123]
[/COLOR]Dim answer As Integer        Dim FullPathFile As String
answer = MsgBox("Save the file?", vbQuestion + vbYesNo + vbDefaultButton2, "What to do....?")
If answer = vbYes Then
            Dim newfilename, environment As String
            newfilename = "name - "
                If Sheets("sheet1").Range("M3").Value = " (Test Environment)  " Then
                   environment = "Test - "
                Else
                   environment = "Production - "
                End If ' prod / test
            newfilename = newfilename & environment
            newfilename = newfilename & Sheets("sheet2").Range("B5").Value & " - "
            newfilename = Replace(newfilename, "/", "")
            newfilename = Replace(newfilename, ".", "_")

newfilename = Application.GetSaveAsFilename(InitialFileName:=newfilename, FileFilter:="Excel Files,*.xlsm", Title:="Save As")
ActiveWorkbook.SaveAs Filename:=newfilename, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Else ' vbNot Yes
         MsgBox "Not saved"
End If 'vbYes/vbNo

This probably can use some refinement, but at least I am able to continue.
Thanks for the tips Joe

 

[Joe4]

You are welcome!

Yes, the Macro Recorder is a great little tool to quickly & easily get little snippets of code that you may need. I use it quite often myself.