I wrote a vbScript to gather computer information for each user's computer on login, and Id like to have the script record the information to an Excel sheet on the server, with each computer having its own row.
I wrote this like 6 years ago but lost the script and havent touched vbScript since.
So what I need to do is,
- check all the cells in column B (which would be computer name) that have a value
- compare that value to value saved for the computer's name
- if it matches, write the computer info to that row
- if there are no matches, then write the info to the first empty row
I have no idea where to start since vbScript is pretty foreign to me.
Edit – I have this loop so far and that echo to test it, but it only goes to 1, while I have like 6 rows with values in column 0. I tried that conditional to check the cell value for a value I know exists and I get a runtime error.
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("test.xlsx")
Do While objExcel.ActiveCell.Offset(iRow,0).Value<>""
WScript.Echo iRow
If objExcel.Cells(iRow,0).Value = "three" Then
WScript.Echo "three"
End If
iRow = iRow+1
Loop
WScript.Quit
Best Answer
You most likely have an
On Error Resume Next
somewhere in your script, which makes the code fail silently.As far as I can tell from your code this is probably what's happening:
Do While objExcel.ActiveCell.Offset(iRow,0).Value<>""
The script checks if the cell at the offset
(iRow,0)
of the active cell is empty and if it isn't goes into the loop.WScript.Echo iRow
The script echoes the current value of
iRow
(probably 0).If objExcel.Cells(iRow,0).Value = "three" Then
The script tries to access the cell
(iRow,0)
(not the cell at the offset(iRow,0)
), which fails, becauseCells()
is not zero-based. The first row as well as the first column have the index 1 in theCells()
property. Not to mention that withCells()
you have to use the absolute row and column numbers, not the ones relative toActiveCell
as inOffset()
.To fix the issue I'd remove
On Error Resume Next
and simply useCells()
instead ofActiveCell.Offset()
: