writing to excel thru vb

[lunarpc]

Hi,
I'm really new to vbscript. I've cobbled to gether a script that will read and excel file that has IP addresses in it. The script will ping the IP address, get the computer name, then get the computer description from Active Directory. It then writes the computer name and description to the excel file. For some reason it does not write the name and description of the first IP Address. I tried different IP Addresses, but it is always the first one. Some how I wrote a script that says 'please make the first entry blank'. So any help his appreciated.

Here's the script:

Const ADS_SCOPE_SUBTREE = 2
Const ForReading = 1

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE

strFile = "C:\admin\new Ping\names.xls"
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks. Open strFile
Set objSheet = objExcel.ActiveWorkbook.WorkSheets(1)

intRow = 2

'Do Until objFile.AtEndOfStream
Do While objExcel.Cells(intRow, 1).Value <> ""

strHost = objExcel.Cells(intRow, 2).Value

if Ping(strHost) = True then
strComputer = GetNbName(strRName)
strDescr = GetComputerUser(strComputer)

Else
strComputer = "Not Found"
strDescr = ""

end if

objExcel.Cells(intRow, 5) = strComputer
objExcel.Cells(intRow, 6) = strDescr

intRow = intRow + 1
Loop
'Save, close, and exit.
objExcel.ActiveWorkbook.SaveAs strFile
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

wscript.echo "Finished"

Function Ping(strHost)

dim objPing, objRetStatus

set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery _
("select * from Win32_PingStatus where address = '" & strHost & "'" & " and resolveAddressNames = true")

for each objRetStatus in objPing
if IsNull(objRetStatus.StatusCode) or objRetStatus.StatusCode<>0 then
Ping = False
'WScript.Echo "Status code is " & objRetStatus.StatusCode
else
Ping = True
strRName = objRetStatus.ProtocolAddressResolved
'Wscript.echo "Computer Name = " & strRName
'Wscript.Echo "Bytes = " & vbTab & objRetStatus.BufferSize
'Wscript.Echo "Time (ms) = " & vbTab & objRetStatus.ResponseTime
'Wscript.Echo "TTL (s) = " & vbTab & objRetStatus.ResponseTimeToLive
end if
next

End Function


Function GetNbName(strName)

dim lngPeriod, strNewName

lngperiod = InStr(strName, ".")
IF lngPeriod > 0 Then
strNewName = Left(strName, lngPeriod - 1)
GetNbName = Trim(strNewName)
ELSE
GetNbName = strName
END IF

End Function

Function GetComputerUser(strComputerName)
If (strComputerName <> "") Then
objCommand.CommandText = "SELECT Name, description FROM 'LDAP://dc=Fakbrain,dc=com' " & _
"WHERE sAMAccountName = '" & strComputer & "$'"
Set objRecordset = objCommand.Execute

If objRecordset.RecordCount = 0 Then
strDescr = "does not exist."
Else
arrDescr = objRecordset.Fields("description").Value
If IsNull(arrDescr) Then
strDescr = ""
Else
For Each strItem in arrDescr
strDescr = strItem
Next
End If
GetComputerUser = strDescr
End If
END If
End Function

 

[PHV]

Replace this:
intRow = 2
with this:
intRow = 1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lunarpc]

Hi PH,
I tried replacing intRow =1. That replaced the header in column 5 with "Not Found" but the first IP Address is still blank. So it has something to with the rest of the code. I'm wondering if its not looping correctly?

 

[lunarpc]

I figured it out. I used the Option Explicit and declared all the variables and now it runs like it should.

 

[tsuji]

>I used the Option Explicit and declared all the variables and now it runs like it should.
If the script is becoming running like it should rather than like it should not before, you've to find the right reason. The reason you give is a doubtful excuse. If you think it is the rule to have a script running like it should, you are missing yourself.

 

[tsuji]

I meant "...you are [red]misleading[/red] yourself".