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