Using excel Multi-Select Dialog Box in IDEA

Good day,
 
I would like to share my experience using Excel Multi-Select Dialog Box (MSDB) in IDEA.
First – some background. Probably the best places to learn how MSDB should work are here:
http://www.wiseowl.co.uk/blog/s209/type-filedialog.htm
and here: ( plus a useful example of how to use the File Dialog object)
https://msdn.microsoft.com/en-us/library/office/aa219843%28v=office.11%29.aspx
 
I used Windows 7 SP1 Excel 2013 MSDB object in IDEA version 9 script. As you could guess – it is not a straightforward exercise. (Otherwise, there was no need for this post, right?)
 
Using the info in the above mentioned links and a key insight from Brian Element I managed to create the following IDEA script code:
 
‘Constant value for excel File Open Dialog Box
Global Const msoFileDialogOpen As Long = 1
‘Constant value for excel File Browse Dialog box
Global Const msoFileDialogFilePicker as Long = 3
 
Dim objShell, excelApp, excelFD as Object
Dim buttonChosen, lastElement as Integer
Dim sWBTitle as String
 
The reason for the next two lines is to provide a work-around to an intermittent Bring To Front problem when showing the excel Dialog Box. This work-around will show all windows, including those opened beneath:
 
Set objShell = CreateObject("shell.application")
objShell.CascadeWindows
 
Since there is no IDEA or Shell easy method for MSDB, we will use excel File Browse or File Open Dialog Boxes. Thanks to Brian Element for providing this insight. Please note that for this to work, Microsoft excel must be installed . This is not really a big limitation as most if not all systems with IDEA installations also have Excel.
If msoFileDialogFilePicker is used - the File Browse Dialog Box is presented
If msoFileDialogOpen is used - the File Open Dialog Box is presented
The difference is that File Open Dialog Box allows you to filter the required File Extensions. Unfortunately, however, one cannot set these filters in the IDEA script code (See below).
 
Set excelApp = CreateObject("Excel.Application")
Set excelFD = excelApp.FileDialog(msoFileDialogOpen)
 
 The .Title field can be displayed but can’t be set.. Hmmm, Not very useful, Eh?
 
sWBTitle = excelFD.Title
 
Not supported: 1)          To set the caption of the dialog box,
Not supported:               set the Title property
 
‘ excelFD.Title = "Select File"
 
Not supported: 2)          Set the oddly named InitialFileName property to
Not supported:               determine the initial folder selected
 
' excelFD.InitialFileName = "C:\Users\" & WINUsername & "\Documents\"
 
Not supported: 3)         Set the InitialView property to control how your files
Not supported:               appear on screen (as a list, icons, etc.)
 
' excelFD.InitialView = msoFileDialogViewDetails
 
 
Not supported: 4)          To set the filters (you can have as many as you like)
Not supported:               first clear any existing ones, then add them one by one
 
' excelFD.Filters.Clear
' excelFD.Filters.Add "All Files", "*.*"
' excelFD.Filters.Add "Text", "*.txt"
 
Not supported:               if there's more than one filter, you can control which
Not supported:               one is selected by default
 
' excelFD.FilterIndex = 2
 
Not supported: 5)          Make the Dialog Box visible
 
' excelFD.visible = True
 
Not supported:  6)         Set the ButtonName property to control the text on
Not supported:               the OK button (the ampersand means the following
Not supported:               letter is underlined and choosable with the ALT key)
 
' excelFD.ButtonName = "Open"
 
The .Show field is supported – returned values are 0 if Cancel and -1 if at least one file was selected.
 
buttonChosen = excelFD.Show  
If buttonChosen = 0 Then
          'didn't choose anything (clicked on CANCEL)
            GoTo CancelButtonHandler
End If
 
Now comes an important kludge.. you cannot use the Microsoft suggested loop - For Each vrtSelectedItem In .SelectedItems . This is because the  excelFD.SelectedItems.count field trampled on sometimes. As a result you cannot rely on it to keep the count value till the end of the For loop
 
lastElement =  excelFD.SelectedItems.count
 
For i = 1 To  lastElement
 
            ‘ Do your stuff here, you can refer to each selected item as
            ‘ excelFD.SelectedItems(i) (type Variant)
 
Next i
 
Set excelFD = Nothing
Set excelApp = Nothing
Set objShell = Nothing
 
Last comment: I will be more than happy if someone in the community will either find mistakes in my implementation and will be able to use more fields, which I claimed are not supported, or will find a different method for MSDB which is fully supported by IDEA.
 
Thank you.