Subscript out of range error when trying to open multiple workbooks

Question

I'm trying to open all selected file using my code. But only the first path is opened when it goes to another path, the error "Subscript out of range" pops up.

Below is my code:

Sub Select_File_Click()
    Dim lngCount As Long
    Dim cl As Range
    Dim c2 As Range
    Dim ItemType As String

    Set cl = ActiveSheet.Cells(1, 3)
    ' Open the file dialog
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Filters.Clear
        .Filters.Add "comma-separated values", "*.csv"
        .InitialFileName = "*" & ItemType & "*.*"
        .InitialView = msoFileDialogViewDetails
        .Show
        For lngCount = 1 To .SelectedItems.Count
            ' Add Hyperlinks
            cl.Worksheet.Hyperlinks.Add _
            Anchor:=cl, Address:=.SelectedItems(lngCount), _
                TextToDisplay:=.SelectedItems(lngCount)
            ' Add file name
            'cl.Offset(0, 1) = _
            '    Mid(.SelectedItems(lngCount), InStrRev(.SelectedItems(lngCount), "\") + 1)
            ' Add file as formula
            cl.Offset(0, 1).FormulaR1C1 = _
                 "=TRIM(RIGHT(SUBSTITUTE(RC[-1],""\"",REPT("" "",99)),99))"


            Set cl = cl.Offset(1, 0)
            Set c2 = cl.Offset(0, 1)
        Next lngCount
        Sheets(1).Cells(1, 1) = .SelectedItems.Count
    End With
End Sub

Sub All_data_Click()
    Dim Count As Integer
    Count = Sheets(1).Cells(1, 1)

    For i = 1 To Count
        pth = Sheets("Sheet1").Cells(i, 3).Value 'Select folder path
        Set LookupWB = Workbooks.Open(Filename:=pth)
    Next i
End Sub

Is there any other way to do this?

Answer 1

The problem is in this line:

pth = Sheets("Sheet1").Cells(i, 3).Value

After the first CSV file is open in Excel, it becomes the active workbook. So, when you call Sheets, it actually refers to the newly opened workbook (i.e., the CSV file) which doesn't have a sheet called "Sheet1", hence, the Subscript out of range error.

You can get around this by calling ThisWorkbook.Sheets instead. The ThisWorkbook property refers to the workbook that is hosting the current running VBA code. So, your All_data_Click would look something like this:

Sub All_data_Click()
    Dim Count As Integer
    Count = Sheets(1).Cells(1, 1)

    For i = 1 To Count
        pth = ThisWorkbook.Sheets("Sheet1").Cells(i, 3).Value 'Select folder path
        Set LookupWB = Workbooks.Open(Filename:=pth)
    Next i
End Sub

Hope that helps.