Thanks Dave, good tip. I tried and used John Walkenbach version which is
ideal - thanks John
--
Cheers
Nigel
"Dave Peterson" wrote in message
...
You could use application.getsaveasfilename. The file doesn't have to
exist.
Option Explicit
Sub testme()
Dim myFolderName As Variant
myFolderName = Application.GetSaveAsFilename _
(InitialFileName:="FileNameWillBeIgnored")
If myFolderName = False Then
Exit Sub 'user hit cancel
Else
'your routine to strip out filename
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)
Nigel wrote:
Hi All
On a userform I have a textbox control in which I let the user store the
destination directory for the file(s) created. I have been using
Application.GetOpenFilename to let the user go and find the directory
and
choose a file - I then strip the filename leaving the path only and put
this
in the control.
It is not elegant since the user has to choose a file to open (anyone
will
do) so I can extract the path, problem #1 is that user is now being
asked to
open a file which doesn't actually happen just so I can get the
directory -
all a bit confusing for them (and me!).
Aside from that, if the directory is empty there are no files to
select - so
problem #2 they cannot select this directory.
Anyone got a solution please?
--
Cheers
Nigel
--
Dave Peterson