VBA for File save-as XLSM with user folder selection

DutchKevin

Board Regular
Joined
Apr 13, 2011
Messages
126
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..??:confused:
Any tips, general approach, a page that explains best practice?

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,429
Office Version
365
Platform
Windows
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

Board Regular
Joined
Apr 13, 2011
Messages
126
There was a weekend in between so hence the delay in response.
This is the code I currently have.

Code:
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
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,429
Office Version
365
Platform
Windows
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

Board Regular
Joined
Apr 13, 2011
Messages
126
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.
Code:
[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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,429
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,081,677
Messages
5,360,451
Members
400,586
Latest member
Minty

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top