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

Question

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?

Why do you need an array? That code only allows the user to select one file. — norie, Mar 22 '21 at 14:22
I want the search to run over multiple files but I want to select them one at a time so I thought I would store each file path in a list to run over them with the search loop — Shawn, Mar 22 '21 at 14:31
Refer [This](https://stackoverflow.com/questions/44439638/change-file-names-within-subfolders-with-vba/44440788#44440788) — Dy.Lee, Mar 22 '21 at 14:36
Since you don't know how many files the user will select, you will need to use a Do loop instead of a For loop. Also, I would store the files in a Collection rather than an array. — Brian M Stafford, Mar 22 '21 at 14:37

FaneDuru · Accepted Answer · 2021-03-23T08:53:24.097

1

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

edited Mar 23 '21 at 08:53

answered Mar 22 '21 at 14:45

FaneDuru

38,298
4
19
27

This works great. Since the array is being redefined when needed anyway, could I set the array size to 1 and it will increase as I select more? – Shawn Mar 24 '21 at 15:06
@Shawn Malone: You can, but it is not good from memory handling, point of view. It is good to `Redim preserve` as rare as possible. That's why I added the line adding another set (in my example 100 elements) in case of reaching the array limit... – FaneDuru Mar 24 '21 at 15:15
I see, thanks. Is there a way to make it save the selected items as workbooks instead? I am trying to search for a range in each book and it doesn't look like filepaths will accomplish that. I tried to change the FullPath to Workbook but it gives an error – Shawn Mar 24 '21 at 15:17
I am driving now... I will telll something after some hours, when I will be at home. – FaneDuru Mar 24 '21 at 15:31
Try declaring the array on top of the code module. In this way it will remain and can be used from other Subs... – FaneDuru Mar 24 '21 at 15:38

score 1 · Answer 2 · answered Mar 22 '21 at 14:56

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

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

2 Answers2