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,177
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,177
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,177
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,078,461
Messages
5,340,440
Members
399,375
Latest member
alwayssunny

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top