There is a folder picker dialog that you could use:
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Title = "Please select folder in which to save file"
If .Show = False Then Exit Sub 'User Clicked Cancel
MySaveFolder = .SelectedItems(1)
End With
ActiveWorkbook.SaveAs Filename:= MySavePath & _
"\AP4301OKCityJIBImport" & Trim(AcctMth) &
Trim(AcctYear) & ".txt", _
FileFormat:=xlTextWindows, CreateBackup:=False

--
Charles Chickering

"A good example is twice the value of good advice."


"Fredriksson via OfficeKB.com" wrote:

My macro used to save the file to the directory that Excel was executed from.
Now my Macro only saves to the root of My Documents. No matter what
directory I am in the program thinks the CurDir in My Document

MySavePath = CurDir (This is the only reference to CurDir in the Module)

'Save Excel Workbook
ActiveWorkbook.Save

Sheets("AP4301").Select
AcctMth = Str(Month(Range("AcctgPeriod").Value))
AcctYear = Str(Year(Range("AcctgPeriod").Value))

'Save Import File in Text Format
ActiveWorkbook.SaveAs Filename:= _
MySavePath & "\AP4301OKCityJIBImport" & Trim(AcctMth) & Trim(AcctYear)
& ".txt", _
FileFormat:=xlTextWindows, CreateBackup:=False

I do not want to hard code the directory path in the file since the directory
changes. Were there update to CurDir function in the patches that Micro Soft
send out? Is there a way, to select a directory like when I use th e
FileDialog

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Add "Text files", "*.den"

This is fustrating not knowing where the file will be save

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200703/1