VBA – How to use COUNTA for range in closed workbook?

While I don't believe that you can do a COUNTA on a closed workbook, if you have the formula in a cell in the closed workbook, you can extract it from there. Please see the attached example files.
control.xlsm
countA.xlsx

I can do a COUNTA function in my current workbook that references the other workbook, but it will only update if I open the other workbook.  I'm looking for a solution that does not require opening the other workbook, because I have 7 files that I need to do this with.  Since the files are downloaded every day, adding the COUNTA on the closed workbook is not feasible.

Sorry ... I don't know how to do that with a closed workbook - maybe someone else would know.
Failing that, could you programmatically open & close the files?

Yes I can, as a backup measure.  I still hoping that we can somehow add the below line to your code.

WorksheetFunction.CountA(Range("A1:A"))

Select allOpen in new window

I'm posting your code for others to see.

Private Function GetValue(path, file, sheet, ref)
'   Retrieves a value from a closed workbook
    Dim arg As String
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
'   Create the argument
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Address(, , xlR1C1)
      
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

Sub Main()
    path = ActiveWorkbook.path
    file = "countA.xlsx"
    sheet = "Sheet1"
    cell = "C2"
    Range("B1").Value = GetValue(path, file, sheet, cell)
End Sub

Select allOpen in new window

COUNTA should work, just tested.
Count on Sheet1, column A in workbook C:\Test\Data.xlsx
=COUNTA('C:\Test\[Data.xlsx]Sheet1'!$A:$A)

You can use SUMPRODUCT to do the same
=SUMPRODUCT(('C:\Test\[Data.xlsx]Sheet1'!$A:$A<>"")*1)

Ejgil - I was not able to get it to work.  Do you have the target workbook closed what entering the formula into the current worksheet?

Hi,

What do you mean exactly by "closed workbook" ?
To my knowledge, this involve setting an ODBC connection to it, and retrieving data with an SQL query.

@Ejgil:
This kind of formula work, but absolute path can be a problem.

Sorry for the long delay.  It works great.

You are welcome. Thanks for feedback.