Vista - Finding the last row of and excel worksheet.

Hi all, I have a script that goes out and searches a spreadsheet for
information. If it finds a null value, I need my script to open up
another logging spreadsheet and look into Column B (Column A has
preset Error ID numbers to help identify the problem) and find the
next open row, then input a text description.

I have everythign working great, except for I can't for the life of me
figure out what code I'm supposed to put in to find the next available
row in excel.

'~~~~~~~~~~~Visual of what my Error Spreadsheet should
read~~~~~~~~~~~~~~~
Col A Col B
ID # Issue
ID 2 Null Value
ID 3 Null Value
ID 4
ID 5

'~~~~~~~~~~~My script that needs help - Not all of
script~~~~~~~~~~~~~~~~~~~~~~~~
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open ("C:\Excelfilename.xls")
Set objSheet = objExcel.ActiveWorkbook.WorkSheets(1)

IntRow = 9
K = 11
R = 18

if objExcel.Cells(intRow, K).Value <> "" then
objExcel.Cells(intRow, R).Interior.ColorIndex = 4
else
objExcel.Visible = False
objExcel.Workbooks.Open("C:\ErrorLog.xls")
objExcel.Cells(NEXTOPENROW, R) = "Null Value" <==========THIS IS
WHERE I NEED HELP========
End If

I put NEXTOPENROW in the above script to let you know that's where i
need help with, that's not part of my script, I have no idea how to
find the next open row....any help is appriciated!!!!!

GBPackerBacker schrieb:[...]
Try something like:

Const xlLastCell = 11
... = <WorkSheet>.UsedRange.SpecialCells( xlLastCell )

"GBPackerBacker" <gbpackerbacker@xxxxxx> wrote in message
news:2699deec-49bc-44c3-a02b-c30f0a99029c@xxxxxxI faced the same problem several months ago. With a fair bit of googling I
found this solution:
Function LastRow
Const xlCellTypeLastCell = 11
oSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Activate
LastRow = oExcel.ActiveCell.Row
End LastRow

On Mar 31, 1:56*pm, "ekkehard.horner" <ekkehard.hor...@xxxxxx>
wrote:
Thanks for the info, I'll see if I can get it to work, in the
meantime, can you explain a little more? What is the significance of
xlLastCell = 11. What does 11 mean? Also, once I find the cell, how
do I set that row number as a variable? For instance, once the cell
is found, I want to input a number into the cell, but also use that
same row to enter in information in a couple different columns....
Example below...


else
objExcel.Visible = False
objExcel.Workbooks.Open("C:\ErrorLog.xls")
varRow = <WorkSheet>.UsedRange.SpecialCells( xlLastCell )
objExcel.Cells(VarRow, R) = "Null Value"
objExcel.Cells(VarRow, S) = "Today's Date"
IdNum = (contentsofpreviouscell) +1
objExcel.Cells(VarRow, A) = IdNum
End If

Okay Pegasus, I can get your script to work great on a test excel
spreadsheet, but when I plug it into my master script (snippet below)
it errors out with the error: "Activate method of Range class failed"
Code: 800A03EC. Any ideas?

else
objExcel.Visible = False
objExcel.Workbooks.Open("C:\test.xls")
Const xlCellTypeLastCell = 11
objSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Activate
LastRow = objExcel.ActiveCell.Row
FirstOpenRow = LastRow + 1
objExcel.Cells(FirstOpenRow, B) = strComputer
objExcel.Cells(FirstOpenRow, G) = "NULL VALUE for MAC Address, in
row " & intRow & ", Column K"
End If

"GBPackerBacker" <gbpackerbacker@xxxxxx> wrote in message
news:0509e9d6-b01a-4107-82a2-6b1943e7ede8@xxxxxxCan't tell - you're not showing us your definition of objSheet. It should be
something like
Set objSheet = objWorkbook.ActiveSheet

and objWorkbook could be something like

Set objWorkbook = objExcel.Workbooks.Open("d:\temp.xls")

Try experimenting a little - it's very productive!

GBPackerBacker schrieb:[...]

The Const xlLastCell must be defined, because VBScript doesn't know
about this Excel secrets.

This sample code:

Const xlLastCell = 11

Dim oFS : Set oFS = CreateObject( "Scripting.FileSystemObject" )
Dim sFSpec : sFSpec = oFS.GetAbsolutePathName( <your.xls> )

Dim oExcel : Set oExcel = CreateObject( "Excel.Application" )
Dim oWBook : Set oWBook = oExcel.Workbooks.Open( sFSpec )
Dim oSheet : Set oSheet = oWBook.Sheets( <yoursheet> )
Dim oLCell : Set oLCell = oSheet.UsedRange.SpecialCells( xlLastCell )

WScript.Echo oSheet.Cells( oLCell.Row, oLCell.Column )
oSheet.Cells( oLCell.Row + 1, oLCell.Column ) = Now

oWBook.Save
oWBook.Close
oExcel.Quit

appends the current date/time to the sheet.

Pegasus [MVP] schrieb:In general, I'd try to avoid Activ* when automating Excel from
VBScript, because it is tied to interactive use.