Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Select Folder (Word 2002/VBA)

    I would like to present the user with a dialog from which to choose the folder which files are to be copied to. This folder will always already exist. What is the best method to use for presenting this choice to the user? The folder will always be a sub-folder of a known parent folder.

    Thanks,
    Richard Barrett

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Folder (Word 2002/VBA)

    Try this function (originally posted by Don Ceraso):

    Public Function BrowseFolder(Optional Title As String = "Select a Folder", Optional RootFolder As Variant) As String
    On Error Resume Next
    BrowseFolder = CreateObject("Shell.Application").BrowseForFolder( 0, Title, 0, RootFolder).Items.Item.Path
    End Function

    Use like this:

    Dim strPath As String
    strPath = BrowseFolder("Destination Folder", "C:Parent")
    If strPath = "" Then Exit Sub
    ...

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Location
    New York, NY
    Posts
    922
    Thanks
    2
    Thanked 12 Times in 11 Posts

    Re: Select Folder (Word 2002/VBA)

    Thanks very much. Richard

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Folder (Word 2002/VBA)

    Is there a means of making this shell modal? I've read through MSKB BROWSEINFO Structure and am no wiser.

    I have a userform with this code<pre>Private Sub UserForm_Initialize()
    Me.cmdOK.Tag = ""
    End Sub
    Private Sub cmdBrowse_Click()
    Me.cmdOK.Tag = BrowseFolder("Destination Folder", "C:")
    End Sub
    Private Sub cmdOK_Click()
    Me.hide
    End Sub
    </pre>

    and a module with this code<pre>Public Function BrowseFolder(Optional Title As String = "Select a Folder", Optional RootFolder As Variant) As String
    On Error Resume Next
    BrowseFolder = CreateObject("Shell.Application").BrowseForFolder( 0, Title, 0, RootFolder).Items.Item.Path
    End Function
    Sub TESTBrowseFolder()
    UserForm1.Show vbModal
    MsgBox UserForm1.cmdOK.Tag
    End Sub</pre>

    When I run my TEST macro, my userform pops up. I choose Browse and the Shell.Application pops up. I can now Alt-Tab to my userform, click cancel, and leave the Shell.Application floating in space. I thought I ought to be able to make the user form inactive until I'd returned from the Shell.Application.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Folder (Word 2002/VBA)

    I don't know how to make the BrowseForFolder dialog modal, sorry.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Folder (Word 2002/VBA)

    Thanks.

    I suppose that This MSKB is overkill for my situation?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Folder (Word 2002/VBA)

    If you want to do something like that, I'm sure that <!profile=Jezza>Jezza<!/profile> can whip up an invention for you, utilizing lollipop sticks, rubber bands and mirrors. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Folder (Word 2002/VBA)

    <pre> Public SH As Shell32.Shell
    Public Function strBrowseForFolder(Optional Caption As String, _
    Optional InitialFolder As String, Optional Flags As Long) As String
    Dim strResult As String
    Dim F As Shell32.FoldEr
    Set SH = New Shell32.Shell
    Set F = SH.BrowseForFolder(0&, Caption, Flags, _
    InitialFolder)
    (snip!)</pre>


    I started with a Public Boolean, then figured I could use the SH, so made it Public and dragged it outside the Function, as shown above.

    I then replaced every instance of Me.Hide (for starters!) with a call to a function HideMe<pre>Private Function HideMe()
    If SH Is Nothing Then
    Me.hide
    Else
    MsgBox "FINISH YOUR BROWSING, SILLY COW"
    Set SH = Nothing
    End If
    End Function
    </pre>

    and feel that I should be able to do something with the SH, at least get rid of it and treat this as a case of "user clicked Cancel on the SHell browse folder GUI" and continue in my own UserForm.

    The SH seems to be deficient in methods. I can count how many windows there are (1!), but can't seem to get rid of the dialogue box.

    I brought this SH into the world; I'd expect to be able to take it out again, not matter how ugly. It's gotten a bit beyond jezza's rubbers, I'm afraid!

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Folder (Word 2002/VBA)

    Here is how to make BrowseForFolder modal: you have to pass the window handle of the userform as first argument.

    Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

    ' Modified definition, with an extra argument!
    Public Function BrowseFolder(Optional Title As String = "Select a Folder", _
    Optional RootFolder As Variant, Optional hWnd As Long) As String
    On Error Resume Next
    BrowseFolder = CreateObject("Shell.Application").BrowseForFolder _
    (hWnd, Title, 0, RootFolder).Items.Item.Path
    End Function

    and in cmdBrowse_Click:

    Dim hWnd As Long
    hWnd = FindWindow("ThunderDFrame", Me.Caption)
    Me.cmdOK.Tag = BrowseFolder("Destination Folder", "C:", hWnd)

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Folder (Word 2002/VBA)

    Thanks, Hans. That does, as we say in the trade, the trick.

    I'm curious: How did you know that offering the handle would make it modal?

    I see this in the MSKB:
    Hwnd Required. The handle to the parent window of the dialog box. This value can be zero.

    But I see nothing that tells me that a non-zero value will make the form modal.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Folder (Word 2002/VBA)

    I searched the newsgroups for browsefolder modal and found some oblique references to passing the window handle of the "parent" to make the dialog modal, so I tried it out and it turned out to work!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •