Link to home
Start Free TrialLog in
Avatar of kbay808
kbay808Flag for United States of America

asked on

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

As a check, I am comparing the source data (the closed workbook) to the imported file in the current workbook.  I am trying to do this by counting the non-blank cells in column A in both files.  How do I target another workbook and count the non-blank cells without opening it?
Avatar of Sam Jacobs
Sam Jacobs
Flag of United States of America image

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.
Avatar of kbay808


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?
Avatar of kbay808


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

Open 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

Open in new window

COUNTA should work, just tested.
Count on Sheet1, column A in workbook C:\Test\Data.xlsx

You can use SUMPRODUCT to do the same
Avatar of kbay808


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?

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.

This kind of formula work, but absolute path can be a problem.
Avatar of Professor J
Professor J

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kbay808


Sorry for the long delay.  It works great.
Avatar of Professor J
Professor J

You are welcome. Thanks for feedback.