Subscript out of range

Question

Hello, I am trying to run this code and getting a subscript out of range error on line "Set destSheet = ThisWorkbook.Worksheets("Report")" and I cannot understand why or how to fix it, I am trying to go a directory of subfolders and copy certain cells into the report workbook.

Sub Copdata()  
    Dim r As Long  
    Call GetFiles("D:\data\Analysis\records\", r)  
End Sub  
Sub GetFiles(ByVal path As String, r As Long)  
Dim fso As Object  
Set fso = CreateObject("Scripting.FileSystemObject")  
  
Dim folder As Object  
Set folder = fso.GetFolder(path)  
  
Dim subfolder As Object  
Dim file As Object  
  
For Each subfolder In folder.SubFolders  
    GetFiles subfolder.path, r  
Next subfolder  
  
Set destSheet = ThisWorkbook.Worksheets("Report")  
  
For Each file In folder.Files  
    Set fromWorkbook = Workbooks.Open(file)  
    With fromWorkbook.Worksheets("Dashboard")  
        destSheet.Range("A2").Offset(r).Value = .Range("C5").Value  
        destSheet.Range("B2").Offset(r).Value = .Range("C3").Value  
        r = r + 1  
    End With  
    fromWorkbook.Close savechanges:=False  
Next file  
  
Set fso = Nothing  
Set folder = Nothing  
Set subfolder = Nothing  
Set file = Nothing  
  
  
End Sub  

Answer 1

Just guessing here, as you haven't posted all the information about your working environment. ThisWorkbook is the file that contains the running macro. https://learn.microsoft.com/en-us/office/vba/api/excel.application.thisworkbook

If your data is in a different workbook from the one with the macro, you would want to use a different object, like ActiveWorkbook.

Answer 2

@Orfevre

I'm missing some context here, however I noticed that your recursive 'GetFiles(ByVal path As String, r As Long)' routine, appears to attempt to open all files located in your "D:\data\Analysis\records\" folder, including any contained within nested folders. Perhaps the routine is attempting to open a file that can't be accessed or read by the Workbooks.Open method. The FileSystemObject (FSO) class objects - such as "File" - have access to a "Type" property you can use to check the file extension (e.g., .csv, .txt, .xls), or alternatively, you may check the extension using string routines. Have you tried using a direct reference to the "Microsoft Scripting Runtime", so that you may declare specific FSO objects, in order to access the methods?

Dim file As Object  

For Each subfolder In folder.SubFolders  
[...]  
 For Each file In folder.Files  
[...]  
Set fromWorkbook = Workbooks.Open(file)