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\"
TrialNames() = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xls), *.xls", _
Title:="Please select the trials you wish to import.", _
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"
Loop Until TrialNames(1) <> "False"
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).