I have the following function:

ActiveWorkbook.SaveAs Filename:=Range("filename").Value

Range("filename") contains:
=CONCATENATE(path,"\",Name2," ","2007",".","xls")

Problem:
I want to be able for the person to BROWSE and select the PATH to put into
the CELL = Range("path") so the person doesn't have to type it in.

thanks
Helmut

You could use application.getsaveasfilename. The file doesn't have to
exist.

Sub test()
Dim myFolderName As Variant
Dim fpath As String
myFolderName = Application.GetSaveAsFilename _
(InitialFileName:="FileNameWillBeIgnored")

If myFolderName = False Then
Exit Sub 'user hit cancel
Else
fpath = Mid(myFolderName, 1, InStrRev(myFolderName, "\"))
MsgBox fpath
End If
End Sub

Or you could use one of these routines...

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm


===
ps. If you and all your users are running xl2002+, take a look at VBA's
help:
application.filedialog(msoFileDialogFolderPicker)

--
Cheers
Nigel



"Helmut" wrote in message
...
I have the following function:

ActiveWorkbook.SaveAs Filename:=Range("filename").Value

Range("filename") contains:
=CONCATENATE(path,"\",Name2," ","2007",".","xls")

Problem:
I want to be able for the person to BROWSE and select the PATH to put into
the CELL = Range("path") so the person doesn't have to type it in.

thanks
Helmut

Hi Nigel,
It gives me the message box path, but doesn't save the file.

Can I replace the "FileNameWillBeIgnored" with Range("Fname").value and
after the user selects the appropriate directory where he/she wants to save
the file, it will save it there with the FILENAME from the Range?
How?


"Nigel" wrote:

You could use application.getsaveasfilename. The file doesn't have to
exist.

Sub test()
Dim myFolderName As Variant
Dim fpath As String
myFolderName = Application.GetSaveAsFilename _
(InitialFileName:="FileNameWillBeIgnored")

If myFolderName = False Then
Exit Sub 'user hit cancel
Else
fpath = Mid(myFolderName, 1, InStrRev(myFolderName, "\"))
MsgBox fpath
End If
End Sub

Or you could use one of these routines...

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm


===
ps. If you and all your users are running xl2002+, take a look at VBA's
help:
application.filedialog(msoFileDialogFolderPicker)

--
Cheers
Nigel



"Helmut" wrote in message
...
I have the following function:

ActiveWorkbook.SaveAs Filename:=Range("filename").Value

Range("filename") contains:
=CONCATENATE(path,"\",Name2," ","2007",".","xls")

Problem:
I want to be able for the person to BROWSE and select the PATH to put into
the CELL = Range("path") so the person doesn't have to type it in.

thanks
Helmut

With Application.FileDialog(msoFileDialogFolderPicker)
.Show


Range("path").Value = .SelectedItems(1)

End With


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Helmut" wrote in message
...
I have the following function:

ActiveWorkbook.SaveAs Filename:=Range("filename").Value

Range("filename") contains:
=CONCATENATE(path,"\",Name2," ","2007",".","xls")

Problem:
I want to be able for the person to BROWSE and select the PATH to put into
the CELL = Range("path") so the person doesn't have to type it in.

thanks
Helmut

Hi Bob,

It asks me to define "with"....
Do I run this before or after: ActiveWorkbook.SaveAs
Filename:=Range("filename").Value

Thanks
Helmut

"Bob Phillips" wrote:

With Application.FileDialog(msoFileDialogFolderPicker)
.Show


Range("path").Value = .SelectedItems(1)

End With


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Helmut" wrote in message
...
I have the following function:

ActiveWorkbook.SaveAs Filename:=Range("filename").Value

Range("filename") contains:
=CONCATENATE(path,"\",Name2," ","2007",".","xls")

Problem:
I want to be able for the person to BROWSE and select the PATH to put into
the CELL = Range("path") so the person doesn't have to type it in.

thanks
Helmut

You run it before, it just stores the selected folder in Range("path")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Helmut" wrote in message
...
Hi Bob,

It asks me to define "with"....
Do I run this before or after: ActiveWorkbook.SaveAs
Filename:=Range("filename").Value

Thanks
Helmut

"Bob Phillips" wrote:

With Application.FileDialog(msoFileDialogFolderPicker)
.Show


Range("path").Value = .SelectedItems(1)

End With


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Helmut" wrote in message
...
I have the following function:

ActiveWorkbook.SaveAs Filename:=Range("filename").Value

Range("filename") contains:
=CONCATENATE(path,"\",Name2," ","2007",".","xls")

Problem:
I want to be able for the person to BROWSE and select the PATH to put
into
the CELL = Range("path") so the person doesn't have to type it in.

thanks
Helmut

Bob, the following did it:

Dim SvPath As String
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = False Then Exit Sub
SvPath = .SelectedItems(1)
End With
ActiveWorkbook.SaveAs SvPath & "\" & Range("Fname")




"Bob Phillips" wrote:

You run it before, it just stores the selected folder in Range("path")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Helmut" wrote in message
...
Hi Bob,

It asks me to define "with"....
Do I run this before or after: ActiveWorkbook.SaveAs
Filename:=Range("filename").Value

Thanks
Helmut

"Bob Phillips" wrote:

With Application.FileDialog(msoFileDialogFolderPicker)
.Show


Range("path").Value = .SelectedItems(1)

End With


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Helmut" wrote in message
...
I have the following function:

ActiveWorkbook.SaveAs Filename:=Range("filename").Value

Range("filename") contains:
=CONCATENATE(path,"\",Name2," ","2007",".","xls")

Problem:
I want to be able for the person to BROWSE and select the PATH to put
into
the CELL = Range("path") so the person doesn't have to type it in.

thanks
Helmut

Glad to hear it Helmut.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Helmut" wrote in message
...
Bob, the following did it:

Dim SvPath As String
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = False Then Exit Sub
SvPath = .SelectedItems(1)
End With
ActiveWorkbook.SaveAs SvPath & "\" & Range("Fname")




"Bob Phillips" wrote:

You run it before, it just stores the selected folder in Range("path")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Helmut" wrote in message
...
Hi Bob,

It asks me to define "with"....
Do I run this before or after: ActiveWorkbook.SaveAs
Filename:=Range("filename").Value

Thanks
Helmut

"Bob Phillips" wrote:

With Application.FileDialog(msoFileDialogFolderPicker)
.Show


Range("path").Value = .SelectedItems(1)

End With


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Helmut" wrote in message
...
I have the following function:

ActiveWorkbook.SaveAs Filename:=Range("filename").Value

Range("filename") contains:
=CONCATENATE(path,"\",Name2," ","2007",".","xls")

Problem:
I want to be able for the person to BROWSE and select the PATH to
put
into
the CELL = Range("path") so the person doesn't have to type it in.

thanks
Helmut