So Question again; Why does COUNTA('C:\Users\User\Documents\[filename.xlsm]'!$A:$A) work but not CountIf? (and closed .xlsm versus closed .xls/x?)

[Spyros13]

Interesting question I encountered. Why does the following work

Sub main()

Dim FName1 As String

'   Path to Family Mapping.xlsx
    'FName1 = "C:\Users\User\Documents\[filename.xlsx]" IGNORE
    'FName1 = "C:\Users\User\Documents\[filename.xls]" IGNORE 
    FName1 = "C:\Users\User\Documents\[filename.xlsm]" 'WORKS FOR .XLSM
   
    'get row count of closed workbook [Family Mapping.xlsx] Worksheet "BMS-DES-POBA"
    Const ShName1 As String = "Sheet1"
    Const ColNo1 As Integer = 1
    Dim ShNew1 As Worksheet
    Dim LastRow5 As Long
    Dim LastRow6 As Long
    Debug.Print FName1
    Application.DisplayAlerts = False
    Set ShNew1 = Worksheets.Add
    With ShNew1.Range("A1")
        .FormulaR1C1 = "=COUNTa('" & FName1 & ShName1 & "'!C" & ColNo1 & ")"
        '.FormulaR1C1 = "=COUNTif('" & FName1 & ShName1 & "'!C" & ColNo1 & "," & Chr(34) & "*" & Chr(34) & ")" 'DITTO
        '.FormulaR1C1 = "=COUNTif('" & FName1 & ShName1 & "'!C" & ColNo1 & "," & Chr(34) & "*" & Chr(34) & ")" THIS SHOULD WORK (AND DOES IF THE FILE IS OPENED, BUT NOT WHEN CLOSED)
    
        'LastRow5 = .Value 'IGNORE
    End With
   
    'ShNew1.Delete 'IGNORE
    Application.DisplayAlerts = True
    Debug.Print Trim(LastRow5) + 1
End Sub

and even without a macro, I can read the closed files ranges and rows and get an accurate response (open .xlsm files) , with this typed out in A1:

=COUNTA('C:\Users\User\Documents\[filename.xlsm]Sheet1'!$A:$A)

but this returns error when the file is closed?

=COUNTIF('C:\Users\User\Documents\[filename.xlsm]Sheet1'!$A:$A,"*")

but yet displays correct result when opened?

Is it something to do with the way I have written

FormulaR1C1 = "=COUNTif('" & FName1 & ShName1 & "'!C" & ColNo1 & "," & Chr(34) & "*" & Chr(34) & ")"

or is it the macro (?? doubt that ) or is it some property of CountA that allowes it to work no matter if the file is closed or not, but yet same propery with countif can only let it work with open workbooks. What detfect does Countif(range,"*") have compared to counta(range).

Anyone know? Can anyone explain ?? and thus enlighten me?? Many thanks for reading and your responses if you do.

 

[MARK858]

See response #3 in the link below.

List of functions that work with closed workbooks

I know of a couple of functions that can be used on closed workbooks such as sumproduct and vlookup. Is there a list of functions somewhere that have this capability, or are these two the only ones? Thank you for helping me expand my knowledge of Excel.

www.mrexcel.com

 

[Spyros13]

BOWING TO YOU lieing prostrate on the floor at your feet with a clasped hand prayer symbol above on the crown of my head !!! !! Thank You!!

 

[MARK858]

BOWING TO YOU

Don't bow to me (maybe to Aladin) Happy it helped.