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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,067
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
52,067
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
52,067
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,089,317
Messages
5,407,535
Members
403,152
Latest member
Junaid Azhar

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top