none
FileDialog in Outlook 2013 RRS feed

  • Question

  • Hi

    I am using Outlook in Office 2013 on Windows 7 (64 bit).

    I am trying to use a file dialog box via excel which works fine when the IDE is open but when the IDE is closed the file dialog box does not open onto the screen.

    The only way I can show it is to open the Task Manager, select the dialog box window and click SwitchTo. 


    Any help would be appreciated.

    Code is as follows:

    Sub SelectAFolder()
        Dim fd As Office.FileDialog
        Dim oxl As Object
        Set oxl = CreateObject("Excel.Application")
        Set fd = oxl.Application.FileDialog(msoFileDialogFolderPicker)
        
        fd.Title = "Select folder"
        fd.AllowMultiSelect = False
        
        If fd.Show = -1 Then
                MsgBox "Selected Folder: " & fd.SelectedItems(1)
        End If
        
        oxl.Quit
        Set oxl = Nothing
    End Sub

    Monday, September 26, 2016 10:42 AM

Answers

  • Are you automating Excel from within Outlook for the sole purpose of displaying a folder browser?

    If that is the case you can use the following VBScript from within Outlook -

    Sub ShellFolderBrowse()
    Dim fld As Object
    Dim fi As Object
    Dim shl As Object
    
    Set shl = CreateObject("Shell.Application")
    
    Set fld = shl.BrowseForFolder(0, "Select Folder", 80, 5)
    If (Not fld Is Nothing) Then
        Set fi = fld.Self
        MsgBox " Selected Folder: " & fi.Path
    End If
    
    Set fi = Nothing
    Set fld = Nothing
    Set shl = Nothing
    
    End Sub
    
    

    • Edited by RLWA32 Monday, September 26, 2016 11:57 AM added script
    • Marked as answer by MikeKimpton Tuesday, September 27, 2016 5:45 PM
    Monday, September 26, 2016 11:00 AM
  • Hello Mike,

    The fact is that you didn't specify the parent window handle for the dialog window. So, it is shown behind Outlook windows. In .Net based applications you can do so by passing an instance of the IWin32Window interface. In Outlook you can cast an Explorer or Inspector windows to the IOleWindow interface to get the parent window handle. Then you can create an instance of the class which implements the IWin32Window interface and pass it to the Show or ShowDialog methods of the Form class.

    In case of VBA you can use Windows API functions to bring the form on top of other windows. See the SetForegroundWindow function which brings the thread that created the specified window into the foreground and activates the window. Keyboard input is directed to the window, and various visual cues are changed for the user. The system assigns a slightly higher priority to the thread that created the foreground window than it does to other threads. If the window was brought to the foreground, the return value is nonzero. If the window was not brought to the foreground, the return value is zero. For example:

    ' In the declaration section, declare User32 function  "SetForgroundWindow
    
    
    Public Declare Function SetForegroundWindow Lib "user32" ( _
    ByVal hWnd As Long) _
    As Long
    
    
    
    ' Later on in the code, use the following to bring the current application
    ' to the foreground
    
    ...
    
    SetForegroundWindow Application.hWnd
    
    ...


    [custom.development]

    • Marked as answer by MikeKimpton Tuesday, September 27, 2016 5:45 PM
    Monday, September 26, 2016 9:05 PM

All replies

  • Are you automating Excel from within Outlook for the sole purpose of displaying a folder browser?

    If that is the case you can use the following VBScript from within Outlook -

    Sub ShellFolderBrowse()
    Dim fld As Object
    Dim fi As Object
    Dim shl As Object
    
    Set shl = CreateObject("Shell.Application")
    
    Set fld = shl.BrowseForFolder(0, "Select Folder", 80, 5)
    If (Not fld Is Nothing) Then
        Set fi = fld.Self
        MsgBox " Selected Folder: " & fi.Path
    End If
    
    Set fi = Nothing
    Set fld = Nothing
    Set shl = Nothing
    
    End Sub
    
    

    • Edited by RLWA32 Monday, September 26, 2016 11:57 AM added script
    • Marked as answer by MikeKimpton Tuesday, September 27, 2016 5:45 PM
    Monday, September 26, 2016 11:00 AM
  • Hello Mike,

    The fact is that you didn't specify the parent window handle for the dialog window. So, it is shown behind Outlook windows. In .Net based applications you can do so by passing an instance of the IWin32Window interface. In Outlook you can cast an Explorer or Inspector windows to the IOleWindow interface to get the parent window handle. Then you can create an instance of the class which implements the IWin32Window interface and pass it to the Show or ShowDialog methods of the Form class.

    In case of VBA you can use Windows API functions to bring the form on top of other windows. See the SetForegroundWindow function which brings the thread that created the specified window into the foreground and activates the window. Keyboard input is directed to the window, and various visual cues are changed for the user. The system assigns a slightly higher priority to the thread that created the foreground window than it does to other threads. If the window was brought to the foreground, the return value is nonzero. If the window was not brought to the foreground, the return value is zero. For example:

    ' In the declaration section, declare User32 function  "SetForgroundWindow
    
    
    Public Declare Function SetForegroundWindow Lib "user32" ( _
    ByVal hWnd As Long) _
    As Long
    
    
    
    ' Later on in the code, use the following to bring the current application
    ' to the foreground
    
    ...
    
    SetForegroundWindow Application.hWnd
    
    ...


    [custom.development]

    • Marked as answer by MikeKimpton Tuesday, September 27, 2016 5:45 PM
    Monday, September 26, 2016 9:05 PM
  • Thank you guys.

    My background is on mainframes and haven't got the complete hang of this object orientated stuff yet.  

    I did try using the shell to BrowseForFolder but as it doesn't allow browsing at a higher level than the preselected root folder that causes a (not insurmountable) problem for my users' current requirements, however Eugene's SetForgroundWindow solution fits the bill perfectly.

    Once again, thank you both.

    Mike

    Tuesday, September 27, 2016 6:01 PM
  • Mike,

    I am glad to hear that the SetForgroundWindow function works for you. Good luck with your Outlook solution!


    [custom.development]

    Tuesday, September 27, 2016 6:34 PM
  • Glad it all worked out for you.

    If you consider using the shell browser in the future, take a look at ShellSpecialFolderConstants

    BTW, if you try it by selecting 0 instead of 5 as the starting folder the user can browse the entire system.  And of course you can always specify a starting path as a string value.  Enjoy!

    Tuesday, September 27, 2016 6:38 PM