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

    GetOpenFilename (multiple folders) (Excel 97 et al

    The user has workbooks saved with extents of "SAW and "RAW", and wants to use a multi-select File Open dialog, selecting a few RAW files from this folder and a few RAW files from that folder, a couple of SAW files from the other folder, and then go to lunch while my application gets to work.
    I can't find, anywhere, a simple way of getting a standard dialogue to do this.
    The code below will demonstrate multi-select from a folder, but as soon as we browse to another folder, the selected files are "lost" from my collection.
    As far as I can see I can't harvest intermediate results until control is returned to me, and by that time the intermediate results are gone, replaced by the user's selection in the latest selected folder.
    That is, I'll have to resort to skull-duggery, such as multiple calls to the GetOpenFilename dialogue to get the job done.
    Unless someone has a better idea.
    <pre>Sub test()
    Dim fileToOpen
    fileToOpen = Application.GetOpenFilename("Corporate Files (*.RAW), *.RAW,Summary Files (*.SAW), *.SAW", , , , True)
    On Error GoTo Failed
    If fileToOpen = False Then
    MsgBox "user cancelled"
    Else
    MsgBox "Open " & fileToOpen
    End If
    Exit Sub
    Failed:
    Select Case Err.Number
    Case 13
    Dim strFiles As String
    Dim lng As Long
    For lng = LBound(fileToOpen) To UBound(fileToOpen)
    strFiles = strFiles & vbCrLf & fileToOpen(lng)
    Next lng
    MsgBox strFiles
    Case Else
    MsgBox Str(Err.Number) & Err.Description
    End Select
    End Sub</pre>


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

    Re: GetOpenFilename (multiple folders) (Excel 97 et al

    The File | Open dialog doesn't allow you to select files from multiple folders, so I think you'll have to show the dialog in a loop until the user cancels.

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

    Re: GetOpenFilename (multiple folders) (Excel 97 e

    Thanks for the confirmation, Hans.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: GetOpenFilename (multiple folders) (Excel 97 et al

    Chris

    This might be of interest. Even though it is .NET, it seems the method is readily adaptable to VB/ VBA.
    VB Helper: HowTo: Let the user pick multiple files in VB .NET

    Alan

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

    Re: GetOpenFilename (multiple folders) (Excel 97 et al

    I guess one alternative would be to use the filesearch object to get alist of all .?AW files, then use a userform with a treeview that shows them so the user can pick which ones to open.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  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: GetOpenFilename (multiple folders) (Excel 97 e

    Thanks Alan, I read through the d/l samples. The portion reproduced below indicates that the technique is that of making repeated calls to a FileOpen dialogue (which I can do in VBA).
    My question, rephrased, is now easily answered: Is there a way to trap the event that a use clicks on ("selects") a file name in a File Open dialogue box. Anyone? (grin!)

    When the user clicks one of the form's browse buttons, the program gets the index of the button pressed from its Tag property (set at design time). It prepares the File Open dialog, sets its file name to the corresponding TextBox's current value, and displays the dialog. If the user clicks OK, the program saves the selected file name and the dialog's current filter number for use next time.<pre> If dlgFile.ShowDialog() = DialogResult.OK Then
    m_txtFile(index).Text = dlgFile.FileName
    m_FilterSelected(index) = dlgFile.FilterIndex
    End If
    End Sub</pre>


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

    Re: GetOpenFilename (multiple folders) (Excel 97 e

    The Windows API function GetOpenFileNameA allows you to set a hook function that can interact with all messages that are passed back and forth by the dialog. If you search Google for lpFnHook, you should find examples.

  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: GetOpenFilename (multiple folders) (Excel 97 e

    Thanks Jan Karel; I'll probably go this way as the same application has need to obtain all raw workbook templates (*.RAW) in a folder tree and present them to the user for selection as a single block of data in a listbox.
    (later) but please see my reply to Hans

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

    Re: GetOpenFilename (multiple folders) (Excel 97 e

    > If you search Google for lpFnHook

    (Several bowls of oatmeal later) ... The macro "TESTGetSaveAsName" demonstrates some hook events. In particular it traps various events such as a change of folder and a file being selected.
    The attached DOC has all the code needed to run in Word2003.
    It's ugly, but then I wanted at this point to satisfy myself that it could be done.

    Thanks to Hans for prodding me in the right direction.

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

    Re: GetOpenFilename (multiple folders) (Excel 97 e

    I'm glad it helped.

    So "chrisgreaves" is a machine that converts oatmeal to VBA code? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

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

    Re: GetOpenFilename (multiple folders) (Excel 97 e

    > So "chrisgreaves" is a machine that converts oatmeal to VBA code?

    <font color=448800>Actually it's a bit more complicated than that.
    It converts a blend of ginger fur (roughage) with oatmeal and raisins to generate occasional coating errors (and hence "I'm raisin' an error, Right Now!")</font color=448800>

    If the day quietens down I'd like to enhance the demo I u/l to make a better interface.

Posting Permissions

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