browse for path in vba

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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)
 
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?
 
Hi Bob,

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

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)
 
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")
 
Glad to hear it Helmut.

--

HTH

Bob Phillips

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top