debug help! getopenfilename on multiple files

[kspencer]

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:

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).

[Neale]

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'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

[kspencer]

Huh. Did you use the exact same code?

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

Run-time error '13':
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't get why it would work for you and not for me...

Help!!
Kelly.

[Pesky Weasel]

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.

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

[kspencer]

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've never used array variables before...

Thanks,
Kelly.

[Andy Pope]

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.

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

[kspencer]

Weasel's works, but only if the user doesn't hit cancel.

Now I'm going to try Andy's.

You guys are awesome!

Kelly.

[kspencer]

Andy's solution works great :-)

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

Kelly.

[Richie(UK)]

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:

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

[Pesky Weasel]

Originally posted by kspencer
Andy's solution works great :-)

Now a question: I was trying not to spec it as a variant array because I've read that they are not ideal (memory or speed wise? can't remember). I know I'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