How to specify save PATH in macro?

[Guest]

Hi, heres the code i have so far, I have tried many different methods to
specify a save path, but the dialog box keeps defaulting to "c:\my Documents".
Any help would greatly be appreciated.

*************

Sub CustomSaveAs()

Dim res As Variant, sName As String
With Worksheets("SA009_Prepsheet")
sName = .Range("G4").Text & " - " & .Range("C13").Text
End With
res = Application.GetSaveAsFilename(InitialFileName:=sName,
fileFilter:="Excel Workbook Files (*.xls), *.xls")
If res = False Then Exit Sub
ActiveWorkbook.SaveAs Filename:=res

End Sub

 

[Ron de Bruin]

First this


Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir

MyPath = "C:\Data\"
ChDrive MyPath
ChDir MyPath



Then you code




Then set it back


ChDrive SaveDriveDir
ChDir SaveDriveDir

 

[Guest]

Thanks Ron. All i really needed was ChDir "c:\savedir", but i never inserted
it near enough to the top of the script, and therfore i never worked. I
inserted as the first line & bingo. Thanks tons.