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

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

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