get the last row from a closed workbook without opening it

[absolute.ivor]

I want to get the last used row of a workbook without opening the workbook. Opening and closing the workbook, even sight unseen, is unacceptable.

The 2002 solution offered by Andrew Poulsom (https://www.mrexcel.com/forum/excel-questions/155993-last-row-used-closed-workbook-sheet.html ) but requires entry of a worksheet. I need the same code without using a worksheet to get the last row number.

Andrews code follows:

Sub Test()
'   file name, sheet name and column number - change to suit
    Const FName As String = "P:\TEMP\MrExcel\FileLinks\[MyFile.xls]"
    Const ShName As String = "Sheet1"
    Const ColNo As Integer = 1
    Dim ShNew As Worksheet
    Dim LastRow As Long
    Application.DisplayAlerts = False
    Set ShNew = Worksheets.Add
    With ShNew.Range("A1")
        .FormulaR1C1 = "=COUNTA('" & FName & ShName & "'!C" & ColNo & ")"
        LastRow = .Value
    End With
    ShNew.Delete
    Application.DisplayAlerts = True
    MsgBox LastRow
End Sub

 

[herofox]

pls move this post to Vba Dep.

 

[AlanSidman]

https://www.exceltip.com/keyboard-other-shortcuts-in-vba/pull-data-from-a-closed-workbook.html

 

[Chihiro]

Some other ideas.

1. If workbook has sheets in flat table structure...
You can use ADO code to count recordset + 1 (header)

2. Use some out of way cell (in workbook that has the code), add formula. (Just realized it's same concept as code you posted after looking at your post again).
Ex:

Sub Demo()
Dim strWb As String: strWb = "'C:\Reports\SS\[SS_Detail_2019-1002.xlsb]"
Dim x
With Sheets("Sheet1").Range("Z1")
    .Formula = "=COUNTA(" & strWb & "Sheet1'!$A:$A)"
    .Value = .Value
    x = .Value
    .Clear
End With

Debug.Print x
End Sub

EDIT: Oh didn't read your initial post in full. You can use ADO to get schema info (i.e. sheet name) from closed workbook. Then supply it to the code, or use ADO itself to query worksheet data and use recordset count.

See link below, for sample code I did a while back to query sheet names from workbook.
https://chandoo.org/forum/threads/using-microsoft-excel-driver-to-query-workbook.39902/#post-238672

 

[vletm]

absolute.ivor
Could previous user of those files save information of the last row to known cell?
and then You could get that value with 'normal' link.

 

[Kenneth Hobson]

What is the sheet name or index number you want this information from? Workbooks don't have ranges. Worksheets have ranges.

 

[absolute.ivor]

What is the sheet name or index number you want this information from? Workbooks don't have ranges. Worksheets have ranges.

The application is a property management reporting system. Each issue, such as say "Outside gutters of the building need repairing" has a dedicated workbook, serially numbered and a concatenated file name made of the seial number, "I00012" & an indication of the issue. "I00012_Outside gutters.xlsx". Each workbook has three sheets, "IssueHeadings", "IssueHistory" and "PlansHistory". The sheet "IssueHeadings" has static fields A2 to P2, and the contents and values of these are simply imported into a consolidated list of all active issue workbooks using a "list files" macro combined with code using the function "GetInformationFromClosedFile" using the static cell addresses for each value in A2 to P2.

The other two sheets are used to enter the ongoing comments and discussions at meetings regarding the issue and the plans to resolve the issue. The more simple case is the "IssueHistory" which only has two fields, column A, the date of the last comment on the issue and column B, the content of the issue discussion. Each time there is a meeting the date thereof is entered in Column A and the comment in Column B. An issue may only be discussed once, or numerous times, so the latest comment will always be in the last used cell in Column B. By knowing the last used row, say row 30, we can then import the data therein by "getting" the data from Sheets("IssueHistory").Range("B30") using the following function:

>> 2nd time ... use code - tags <<​

Public Function GetInfoFromClosedFile(ByVal wbPath As String, wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
    GetInfoFromClosedFile = ""
   
    If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
    If Dir(wbPath & "\" & wbName) = "" Then Exit Function
   
    arg = "'" & wbPath & "[" & wbName & "]" & wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

Thus, what I need is a piece of code similar to that of Andrew Poulsom's previously posted, but using variants, rather than a worksheet and formulas in cells.

 

[YasserKhalil]

Have a look at this link

Get last row in closed workbook using ADODB - Eileen's Lounge

 

[Kenneth Hobson]

If the sheet name is known, ADO can be used to get the rows but your data should be in a matrix. There would be no need for #7 code. Of course ADO offers SQL which can filter the data if sheet name is known.

#7 says that you know the sheet name so i don't know what the issue is.

Another method is to make a scratch worksheet and import all of a known sheet to it. Then standard methods and even more elaborate filtering methods can be used. That was the method first posted in #1.