VBA code to open the SAVE AS dialogue box

[ExcelPhil]

Hi guys,

I'm after VBA code to open the SAVE AS dialogue box where the file name is extracted from cell M1 and the save folder is extracted from cell M2.

The folder address in M2 is the starting point of where the file will be saved.

That folder has many other sub folders that the user could then save the folder to or perhaps even create a new sub folder to save the file to.

I hope this makes sense.

Any help would be appreciated.

 

[HaHoBe]

Hi ExcelPhil,

you should have given information about what is to be found in the cells.

Maybe start from this:

Public Sub MrE_1226317_170080A()
' https://www.mrexcel.com/board/threads/vba-code-to-open-the-save-as-dialogue-box.1226317/

Dim varSaveFName As Variant
Dim strPath As String
Dim strFName As String

Const cstrExt As String = ".xlsm"

strPath = Range("M2").Value
If Right(strPath, 1) <> "\" Then
  strPath = strPath & "\"
End If
strFName = Range("M1").Value
If InStr(strFName, ".") = 0 Then strFName = strFName & cstrExt
varSaveFName = Application.GetSaveAsFilename( _
    InitialFileName:=strPath & strFName, _
    fileFilter:="Excel workbook with Makro ((*.xlsm), *.xlsm")
If VarType(varSaveFName) = vbString Then
  ThisWorkbook.SaveAs Filename:=varSaveFName, _
                      FileFormat:=52 'xlOpenXMLWorkbookMacroEnabled
Else
  MsgBox "User cancelled!", vbOKOnly + vbExclamation
End If
End Sub

Ciao,
Holger

 

[ExcelPhil]

Hi ExcelPhil,

you should have given information about what is to be found in the cells.

Maybe start from this:

Public Sub MrE_1226317_170080A()
' https://www.mrexcel.com/board/threads/vba-code-to-open-the-save-as-dialogue-box.1226317/

Dim varSaveFName As Variant
Dim strPath As String
Dim strFName As String

Const cstrExt As String = ".xlsm"

strPath = Range("M2").Value
If Right(strPath, 1) <> "\" Then
  strPath = strPath & "\"
End If
strFName = Range("M1").Value
If InStr(strFName, ".") = 0 Then strFName = strFName & cstrExt
varSaveFName = Application.GetSaveAsFilename( _
    InitialFileName:=strPath & strFName, _
    fileFilter:="Excel workbook with Makro ((*.xlsm), *.xlsm")
If VarType(varSaveFName) = vbString Then
  ThisWorkbook.SaveAs Filename:=varSaveFName, _
                      FileFormat:=52 'xlOpenXMLWorkbookMacroEnabled
Else
  MsgBox "User cancelled!", vbOKOnly + vbExclamation
End If
End Sub

Ciao,
Holger

Thanks Holger, thats works perfectly. You are a legend!