VBA to Retrieve Multiple Cells from Closed Workbook

[Barklie]

Greetings,

I am trying to write a routine to retrieve values from multiple cells (5 total- B2:B7) from a series of closed workbooks. The code I have written works, but it only retrieves the first value (B2). I am trying to avoid doing the retrieve five times over to get what I want, but I know I have that option if need be. I think it's a syntax thing but cannot figure it out for the life of me. My code is below:

Sub CustTempScrape2()

'Declare universal variables
CustSheet = "Lead"

'Begin through every customer row, ignoring errors
On Error Resume Next
For CustomerRow = 2 To Cells(Rows.Count, 1).End(xlUp).Row

    'Continue if Street, City, State, Zip, Phone, or Email is blank
    If WorksheetFunction.CountA(Range(Range("C" & CustomerRow), Range("H" & CustomerRow))) < 6 Then
        CustTempPath = CreateObject("WScript.Shell").ExpandEnvironmentStrings("%UserProfile%") & "\OneDrive\Solar Company\Sales\Customer Folders\" & Range("B" & CustomerRow) & "\"
        CustFile = Range("B" & CustomerRow) & ".xlsm"
       
        'Continue if Customer Workbook is present
        If Dir(CustTempPath & CustFile) <> "" Then
            PhonePath = "'" & CustTempPath & "[" & CustFile & "]" & CustSheet & "'!" & Range("B2:B7").Address(1, 1, xlR1C1)' THIS IS THE LINE THAT NEEDS FIXING
            Range("G4") = PhonePath
            PhoneValue = ExecuteExcel4Macro(PhonePath)
            If Range("G" & CustomerRow) = "" And PhoneValue <> "0" Then Range("G" & CustomerRow) = PhoneValue
        End If
    End If
Next


End Sub

Any help would be tremendously appreciated.

Thanks,
Barklie

 

[GWteB]

Try replacing this
PhonePath = "'" & CustTempPath & "[" & CustFile & "]" & CustSheet & "'!" & Range("B2:B7").Address(1, 1, xlR1C1)' THIS IS THE LINE THAT NEEDS FIXING

with this

PhonePath = "'" & CustTempPath & "[" & CustFile & "]" & CustSheet & "'!" & Range("B2:B7").Value