Get the last filled line in excel

[Katto]

Hello,

I am trying to convert a VBA script to VBS. The script below gives me an error on the line starting with LastRow (invalid or unqualified reference).
Not sure how to fix this.

Thanks

SPATH = "Z:\MyNetworkScriptsNew\Simulation_Journal\"
strExcelPath = SPATH + "Simulations_Journal_ANTEG_202004_0723.xlsm"

Set objExcel = CreateObject("Excel.Application")
  
' sheet1 - MAIN
' sheet2 - Simulations_journal
' sheet3 - Misc

objExcel.Workbooks.Open strExcelPath
objExcel.Visible = True
Set objSheet = objExcel.ActiveWorkbook.Worksheets(2)
LastRow = objExcel.ActiveWorkbook.Worksheets(2).Cells(.Rows.Count, 4).End(xlUp).Row


pako = CStr(LastRow) + Chr(10) + CStr(LastColumn)

objExcel.Cells(LastRow + 2, 1).value = Now
objExcel.Cells(LastRow + 2, 2).value = Now
objExcel.Cells(LastRow + 2, 4).value = MyProjectName
objExcel.Cells(LastRow + 2, 5).value = MyProjectPath

' Excel Save, Quit and Unload
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
objExcel.DisplayAlerts = True
Set objExcel = Nothing

 

[combo]

In Excel library:
[tt]Const xlUp = -4162 (&HFFFFEFBE)[/tt]
Set xlUp value in your code, otherwise VBS assumes xlUp=0.

combo

 

[SkipVought]

Hi,

Set objSheet = objExcel.ActiveWorkbook.Worksheets(2)
With objSheet
   LastRow = .Cells(.Rows.Count, 4).End(-4162).Row
End With

/

https://docs.microsoft.com/en-us/office/vba/api/excel.xldirection

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

And here's a reference to a list of ALL the Excel constant values...

https://docs.microsoft.com/en-us/office/vba/api/excel.constants

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Katto]

Great, Thank you all

 

[Katto]

I was happy too soon

I added the line

xlUp = -4162

and the LastRow line still gives me an error: Invalid or unqualified reference

 

[SkipVought]

Did you heed my code that revised your code?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Katto]

Oops, sorry, I fixed a typo and now it works.

All set,

Thank you