Dynamically set SaveAs folder in VBA

[XP]

I have a program in which the user is prompted to save a file at a certain
point.

What I would like to do is, when the code reaches the file save prompt
(which I can already do), have VBA quietly set MS-Excel to point to the
desired folder, so that if the user clicks [File] then [Save] the SaveAs
dialog box is pointed at the target folder, that's all.

Please note, that this should only occur if the code reaches this point and
I don't want this folder to be set as the default folder or anything like
that...

Thanks in advance for your assistance.

 

[Ron de Bruin]

Use GetSaveAsFilename

This is the start filder
MyPath = "C:\Users\Ron\test\"

Below is a basic example

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

SaveDriveDir = CurDir

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

FName = Application.GetSaveAsFilename("yourfilename", filefilter:="Excel Files (*.xls), *.xls")
If FName <> False Then
ActiveWorkbook.SaveCopyAs FName
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub

 

[XP]

Thanks Ron; I forgot about that!

Use GetSaveAsFilename

This is the start filder
MyPath = "C:\Users\Ron\test\"

Below is a basic example

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

SaveDriveDir = CurDir

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

FName = Application.GetSaveAsFilename("yourfilename", filefilter:="Excel Files (*.xls), *.xls")
If FName <> False Then
ActiveWorkbook.SaveCopyAs FName
End If

ChDrive SaveDriveDir
ChDir SaveDriveDir

End Sub





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

I have a program in which the user is prompted to save a file at a certain
point.

What I would like to do is, when the code reaches the file save prompt
(which I can already do), have VBA quietly set MS-Excel to point to the
desired folder, so that if the user clicks [File] then [Save] the SaveAs
dialog box is pointed at the target folder, that's all.

Please note, that this should only occur if the code reaches this point and
I don't want this folder to be set as the default folder or anything like
that...

Thanks in advance for your assistance.