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" <(E-Mail Removed)> wrote in message
news:0A5C43A6-6000-46EA-AF68-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:0A5C43A6-6000-46EA-AF68-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:0A5C43A6-6000-46EA-AF68-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
> news:0A5C43A6-6000-46EA-AF68-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:16E25482-13B4-481E-BA59-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:0A5C43A6-6000-46EA-AF68-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> news:16E25482-13B4-481E-BA59-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > > news:0A5C43A6-6000-46EA-AF68-(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
news56C4B05-336F-4664-A447-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > news:16E25482-13B4-481E-BA59-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > > news:0A5C43A6-6000-46EA-AF68-(E-Mail Removed)...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >