Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: debug help! getopenfilename on multiple files

  1. #1
    Join Date
    22nd August 2003
    Posts
    38

    debug help! getopenfilename on multiple files

    The idea of the following code is that the user chooses one or many files from the same folder. All files should be .xls files. This section of code is to get and remember the path and names of the files so that it can loop through them to transfer data to a master file later. The path and number of files could vary widely from one usage to the next. The default directory is changed because that is the most likely location of the target files.

    The code as written doesn't work. I'm stuck. I've gotten fairly good at "regular" code (thanks to you guys!) but this is my first attempt at working with arrays.

    BTW I work for a financial services company and we have very strict download restrictions. I'd appreciate it if solutions could be pasted in here rather than set up as attached files. Sorry, I know that's not always fun.

    Here's my code:
    VB:
    Dim TrialChosen As String ' used as error trap
    Dim TrialNames() As String ' array of filenames chosen
    Dim TrialSelectError As Integer ' used as error trap
     
    Private Sub GetTrialsFolder() 
         '
         ' Written by Kelly Spencer
         '
        MsgBox ("Using the next screen, select the trials you wish to import." & Chr(13) & _ 
        "Please check to be sure that the path is correct.") 
        TrialChosen = "Yes" 
        ChDir "X:\ACCOUNTING\Fund_Distributions\Data Download\" 
        Do 
            TrialNames() = Application.GetOpenFilename _ 
            (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _ 
            Title:="Please select the trials you wish to import.", _ 
            MultiSelect:=True) 
            If TrialNames(1) = "False" Then 
                TrialSelectError = MsgBox(Prompt:="You must choose at least one trial to be " _ 
                & "imported." & Chr(13) & "If you do not the macro will be cancelled." _ 
                & Chr(13) & "Do you wish to try again?", Buttons:=vbYesNo) 
                If TrialSelectError = vbNo Then 
                    TrialChosen = "No" 
                    Exit Sub 
                End If 
            End If 
        Loop Until TrialNames(1) <> "False" 
         '
    End Sub 
    
    

    Help!!
    Kelly.

    PS The "TrialChosen" variable is used by the calling procedure to determine whether it should continue or quit (ie if files were or were not chosen).
    Last edited by royUK; August 4th, 2005 at 03:50.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Perth, Australia
    Posts
    650
    I just did quick test and the GetOpenFilename works ok with the Array when you select something but if you cancel it errors out. Maybe an

    On Error Resume Next

    is called for

    I don&#039;t know what the Cancel button returns to the variable.

    Check the logic of your Do Loop Until using the "False" - maybe its wrong. Is it supposed to be False without the ""?

    HTH

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    22nd August 2003
    Posts
    38
    Huh. Did you use the exact same code?

    I just tried to run it again and got the same error:

    Run-time error &#039;13&#039;:
    Type mismatch

    I selected three files from a folder and hit open in the dialog box. The debug highlighted the GetOpenFileName line.

    Argh. I don&#039;t get why it would work for you and not for me...

    Help!!
    Kelly.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    7th February 2003
    Location
    Kewarra Beach, Cairns - TNQ
    Posts
    1,055
    Kelly,

    I am no expert with arrays, having just started - so I have only ammended the code and its probably a bit messy - but I think it does whatyou want now.

    VB:
    Option Explicit 
     
    Dim TrialChosen As String ' used As error trap 
    Dim TrialNames() As String ' array of filenames chosen 
    Dim TrialSelectError As Integer ' used As error trap 
     
    Private Sub GetTrialsFolder() 
        Dim filen As Variant 
        Dim i As Integer 
        Dim x As Integer 
        ' Written by Kelly Spencer 
        ' 
        MsgBox ("Using the next screen, select the trials you wish to import." & Chr(13) & _ 
        "Please check to be sure that the path is correct.") 
        TrialChosen = "Yes" 
        ChDir "X:ACCOUNTINGFund_DistributionsData Download" 
         
        For Each filen In Application.GetOpenFilename _ 
            (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _ 
            Title:="Please select the trials you wish to import.", _ 
            MultiSelect:=True) 
            i = i + 1 
            Redim Preserve TrialNames(1 To i) 
            TrialNames(i) = filen 
        Next filen 
         
        If TrialNames(1) = "False" Then 
            TrialSelectError = MsgBox(Prompt:="You must choose at least one trial to be " _ 
            & "imported." & Chr(13) & "If you do not the macro will be cancelled." _ 
            & Chr(13) & "Do you wish to try again?", Buttons:=vbYesNo) 
            If TrialSelectError = vbNo Then 
                TrialChosen = "No" 
                Exit Sub 
            End If 
        End If 
         
         
        For x = 1 To UBound(TrialNames) 
            MsgBox TrialNames(x) 
        Next x 
         
    End Sub 
    
    
    Regards
    Weasel

    ps: you have to fix the file path as the board doesnt like backslashes
    Kind Regards
    Pesky Weasel
    "I have a plan so cunning, you could put a tail on it and call it a Weasel." EB
    Eagles may soar, but Weasels don't get sucked into jet engines.
    Templates and Calculators
    The Way of the Weasel
    Download Ivan &amp; Colo's HTML Maker Here
    101 Excel Hacks - Great New Book
    Huge Savings on Excel Training

  5. #5
    Join Date
    22nd August 2003
    Posts
    38
    As far as the error traps etc...I have successfully used this exact same code in other apps to open single files. The only difference is that the variable in the TrialNames spot is not specified or used as an array.

    So I know the " " need to be there on "False". GetOpenFileName returns the string False if the user hits cancel. I assumed that this would be written to the array position 1 so TrialNames(1) would be the correct place to look for it. Perhaps that is what is incorrect? Remember I&#039;ve never used array variables before...

    Thanks,
    Kelly.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,313
    Hi Kelly,

    Try this, if files are selected the variant array is automatically populated,
    If the user cancels the dialog then the IsArray will catch it.
    VB:
    Dim vntTrialNames As Variant ' array of filenames chosen 
    Dim TrialChosen As String ' used As error trap 
    Dim TrialSelectError As Integer ' used As error trap 
    Private Sub GetTrialsFolder() 
        ' 
        ' Written by Kelly Spencer 
        ' 
        Dim intIndex As Integer 
         
        MsgBox ("Using the next screen, select the trials you wish to import." & Chr(13) & _ 
        "Please check to be sure that the path is correct.") 
        TrialChosen = "Yes" 
        ChDir "X:\ACCOUNTING\Fund_Distributions\Data Download\" 
         
        Do 
            vntTrialNames = Application.GetOpenFilename _ 
            (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _ 
            Title:="Please select the trials you wish to import.", _ 
            MultiSelect:=True) 
             
            If Not IsArray(vntTrialNames) Then 
                TrialSelectError = MsgBox(Prompt:="You must choose at least one trial to be " _ 
                & "imported." & Chr(13) & "If you do not the macro will be cancelled." _ 
                & Chr(13) & "Do you wish to try again?", Buttons:=vbYesNo) 
                If TrialSelectError = vbNo Then 
                    TrialChosen = "No" 
                    Exit Sub 
                End If 
            Else 
                TrialSelectError = vbNo 
                For intIndex = LBound(vntTrialNames) To UBound(vntTrialNames) 
                    MsgBox "Choice " & intIndex & " was " & vntTrialNames(intIndex) 
                Next 
            End If 
        Loop While TrialSelectError = vbYes 
        ' 
    End Sub 
    
    
    Cheers
    Andy

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    22nd August 2003
    Posts
    38
    Weasel&#039;s works, but only if the user doesn&#039;t hit cancel.

    Now I&#039;m going to try Andy&#039;s.

    You guys are awesome!

    Kelly.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    22nd August 2003
    Posts
    38
    Andy&#039;s solution works great :-)

    Now a question: I was trying not to spec it as a variant array because I&#039;ve read that they are not ideal (memory or speed wise? can&#039;t remember). I know I&#039;m getting string data back from my GetOpenFileName, so why do I need a variant array rather than a string array?

    Kelly.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745
    Hi Kelly,

    Rather than an explicit array variable, try using a Variant to contain the array of entries and checking these for False.

    Alternatively, use TyeName to see if a Boolean value was returned (ie the False generated by Cancel).

    Like this:
    VB:
    Option Base 1 
     
    Private Sub Test() 
        Dim varNames As Variant, i As Integer 
         
        varNames = Application.GetOpenFilename(MultiSelect:=True) 
         
        If TypeName(varNames) = "Boolean" Then 
            MsgBox "You cancelled!" 
            Exit Sub 
        End If 
         
        For i = LBound(varNames) To UBound(varNames) 
            MsgBox varNames(i) 
        Next i 
         
    End Sub 
    
    
    HTH

    EDIT : Sorry - work distractions! Well done Andy
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

  10. #10
    Join Date
    7th February 2003
    Location
    Kewarra Beach, Cairns - TNQ
    Posts
    1,055
    Originally posted by kspencer
    Andy&#039;s solution works great :-)

    Now a question: I was trying not to spec it as a variant array because I&#039;ve read that they are not ideal (memory or speed wise? can&#039;t remember). I know I&#039;m getting string data back from my GetOpenFileName, so why do I need a variant array rather than a string array?

    Kelly.
    I believe that the For...Next loop will not accept a string, thats why I went for a variant.

    regards
    Weasel

    ps: Sorry about the error catching I was caught up in getting first part to work - nice one Andy
    Kind Regards
    Pesky Weasel
    "I have a plan so cunning, you could put a tail on it and call it a Weasel." EB
    Eagles may soar, but Weasels don't get sucked into jet engines.
    Templates and Calculators
    The Way of the Weasel
    Download Ivan &amp; Colo's HTML Maker Here
    101 Excel Hacks - Great New Book
    Huge Savings on Excel Training

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Open Multiple Files Via GetOpenFileName
    By richadj4 in forum EXCEL HELP
    Replies: 3
    Last Post: March 27th, 2008, 07:54
  2. GetOpenFileName To Open Multiple Files
    By rshinn in forum EXCEL HELP
    Replies: 5
    Last Post: July 5th, 2007, 07:53
  3. Multiple Files GetOpenFileName
    By Joe Derr in forum EXCEL HELP
    Replies: 6
    Last Post: January 9th, 2007, 07:14
  4. List Files in Order of GetOpenFileName Choice
    By jolivanes in forum EXCEL HELP
    Replies: 4
    Last Post: December 27th, 2006, 00:33
  5. GetOpenFileName Slow With Multiple Files
    By snic in forum EXCEL HELP
    Replies: 1
    Last Post: November 23rd, 2006, 06:27

Bookmarks

Posting Permissions

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