Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Get the last filled line in excel

Status
Not open for further replies.

Katto

Technical User
Apr 6, 2005
42
US
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


Code:
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
 
In Excel library:
[tt]Const xlUp = -4162 (&HFFFFEFBE)[/tt]
Set xlUp value in your code, otherwise VBS assumes xlUp=0.

combo
 
Hi,

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

/
Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
And here's a reference to a list of ALL the Excel constant values...


Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
I was happy too soon

I added the line

Code:
xlUp = -4162

and the LastRow line still gives me an error: Invalid or unqualified reference
 
Did you heed my code that revised your code?

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/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!
 
Oops, sorry, I fixed a typo and now it works.

All set,

Thank you
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top