VBA - Pull Data from Multiple Closed Spreadsheets

[jamescooper]

So I have the following code which pulls 2 cells from the same closed workbook successfully and populates them into the relevant cells in the specified workbook.

How would I continue this on and obtain values from another workbook please?

Public Sub Copy_Cell_Value_From_Workbooks_To_New_Workbook()

    Dim destcell As Range, r As Long
   
    Dim fileSpec As String, folderPath As String, fileName As String
    myValue = 1801
    cellValue1 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("U6")
      
    fileSpec = "C:\Users\jamesco\OneDrive for Business\PMO - Schedule 4 - Sharing best practice\2. CrossCountry Trains\Revenue\" & myValue & "\" & cellValue1 & ""
   
    Set destcell1 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("C6")
    Set destcell2 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("D6")
    r = 0
    
    folderPath = Left(fileSpec, InStrRev(fileSpec, "\"))
    fileName = Dir(fileSpec)
    While Len(fileName) <> 0
        destcell1.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "G12")
        destcell2.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "G16")
        r = r + 1
        fileName = Dir
    Wend
    


    
End Sub


Private Function GetCellValue(ByVal workbookFullName As String, sheetName As String, cellsRange As String)


    Dim folderPath As String, fileName As String
    Dim arg As String
    
    folderPath = Left(workbookFullName, InStrRev(workbookFullName, "\"))
    fileName = Mid(workbookFullName, InStrRev(workbookFullName, "\") + 1)
        
    arg = "'" & folderPath & "[" & fileName & "]" & sheetName & "'!" & Range(cellsRange).Address(True, True, xlR1C1)
    Debug.Print arg
          
    'Execute Excel 4 Macro with argument to closed workbook
    
    GetCellValue = ExecuteExcel4Macro(arg)
    
End Function

I have tried below but doesn't quite work, many thanks:

Public Sub Copy_Cell_Value_From_Workbooks_To_New_Workbook()


    Dim destcell As Range, r As Long
   
    Dim fileSpec As String, folderPath As String, fileName As String
    myValue = 1801
    cellValue1 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("U6")
    cellValue2 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("U7")
      
    fileSpec1 = "C:\Users\jamesco\OneDrive for Business\PMO - Schedule 4 - Sharing best practice\2. CrossCountry Trains\Revenue\" & myValue & "\" & cellValue1 & ""
    fileSpec2 = "C:\Users\jamesco\OneDrive for Business\PMO - Schedule 4 - Sharing best practice\2. CrossCountry Trains\Revenue\" & myValue & "\" & cellValue2 & ""
   
    Set destcell1 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("C6")
    Set destcell2 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("D6")
    Set destcell3 = Workbooks("Forecast v.s. D42 Statement Template.xlsx").Sheets("Revenue").Range("C7")
    r = 0
    
    folderPath = Left(fileSpec, InStrRev(fileSpec, "\"))
    fileName = Dir(fileSpec)
    While Len(fileName) <> 0
        destcell1.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "G12")
        destcell2.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "G16")
        destcell3.Offset(r, 0).Value = GetCellValue(folderPath & fileName, "Forecast", "H12")
        r = r + 1
        fileName = Dir
    Wend
    


    
End Sub


Private Function GetCellValue(ByVal workbookFullName As String, sheetName As String, cellsRange As String)


    Dim folderPath As String, fileName As String
    Dim arg As String
    
    folderPath = Left(workbookFullName, InStrRev(workbookFullName, "\"))
    fileName = Mid(workbookFullName, InStrRev(workbookFullName, "\") + 1)
        
    arg = "'" & folderPath & "[" & fileName & "]" & sheetName & "'!" & Range(cellsRange).Address(True, True, xlR1C1)
    Debug.Print arg
          
    'Execute Excel 4 Macro with argument to closed workbook
    
    GetCellValue = ExecuteExcel4Macro(arg)
    
End Function

 

[jamescooper]

Any ideas? Thanks.