CODEWITHSUNDEEP
excelvba
Shawn
Shawn

Reputation: 17

How can I store multiple selected file paths in an array from a for loop?

I want to take the file path using the

With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show
        
        'Store in fullpath variable
        fullpath = .SelectedItems.Item(1)
    End With

And run this over a for loop until the user says they are finished and picked all the files they are looking for. I don't know how to store each file path in an array though because I want to run a search for multiple inputted phrases across each file and return an array with all the positive search results. (thinking nested loops) Suggestions?

Upvotes: 0

Views: 951

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Try this code, please:

Sub PathsInAnArray()
  Dim FullPath As String, arrP, i As Long, elNo As Long
  
  elNo = 100 'use here a number bigger then your estimation about the maximum needed paths
  ReDim arrP(elNo)
  
  With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        Do While .Show = -1
             FullPath = .SelectedItems.item(1)
             'in case of a wrong estimation of the necessary number of paths:
             If i = UBound(arrP) Then ReDim Preserve arrP(UBound(arrP) + elNo)
             arrP(i) = FullPath: i = i + 1
        Loop
    End With

    ReDim Preserve arrP(i - 1)
    'do whatever you need with the paths array...
    Debug.Print "There have been placed " & UBound(arrP) + 1 & " paths in ""arrP"" array..."
    Debug.Print arrP(0) 'first path in the array...
End Sub

It will end looping when you will press Cancel in the dialog window...

Upvotes: 1

Brian M Stafford
Brian M Stafford

Reputation: 8868

The combination of a Do loop and a Collection makes this easy to implement:

Private Sub GetFiles()
   Dim files As Collection
   Set files = New Collection

   With Application.FileDialog(msoFileDialogFilePicker)
      .AllowMultiSelect = False
      .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
      
      Do
         .Show
         If .SelectedItems.Count > 0 Then files.Add .SelectedItems.Item(1)
      Loop Until .SelectedItems.Count = 0

      'process the files as needed
   End With
End Sub

Upvotes: 1

Related Questions