Dynamically set SaveAs folder in VBA
 

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.

Dynamically set SaveAs folder in VBA
 

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


"XP" wrote in message ...
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.

Dynamically set SaveAs folder in VBA
 

Thanks Ron; I forgot about that!



"Ron de Bruin" wrote:

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


"XP" wrote in message ...
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.