Find last row of a closed workbook

[tiredofit]

This code

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

was taken from here:

https://www.mrexcel.com/forum/excel-questions/155993-last-row-used-closed-workbook-sheet.html

How can I change it so instead of displaying the result in a cell, I want to assign a variable to it, eg

Dim a as Integer

a="=COUNTA('" & FName & ShName & "'!C" & ColNo & ")"

Thanks

 

[mikerickson]

In that code, the result is put in a variable, LastRow.

 

[tiredofit]

Thanks but is it possible to put it into a variable directly, WITHOUT first assigning to the cell A1 first?

 

[NARAYANK991]

Hi ,

See if this works :

Sub Test()
'   file name, sheet name and column number - change to suit
    Const FName As String = "F:\Excel File Downloads\Abhijeet\[Sample File.xlsm]"
    Const ShName As String = "Sheet1"
    Const ColNo As Integer = 1
    Dim LastRow As Long
    LastRow = Application.Evaluate("=COUNTA('" & FName & ShName & "'!C" & ColNo & ")")
    MsgBox LastRow
End Sub

 

[tiredofit]

Hi ,

See if this works :

Sub Test()
'   file name, sheet name and column number - change to suit
    Const FName As String = "F:\Excel File Downloads\Abhijeet\[Sample File.xlsm]"
    Const ShName As String = "Sheet1"
    Const ColNo As Integer = 1
    Dim LastRow As Long
    LastRow = Application.Evaluate("=COUNTA('" & FName & ShName & "'!C" & ColNo & ")")
    MsgBox LastRow
End Sub

Afraid not, it returns 1 when it should be a lot more.

EDIT SORTED:

 LastRow = Application.Evaluate("=COUNTA('" & FName & ShName & "'!$A:$A)")

Thanks

 

[NARAYANK991]

Hi ,

Was the earlier coding showing the proper result in Lastrow ?

 

[mikerickson]

You may have to use a temporary remote cell to get your result from the closed workbook

With ActiveSheet.Range("AZ65536")
     .FormulaR1C1 = "=COUNTA('" & FName & ShName & "'!C" & ColNo & ")"
     LastRow = .Value
     .Delete
End With

 

[tiredofit]

Yes, I've tested it and LastRow changes depending whether the workbook is open or not.

 

[TDCox]

Like this:

Private Function LastRowFromClosedFile(InFolder As String, InFile As String, OnSheet As String, Optional FromCell As String = "A1", Optional InColumn As Long = 1) As Long
'
' LastRowFromClosedFile Function
'
' Based on a macro created 2005-08-08 by Andrew Poulsom (https://www.mrexcel.com/forum/members/andrew-poulsom.html) shared on https://www.mrexcel.com/forum/excel-questions/155993-last-row-used-closed-workbook-sheet.html
' Created 2018-11-22 by Timothy Daniel Cox
' last edited 2018-11-22 by Timothy Daniel Cox
'
' Returns the LastRow on a given sheet from a closed file: returns "-1" if there was an error
'
' Uses: N/A
' References: N/A
'
On Error GoTo ErrorHandler
    Dim ShNew As Worksheet
    Application.DisplayAlerts = False
    Set ShNew = Worksheets.Add
    With ShNew.Range("A1")
        .FormulaR1C1 = "=COUNTA('" & InFolder & "[" & InFile & "]" & ECMSDataSheetName & "'!C" & InColumn & ")"
        LastRowFromClosedFile = .Value
    End With
    ShNew.Delete
    Application.DisplayAlerts = True
Exit Function
ErrorHandler:
    Err.Clear
    LastRowFromClosedFile = -1
End Function

This code

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

was taken from here:

https://www.mrexcel.com/forum/excel-questions/155993-last-row-used-closed-workbook-sheet.html

How can I change it so instead of displaying the result in a cell, I want to assign a variable to it, eg

Dim a as Integer

a="=COUNTA('" & FName & ShName & "'!C" & ColNo & ")"

Thanks

 

[TDCox]

...though "ECMSDataSheetName" should of course be "OnSheet" (can't edit)