count rows in closed workbook without opening it

Hi

i have some performance issues with my macros with too much opening and closing of a number of workbooks.

i am thinking of an alternative to copying data from an entire sheet in a workbook without opening it. Hopefully that might fasten things up.

The code below is returning 1 for the variable mycount which is not right. it should be 300.. any ideas? or any suggestions? Thanks guys

Sub chekc()

Dim mycount As Long
mycount = Application.WorksheetFunction.CountA("'/Users/JohnSmith/Desktop/[myWB.xlsx]Sheet1'!A:A")
Debug.Print mycount


    strPath = "/Users/JohnSmith/Desktop/"
    strFile = "myWB.xls"
    strSheet = "Sheet1"
    strRng = Range("A1:A" & mycount)
    
    strRef = "'" & strPath & "[" & strFile & "]" & strSheet & "'!" & strRng
    
    Result = ExecuteExcel4Macro(strRef)

End Sub

Originally Posted by xlnitwit

Hi,

It is not possible to use WorksheetFunction.CountA with a range in a closed workbook. Currently it is merely counting the string you pass to it, which is why it returns 1.

ok thanks for that. could your recommend a faster way to copy data from worksheets into arrays? Someone recommended ADO...but i have not use that before...All i need to do is read data in 8 worksheets to arrays..

I doubt that opening/closing is the issue. It is the writing. I am not sure why you say copy an entire sheet. Once open, you can easily get the sheet's UsedRange.

Getting value(s) like the Excel4 method is not copy. You can get the values in other ways like indirect referencing or ADO. Both of which give you values.

For now, let's explore the copy issue a bit. Are you using the Application options to speed things up? e.g.

'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
Public glb_origCalculationMode As Integer

Sub SpeedOn(Optional StatusBarMsg As String = "Running macro...")
  glb_origCalculationMode = Application.Calculation
  With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    .Cursor = xlWait
    .StatusBar = StatusBarMsg
    .EnableCancelKey = xlErrorHandler
  End With
End Sub

Sub SpeedOff()
  With Application
    .Calculation = glb_origCalculationMode
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    .CalculateBeforeSave = True
    .Cursor = xlDefault
    .StatusBar = False
    .EnableCancelKey = xlInterrupt
  End With
End Sub

Sub Yours()
    On Error GoTo EndSub
    SpeedOn
     
' your stuff here
     
EndSub:
    SpeedOff
End Sub