Is there an equivalent dialog to GetSaveAsFilename which will just select the
folder to save a file in instead of returning a full filename? The intention
is that my macro will process a list of files and for each input file will
write an output file in the same user selected folder. A standard dialog
would hopefully give the user the option to create a new folder for the
outputs if it doesn't already exist.

Grateful for advice.

excel 2003 has a Folder Select Dialog Box

--
When you lose your mind, you free your life.
Ever Notice how we use '' for comments in our posts even if they aren''t
expected to go into the code?


"simonc" wrote:

Is there an equivalent dialog to GetSaveAsFilename which will just select the
folder to save a file in instead of returning a full filename? The intention
is that my macro will process a list of files and for each input file will
write an output file in the same user selected folder. A standard dialog
would hopefully give the user the option to create a new folder for the
outputs if it doesn't already exist.

Grateful for advice.

Thanks for amazingly quick reply. Unfortunately I have Excel 2000. Is there
any equivalent for 2000?

"ben" wrote:

excel 2003 has a Folder Select Dialog Box

--
When you lose your mind, you free your life.
Ever Notice how we use '' for comments in our posts even if they aren''t
expected to go into the code?


"simonc" wrote:

Is there an equivalent dialog to GetSaveAsFilename which will just select the
folder to save a file in instead of returning a full filename? The intention
is that my macro will process a list of files and for each input file will
write an output file in the same user selected folder. A standard dialog
would hopefully give the user the option to create a new folder for the
outputs if it doesn't already exist.

Grateful for advice.

Isn't that what GetSaveAsFilename does? You can create extra folders from
that dialog.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"simonc" wrote in message
...
Is there an equivalent dialog to GetSaveAsFilename which will just select
the
folder to save a file in instead of returning a full filename? The
intention
is that my macro will process a list of files and for each input file will
write an output file in the same user selected folder. A standard dialog
would hopefully give the user the option to create a new folder for the
outputs if it doesn't already exist.

Grateful for advice.

look up in help for dialogboxes

--
When you lose your mind, you free your life.
Ever Notice how we use '' for comments in our posts even if they aren''t
expected to go into the code?


"simonc" wrote:

Is there an equivalent dialog to GetSaveAsFilename which will just select the
folder to save a file in instead of returning a full filename? The intention
is that my macro will process a list of files and for each input file will
write an output file in the same user selected folder. A standard dialog
would hopefully give the user the option to create a new folder for the
outputs if it doesn't already exist.

Grateful for advice.

Hi Simon

Try this

You can use FolderName now in the save path of the file

Sub test()
'Browse to the folder
Dim oApp As Object
Dim oFolder
Dim FolderName

Set oApp = CreateObject("Shell.Application")
Set oFolder = oApp.BrowseForFolder(0, "Select folder to Zip", 512)
If Not oFolder Is Nothing Then
FolderName = oFolder.Self.Path
If Right(FolderName, 1) < "\" Then
FolderName = FolderName & "\"
End If
MsgBox FolderName
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"simonc" wrote in message ...
Is there an equivalent dialog to GetSaveAsFilename which will just select the
folder to save a file in instead of returning a full filename? The intention
is that my macro will process a list of files and for each input file will
write an output file in the same user selected folder. A standard dialog
would hopefully give the user the option to create a new folder for the
outputs if it doesn't already exist.

Grateful for advice.

Thanks for this which works perfectly. How do you people know about all these
options???!!! I found if I changed the third option from 512 to 0 it even
gives me a New Folder option button, although I couldn't figure out from the
MSDN page on BrowseForFolder how the different options relate to the integer
value you put in here.

Many thanks

"Ron de Bruin" wrote:

Hi Simon

Try this

You can use FolderName now in the save path of the file

Sub test()
'Browse to the folder
Dim oApp As Object
Dim oFolder
Dim FolderName

Set oApp = CreateObject("Shell.Application")
Set oFolder = oApp.BrowseForFolder(0, "Select folder to Zip", 512)
If Not oFolder Is Nothing Then
FolderName = oFolder.Self.Path
If Right(FolderName, 1) < "\" Then
FolderName = FolderName & "\"
End If
MsgBox FolderName
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"simonc" wrote in message ...
Is there an equivalent dialog to GetSaveAsFilename which will just select the
folder to save a file in instead of returning a full filename? The intention
is that my macro will process a list of files and for each input file will
write an output file in the same user selected folder. A standard dialog
would hopefully give the user the option to create a new folder for the
outputs if it doesn't already exist.

Grateful for advice.

Hi Simon

I found if I changed the third option from 512 to 0 it even
gives me a New Folder option button, although I couldn't figure out from the
MSDN page on BrowseForFolder how the different options relate to the integer
value you put in here.

I never search for the numbers because I never need more then the 512 that I
believe Jim Rech posted a long time ago.

If I have time I will search for more info about this


--
Regards Ron de Bruin
http://www.rondebruin.nl


"simonc" wrote in message ...
Thanks for this which works perfectly. How do you people know about all these
options???!!! I found if I changed the third option from 512 to 0 it even
gives me a New Folder option button, although I couldn't figure out from the
MSDN page on BrowseForFolder how the different options relate to the integer
value you put in here.

Many thanks

"Ron de Bruin" wrote:

Hi Simon

Try this

You can use FolderName now in the save path of the file

Sub test()
'Browse to the folder
Dim oApp As Object
Dim oFolder
Dim FolderName

Set oApp = CreateObject("Shell.Application")
Set oFolder = oApp.BrowseForFolder(0, "Select folder to Zip", 512)
If Not oFolder Is Nothing Then
FolderName = oFolder.Self.Path
If Right(FolderName, 1) < "\" Then
FolderName = FolderName & "\"
End If
MsgBox FolderName
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"simonc" wrote in message ...
Is there an equivalent dialog to GetSaveAsFilename which will just select the
folder to save a file in instead of returning a full filename? The intention
is that my macro will process a list of files and for each input file will
write an output file in the same user selected folder. A standard dialog
would hopefully give the user the option to create a new folder for the
outputs if it doesn't already exist.

Grateful for advice.

Hi Ron,

Following is an excerpt from a post by _
Joe Earnest - public.scripting.vbscript - 04/20/2005

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'---------------------
The following are the generally useful long integer bitwise code flags for dialog operation:

&H0001 1 Only returns file system folders.
Will not select a virtual folder.

&H0002 2 Does not include network folders below the domain level.

&H0010 16 Displays an edit box for user input specification.

&H0020 32 Validates the user specification in the edit box, if implemented.
This option does not appear to work as documented on all versions of
Windows.

&H0040 64 Displays a "new style" dialog box, at least for Win2k.
This option will be ignored, if specified for WinXp,
and may be ignored on versions of Windows other than Win2k.

&H0100 256 Displays a user "hint" in a WinXp-style dialog, if the edit box
is not implemented. Available only for WinXp.
This option is ignored, if an edit box is implemented.

&H0200 512 Suppresses display of the New Folder button in a WinXp-style
dialog. Available only for WinXp.

&H04000 16384 Displays files as well as folders, and allows file selection.
The files will display properly in all versions. With Win2k, however, a
runtime error is generated if a root directory file is selected, and with
WinXp, a runtime error is generated if any file is selected.
'-----------------------------



"Ron de Bruin" wrote in message ...
Hi Simon
I found if I changed the third option from 512 to 0 it even
gives me a New Folder option button, although I couldn't figure out from the
MSDN page on BrowseForFolder how the different options relate to the integer
value you put in here.
I never search for the numbers because I never need more then the 512 that I
believe Jim Rech posted a long time ago.
If I have time I will search for more info about this
--
Regards Ron de Bruin
http://www.rondebruin.nl

Thanks for posting this Jim

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jim Cone" wrote in message ...
Hi Ron,

Following is an excerpt from a post by _
Joe Earnest - public.scripting.vbscript - 04/20/2005

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

'---------------------
The following are the generally useful long integer bitwise code flags for dialog operation:

&H0001 1 Only returns file system folders.
Will not select a virtual folder.

&H0002 2 Does not include network folders below the domain level.

&H0010 16 Displays an edit box for user input specification.

&H0020 32 Validates the user specification in the edit box, if implemented.
This option does not appear to work as documented on all versions of
Windows.

&H0040 64 Displays a "new style" dialog box, at least for Win2k.
This option will be ignored, if specified for WinXp,
and may be ignored on versions of Windows other than Win2k.

&H0100 256 Displays a user "hint" in a WinXp-style dialog, if the edit box
is not implemented. Available only for WinXp.
This option is ignored, if an edit box is implemented.

&H0200 512 Suppresses display of the New Folder button in a WinXp-style
dialog. Available only for WinXp.

&H04000 16384 Displays files as well as folders, and allows file selection.
The files will display properly in all versions. With Win2k, however, a
runtime error is generated if a root directory file is selected, and with
WinXp, a runtime error is generated if any file is selected.
'-----------------------------



"Ron de Bruin" wrote in message ...
Hi Simon
I found if I changed the third option from 512 to 0 it even
gives me a New Folder option button, although I couldn't figure out from the
MSDN page on BrowseForFolder how the different options relate to the integer
value you put in here.
I never search for the numbers because I never need more then the 512 that I
believe Jim Rech posted a long time ago.
If I have time I will search for more info about this
--
Regards Ron de Bruin
http://www.rondebruin.nl