CountA using data in the worksheet in order to reference external file

Question

I'm trying to get this bit of code working:

Sub GetExternalData()

    Dim wbPath As String, WorkbookName As String
    Dim WorksheetName As String, CellRef As String
    Dim Ret As String, i As Long, N As Long

    For i = 1 To Sheets("x").Cells(Rows.Count, 1).End(xlUp).Row

        wbPath = Sheets("x").Cells(i, 1).Value
        WorkbookName = Sheets("x").Cells(i, 2).Value
        WorksheetName = Sheets("x").Cells(i, 3).Value
        CellRef = Sheets("x").Cells(i, 4).Value

        Ret = Application.WorksheetFunction.CountA("'" & wbPath & "[" & WorkbookName & "]" & _
              WorksheetName & "'!" & Range("C:C").Select)

        Sheets("x").Cells(i, 5).Value = ExecuteExcel4Macro(Ret)
        
    Next i
End Sub

Basically, I have a folder path in column 1, file name in column 2 and worksheet name in column three. I want to take these and find the number of items in column C of the reference file.

Currently the equation only outputs a value of 1 when there are 30 some values in the column. Wondering if there is something wrong with how I am calling the COUNTA function, result of which is later used in ExecuteExcel4Macro. Originally it was just meant to call a specific value in ref file (value at C2), for which it did work.

Answer 1

ExecuteExcel4Macro can be used to read individual cells, but you can't pass any function to it: only if it's a valid Excel 4.0 macro function (see https://exceloffthegrid.com/download/4095/)

Easiest to do this by opening the workbooks:

Sub GetExternalData()

    Dim wbPath As String, WorkbookName As String
    Dim WorksheetName As String, CellRef As String
    Dim Ret as long, i As Long, N As Long

    With ThisWorkbook.Worksheets("x")
        For i = 1 To .Cells(.Rows.Count, 1).End(xlUp).Row
    
            wbPath = .Cells(i, 1).Value
            WorkbookName = .Cells(i, 2).Value
            WorksheetName = .Cells(i, 3).Value
            'CellRef = .Cells(i, 4).Value
            
            With Workbooks.Open(wbPath & "\" & WorkbookName).Worksheets(WorksheetName)
                Ret = Application.CountA(.Columns("C"))
                .Parent.Close False
            End With
            .Cells(i, 5).Value = Ret
    
        Next i
    End With
End Sub