EXCEL VBA 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

 

[AlanSidman]

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

 

[vletm]

Duplicated with this:

get the last row from a closed workbook without opening it

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...

chandoo.org

This thread is closed.