Results 1 to 14 of 14
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Open multiple files with GetOpenFileName (Excel2000>)

    Hi,
    The line: myFile=Application.GetOpenFileName("Excel Files,*.xls"), open 1 file that the user selects in the dialog! Is there a way to allow this code to open multiple files. I know the variable can only store one value, but could I do something like:
    Array(myFile1,myFile2,myFile3)...and then have all 3 files open?
    Regards,
    Rudi

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

    Re: Open multiple files with GetOpenFileName (Excel2000>)

    Like this:

    Sub OpenMulti()
    Dim varResult As Variant
    Dim i As Integer
    varResult = Application.GetOpenFilename _
    (FileFilter:="Excel Files, *.xls", MultiSelect:=True)
    If VarType(varResult) = vbBoolean Then
    Exit Sub
    End If
    For i = LBound(varResult) To UBound(varResult)
    ' Code to process varResult(i) goes here
    Debug.Print varResult(i)
    Next i
    End Sub

    BTW Using VarType is a way of checking what kind of data a Variant contains.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open multiple files with GetOpenFileName (Excel2000>)

    Thanx a stack.
    I added a line to open the files...thats all I needed.

    Sub OpenMulti()
    Dim varResult As Variant
    Dim i As Integer
    varResult = Application.GetOpenFilename _
    (FileFilter:="Excel Files, *.xls", MultiSelect:=True)
    If VarType(varResult) = vbBoolean Then
    Exit Sub
    End If
    For i = LBound(varResult) To UBound(varResult)
    Workbooks.Open Filename:=varResult(i)
    Debug.Print varResult(i)
    Next i
    End Sub

    PS: What is the debug.Print line for???
    Regards,
    Rudi

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

    Re: Open multiple files with GetOpenFileName (Excel2000>)

    Debug.Print can be used to display results in the Immediate window (type Ctrl+G in the Visual Basic Editor to activate this window). It was only meant as an example here, you can omit the line in your finished code.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open multiple files with GetOpenFileName (Excel2000>)

    Perfect. Thanx Hans...this is a gem bit of code.

    I see the use of UBound and LBound again. You have used it frequently in the last couple of cases in replies to me. I must make an effort to read up about these array formulas in VBA. Thanx

    PS: How do you get your code pasted with indents in normal text. Whenever I use pre tags, the code text changes. How do you paste your code into the thread???
    Regards,
    Rudi

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

    Re: Open multiple files with GetOpenFileName (Excel2000>)

    See <post#=298625>post 298625</post#>.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open multiple files with GetOpenFileName (Excel2000>)

    Dankeshun!
    Regards,
    Rudi

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

    Re: Open multiple files with GetOpenFileName (Excel2000>)

    That sounds suspiciously like German (danke sch

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open multiple files with GetOpenFileName (Excel2000>)

    Yeah...afrikaans is similar is some degree (though its closer to Dutch!) ... I just could not get round the spelling. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    Regards,
    Rudi

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open multiple files with GetOpenFileName (Exce

    ....so they don't say "dankuwel" in afrikaans?
    Gre

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open multiple files with GetOpenFileName (Exce

    Not really! It will be more in the line of "Baie Dankie" or just "Dankie"... or more endearing by saying "Hartlik Dankie"...
    Regards,
    Rudi

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open multiple files with GetOpenFileName (Excel2000>)

    Hans,
    Could I ask you to explain to me why you test for type boolean at this position??

    If VarType(varResult) = vbBoolean Then
    Exit Sub
    End If

    Why do this...any reason. I see it works well if I take out the Type test as well!
    Tx
    Regards,
    Rudi

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

    Re: Open multiple files with GetOpenFileName (Excel2000>)

    If the user cancels the open dialog, GetOpenFilename returns the boolean value False. Trying to run LBound on a boolean value would cause an error.

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open multiple files with GetOpenFileName (Excel2000>)

    Thankyou! I forgot about the cancel button. It make perfect sense now!
    Regards,
    Rudi

Posting Permissions

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