vbscript to open worksheet, insert data on first opencell....HELP.....
 

Hi all, I have a script that goes out and searches a spreadsheet for
information. If it finds a null value or errors out, I need my script
to open up
another logging spreadsheet and look into the last cell of Column A,
read what the value is and insert the same value plus 1 (Column A is
and ID #) and then enter the error text into column B.

I have scripted inserted columns, values into cells and such, but I
have no idea how to go about finding the last cell, adding 1 to it in
the next row, then adding text to the new row column B.


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


IntRow = 9
A = 1
K = 11
R = 18


if objExcel.Cells(intRow, K).Value < "VALIDTEXT" then
objExcel.Cells(intRow, R).Interior.ColorIndex = 4
else
objExcel.Visible = False
objExcel.Workbooks.Open("C:\ErrorLog.xls")
objExcel.Cells(A,FIRSTOPENCELL) = (LASTUSEDCELL+1) <=THIS IS
WHERE I NEED HELP=
objExcel.Cells(B,FIRSTOPENCELL) = "ERRORTEXT"
End If

So basically, I'm opening a spreadsheet, looking in Column A for the
last value, adding 1 to that value and entering that on the next
available row, then on that same open row, I want to also place a text
value in Column B)

vbscript to open worksheet, insert data on first open cell....HELP
 

Set objExcel = CreateObject("Excel.Application")
Set objSheet = objExcel.Workbooks.Open("C:\Excelfilename.xls")


intRow = 9
A = 1
K = 11
R = 18

With objExcel
If .Cells(intRow, K).Value < "VALIDTEXT" Then
.Cells(intRow, R).Interior.ColorIndex = 4
Else
.Visible = False
Set newbk = .Workbooks.Open("C:\ErrorLog.xls")
LastRow = newbk.ActiveSheet.Range("A" & Rows.Count).End(xlUp) + 1
NewRow = LastRow + 1
.Cells(A, FIRSTOPENCELL) = newbk.ActiveSheet.Range("A" & NewRow) + 1
.Cells(B, FIRSTOPENCELL) = "ERRORTEXT"
End If
End With
"GBPackerBacker" wrote:

Hi all, I have a script that goes out and searches a spreadsheet for
information. If it finds a null value or errors out, I need my script
to open up
another logging spreadsheet and look into the last cell of Column A,
read what the value is and insert the same value plus 1 (Column A is
and ID #) and then enter the error text into column B.

I have scripted inserted columns, values into cells and such, but I
have no idea how to go about finding the last cell, adding 1 to it in
the next row, then adding text to the new row column B.


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


IntRow = 9
A = 1
K = 11
R = 18


if objExcel.Cells(intRow, K).Value < "VALIDTEXT" then
objExcel.Cells(intRow, R).Interior.ColorIndex = 4
else
objExcel.Visible = False
objExcel.Workbooks.Open("C:\ErrorLog.xls")
objExcel.Cells(A,FIRSTOPENCELL) = (LASTUSEDCELL+1) <=THIS IS
WHERE I NEED HELP=
objExcel.Cells(B,FIRSTOPENCELL) = "ERRORTEXT"
End If

So basically, I'm opening a spreadsheet, looking in Column A for the
last value, adding 1 to that value and entering that on the next
available row, then on that same open row, I want to also place a text
value in Column B)

vbscript to open worksheet, insert data on first open cell....HELP.....
 

GBPackerBacker wrote:
.. look into the last cell of Column A

I think you want:
Dim iRow
To get the row of the last used cell in column A:
iRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
To get the row of the last used cell in the worksheet:
iRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row
and you can add 1 to get the next row.