Using SaveAs Dialog and Changing Location

[Orion Cochrane]

I would like to call up the Save As dialog box, but get it to save in a
specific folder, as opposed to the current workbook's path. Eg. Instead of
the workbook's path of "C:\Test\Test.xls", I would like it to look in
"C:\Test2" and let you choose a filename.

TIA.

 

[Ron de Bruin]

Hi Orion

Change MyPath

Sub test()
Dim FName As Variant
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = "C:\Users\Ron\Test2\"
ChDrive MyPath
ChDir MyPath

FName = Application.GetSaveAsFilename("", _
fileFilter:="Excel Files (*.xls), *.xls")
If FName <> False Then
' do your Save stuff
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub

 

[OssieMac]

I wonder if Ron's answer is what the OP really wanted or if the Save as
dialog box was required. The following actually displays the Save As dialog
box with the required folder location.

Sub SaveWorkbook()

Dim myTitle

'myTitle is the title displayed on the dialog box.
myTitle = "Select or enter the file name for save"

With Application.FileDialog(msoFileDialogSaveAs)
.Title = myTitle

'Note the backslash on the end or Excel _
interprets last text as a file name.
.InitialFileName = "C:\Users\OssieMac\Documents\Excel\"

.Show
End With