Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    Liverpool
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filedialog equivalent in office 9 (Office 9)

    I know this is probably a stupid question, but I am trying to deploy a macro written (and working!) in Excel 10 on a Office 2000 machine. The macro simply displays a dialog to allow choice of a folder (Folder picker in FileDialog), then itereates through all the images in the file inserting them into the spreadsheet together with some text etc. Works fine on the Office XP machines, but FileDialog seems to be in the Office 10 library, and I can't find any simple way of doing the same thing.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filedialog equivalent in office 9 (Office 9)

    And to make this work in Excel:

    Option Explicit

    Private Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
    End Type

    Private Declare Function FindWindow32 Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

    Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" _
    (ByVal pidl As Long, ByVal pszPath As String) As Long

    Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" _
    (lpBrowseInfo As BROWSEINFO) As Long

    Private Const BIF_RETURNONLYFSDIRS = &H1

    Public Function BrowseFolder(szDialogTitle As String) As String
    Dim bi As BROWSEINFO, dwIList As Long
    Dim szPath As String, wPos As Integer
    Dim hwndXL As Long
    hwndXL = FindWindow32("XLMAIN", Application.Caption)
    ' For Word:
    ' hwndXL = FindWindow32("OPUSAPP", Application.Caption)
    ' Of course hwndXL looks funy in Word <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    With bi
    .hOwner = hwndXL
    .lpszTitle = szDialogTitle
    .ulFlags = BIF_RETURNONLYFSDIRS
    End With

    dwIList = SHBrowseForFolder(bi)
    szPath = Space$(512)

    If SHGetPathFromIDList(ByVal dwIList, ByVal szPath) Then
    wPos = InStr(szPath, Chr(0))
    BrowseFolder = Left$(szPath, wPos - 1)
    End If
    End Function


    Sub example()
    Dim strFolder As String
    strFolder = BrowseFolder("Select a folder")

    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Filedialog equivalent in office 9 (Office 9)

    Thanks, I always forget to check these little things. I will adapt my reply.

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

    Re: Filedialog equivalent in office 9 (Office 9)

    <P ID="edit" class=small>(Edited by HansV on 02-Jul-03 14:19. Had left some Access-specific code. Also see Pieterse's reply in this thread.)</P>You can put the following code in a standard module:

    Private Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
    End Type

    Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" _
    (ByVal pidl As Long, ByVal pszPath As String) As Long

    Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" _
    (lpBrowseInfo As BROWSEINFO) As Long

    Private Const BIF_RETURNONLYFSDIRS = &H1

    Public Function BrowseFolder(szDialogTitle As String) As String
    Dim bi As BROWSEINFO, dwIList As Long
    Dim szPath As String, wPos As Integer

    With bi
    .hOwner = 0 ' had hWndAccessApp here, which is for Access only
    .lpszTitle = szDialogTitle
    .ulFlags = BIF_RETURNONLYFSDIRS
    End With

    dwIList = SHBrowseForFolder(bi)
    szPath = Space$(512)

    If SHGetPathFromIDList(ByVal dwIList, ByVal szPath) Then
    wPos = InStr(szPath, Chr(0))
    BrowseFolder = Left$(szPath, wPos - 1)
    End If
    End Function

    You can let the user pick a folder as follows:

    Dim strFolder As String
    strFolder = BrowseFolder("Select a folder")

    If the user clicked Cancel, strFolder will be the empty string "".

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filedialog equivalent in office 9 (Office 9)

    I tend to make the same mistakes...

    Which is why it is a good thing people read other peoples' posts.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filedialog equivalent in office 9 (Office 9)

    Of course setting the .Owner to zero does not tie the dialog to Excel, so one can switch applications, come back, OK the dialog and not be returned to Excel (Word).

    I prefer my solution in getting the hwnd for Excel (Word) and using that.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Filedialog equivalent in office 9 (Office 9)

    Agreed. That's why I referred to your post in the header.

  8. #8
    New Lounger
    Join Date
    May 2002
    Location
    Liverpool
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filedialog equivalent in office 9 (Office 9)

    Thanks both of you - leads to 4 conclusions 1) I see why they put FileDialog into 10, 2) I don't see why it took them until Office 10!, 3) I feel MUCH better about not being able to figure out how to do it, and 4) there are some really generous people in the lounge with a lot of knowledge they willing to share.
    I have a feeling that reading this code will advance my knowledge of VBA significantly (and believe me it needs that!)
    Thanks again.

  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filedialog equivalent in office 9 (Office 9)

    Better still, use GetActiveWindow instead of FindWindow. Works with any calling app.

    Private Declare Function GetActiveWindow Lib "user32" () As Long

    hWnd = GetActiveWindow()
    With bi
    .hOwner = hWnd
    .lpszTitle = szDialogTitle
    .ulFlags = BIF_RETURNONLYFSDIRS
    End With
    etc..
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  10. #10
    Star Lounger
    Join Date
    Jan 2001
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filedialog equivalent in office 9 (Office 9)

    In stead of all those API functions you can use the following mini code:

    Sub Test()
    Debug.Print GetFolder(Title:="Gimme a Folder", RootFolder:=&H11)
    End Sub

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

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filedialog equivalent in office 9 (Office 9)

    That is a good one!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    New Lounger
    Join Date
    Jul 2002
    Location
    Auckland, New Zealand
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filedialog equivalent in office 9 (Office 9)

    The above Requires
    shell32.dll version 4.71 or later
    Minimum operating systems: Windows

  13. #13
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filedialog equivalent in office 9 (Office 9)

    How about a dialog box to select a file instead of just the folder?

    ..dane

  14. #14
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Filedialog equivalent in office 9 (Office 9)

    Which API are you using?

    Andrew Cronnolly's <post#=179268>post 179268</post#> shows how, if you are using BrowseForFolder, you can trick it into letting you pick files. The example is in VBScript, but the principle should apply equally well to other contexts.

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Filedialog equivalent in office 9 (Office 9)

    <P ID="edit" class=small>(Edited by JohnBF on 01-Jul-04 11:52. Outlook doesn't support the Method. Available only in Ofiice 10+.)</P>Funny you should ask. This works in Excel but won't run in Outlook, because the App doesn't support the FileDialog method.

    Sub TestBrowse4file()
    MsgBox Browse4File
    End Sub

    Private Function Browse4File() As String
    Dim dlgOpen As FileDialog
    Dim varFile As Variant
    Dim strReturnedPath As String

    Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
    With dlgOpen
    .AllowMultiSelect = False
    .Show
    For Each varFile In .SelectedItems
    strReturnedPath = varFile
    Next varFile
    End With
    Browse4File = strReturnedPath & CStr(varFile)

    End Function

    I'm sure it needs more work.
    -John ... I float in liquid gardens
    UTC -7ąDS

Page 1 of 2 12 LastLast

Posting Permissions

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