Loop through VBA collection

[Rymare]

I have an excel with a list of paths:

This list can change length--sometimes there are 100 .xlsx files, sometimes 4, etc. But always xlsx and always in the same sheet in my workbook (Project Specialist Compile, sheet ghgh). The paths are not fixed, they constantly change so though the list appears to share certain patterns, that will not always be the case.

I want to be able to open these excels, take the data from the first sheet in each excel, and merge it into one sheet in my Project Specialist Compile workbook (sheet is called Addresses). Then have the code move to the next path in the collection open that one, copy paste, etc. The excels do not have a fixed range to paste. They all have a header, but can have 1 row with data or 500 rows with data, so I can't set a fixed range to copy there either. They look like this:

I need the code to count what cells have data (paths), put them into the collection, and then loop them ('for each path in collection' syntax). I want to end up with this:

I would like to NOT have each excel pop up, open, copy, paste then close because that could take forever if there's a long list of paths.

The code below always stops at

Set dirObj = mergeObj.GetFolder("vElement.Value")

and shows:

Option ExplicitSub openingandcopying()


Dim bookList As Workbook
Dim wsp As Worksheet: Set wsp = Sheets("ghgh")
Dim mergeObj As Object
Dim dirObj As Object
Dim filesObj As Object
Dim everyObj As Object
Dim colMyCol As New Collection
Dim vElement As Variant
Dim rRange As Range
Dim rCell As Range
Dim lCount As Long




Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
Set rRange = wsp.Range("A1")
Set rRange = Range(rRange, rRange.End(xlDown))


For Each rCell In rRange
   colMyCol.Add rCell.Value
Next


For Each vElement In colMyCol
    Set dirObj = mergeObj.GetFolder("vElement.Value")
    Set filesObj = dirObj.Files
        For Each everyObj In filesObj
        Set bookList = Workbooks.Open(everyObj)
 
        Range("A2:X" & Range("A65536").End(xlUp).Row).copy
        ThisWorkbook.Sheets("Addresses").Activate


        Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
        Application.CutCopyMode = False
        bookList.Close
        Next everyObj
Next vElement


End Sub

 

[shg]

Set dirObj = mergeObj.GetFolder(vElement)

 

[Rymare]

Set dirObj = mergeObj.GetFolder(vElement)

I'm still getting the same error "Path not found" at the same place.

 

[shg]

Does vElement contain a valid path, like "C:\whatever"

 

[Rymare]

vElement should be each of the paths in the first image I posted. Those paths should be in collection, then vElement loops through.

 

[shg]

I can't read your picture. What's in vElement when the code fails?

 

[Rymare]

C:\Users\ABer\One\User Folders\Desktop\Field Inspection\SLIP Packages Prepared\Inland\Murrieta\Menifee\MNFE 1\12345 AD 7\12345 AD 7.xlsx
C:\Users\ABer\One\User Folders\Desktop\Field Inspection\SLIP Packages Prepared\Inland\Murrieta\Menifee\MNFE 1\6789 AD 3\6789 AD 3.xlsx


Should be the collection colMyCol, each of them is a vElement. I hover over the highlighted section, but nothing comes up.

EDIT:

I think the issue may be that I have dirObj = mergeObj.GetFolder(vElement) is trying to look for a folder, not a file, and the paths are files. I don't know, that may be it. I attempted to correct this with Set filesObj.path = vElement and comment out the dirObj but I know that makes no sense, I just wanted to try.

 

[shg]

Why not just

workbooks.open vElement

 

[Rymare]

Why not just

workbooks.open vElement

Because I may have 100s of workbooks in the collection, and also I just tried it and the file crashed.

 

[shg]

But you only need to have one opened at a time (beyond the one the code is running in).