Interesting question I encountered. Why does the following work
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:
but this returns error when the file is closed?
but yet displays correct result when opened?
Is it something to do with the way I have written
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.
Code:
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:
Code:
=COUNTA('C:\Users\User\Documents\[filename.xlsm]Sheet1'!$A:$A)
but this returns error when the file is closed?
Code:
=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
Code:
FormulaR1C1 = "=COUNTif('" & FName1 & ShName1 & "'!C" & ColNo1 & "," & Chr(34) & "*" & Chr(34) & ")"
Anyone know? Can anyone explain ?? and thus enlighten me?? Many thanks for reading and your responses if you do.