Announcement

Collapse
No announcement yet.

debug help! getopenfilename on multiple files

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:
    Code:
        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, 03:50.

  • #2
    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
    There are three types of people in this world.
    Those who can count and those who can&#039;t.

    Comment


    • #3
      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.

      Comment


      • #4
        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.

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

        Comment


        • #5
          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.

          Comment


          • #6
            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.
            Code:
            Dim vntTrialNames As Variant &#039; array of filenames chosen
            Dim TrialChosen As String &#039; used as error trap
            Dim TrialSelectError As Integer &#039; used as error trap
            Private Sub GetTrialsFolder&#40;&#41;
            &#039;
            &#039; Written by Kelly Spencer
            &#039;
                Dim intIndex As Integer
                
                MsgBox &#40;"Using the next screen, select the trials you wish to import." & Chr&#40;13&#41; & _
                    "Please check to be sure that the path is correct."&#41;
                TrialChosen = "Yes"
                ChDir "X&#58;\ACCOUNTING\Fund_Distributions\Data Download\"
                
                Do
                    vntTrialNames = Application.GetOpenFilename _
                        &#40;FileFilter&#58;="Microsoft Excel Files &#40;*.xls&#41;, *.xls", _
                        Title&#58;="Please select the trials you wish to import.", _
                        MultiSelect&#58;=True&#41;
                    
                    If Not IsArray&#40;vntTrialNames&#41; Then
                        TrialSelectError = MsgBox&#40;Prompt&#58;="You must choose at least one trial to be " _
                        & "imported." & Chr&#40;13&#41; & "If you do not the macro will be cancelled." _
                        & Chr&#40;13&#41; & "Do you wish to try again?", Buttons&#58;=vbYesNo&#41;
                        If TrialSelectError = vbNo Then
                            TrialChosen = "No"
                            Exit Sub
                        End If
                    Else
                        TrialSelectError = vbNo
                        For intIndex = LBound&#40;vntTrialNames&#41; To UBound&#40;vntTrialNames&#41;
                            MsgBox "Choice " & intIndex & " was " & vntTrialNames&#40;intIndex&#41;
                        Next
                    End If
                Loop While TrialSelectError = vbYes
            &#039;
            End Sub
            Cheers
            Andy

            Cheers
            Andy

            Comment


            • #7
              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.

              Comment


              • #8
                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.

                Comment


                • #9
                  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:
                  Code:
                  Option Base 1
                  
                  Private Sub Test&#40;&#41;
                      Dim varNames As Variant, i As Integer
                      
                      varNames = Application.GetOpenFilename&#40;MultiSelect&#58;=True&#41;
                      
                      If TypeName&#40;varNames&#41; = "Boolean" Then
                          MsgBox "You cancelled!"
                          Exit Sub
                      End If
                      
                      For i = LBound&#40;varNames&#41; To UBound&#40;varNames&#41;
                          MsgBox varNames&#40;i&#41;
                      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

                  Comment


                  • #10
                    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

                    Comment


                    • #11


                      Hi Kelly,

                      I don&#039;t believe you can return a string array from a function. I could be wrong, it&#039;s happened before :biggrin:

                      You can however transfer the result into your string array.

                      Code:
                      redim strTrialNames&#40;LBound&#40;vntTrialNames&#41; To UBound&#40;vntTrialNames&#41; &#41; as string
                      For intIndex = LBound&#40;vntTrialNames&#41; To UBound&#40;vntTrialNames&#41; 
                         strTrialNames&#40;intindex&#41; = vntTrialNames&#40;intindex&#41;
                         MsgBox "Choice " & intIndex & " was " & strTrialNames&#40;intIndex&#41; 
                      Next
                      Cheers
                      Andy

                      Cheers
                      Andy

                      Comment

                      Working...
                      X