objCommand Error

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

objCommand Error

Logged in as: Guest

arrSession:exec spGetSession 2,2,61839

	Active Users: There are 0 members and 0 guests.
	Users viewing this topic: none

Printable Version

All Forums >> [Scripting] >> WSH & Client Side VBScript >> objCommand Error
Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!

Page: [1]

Message

<< Older Topic Newer Topic >>

objCommand Error - 6/25/2008 1:01:14 AM

Asurya

Posts: 4
Score: 0
Joined: 9/10/2007
Status: offline

Hi All

Still fairly new to VBScripting, but damm I love it, especially when I get an error and then get to find out why, but I'm afraid this one has got me. Searching and reading all over the net hasn't help and neither has trial and error. I reckon you clever people will get the answer in about 20 seconds (and recommend a better way to write this script) but I'll be happy to receive any help you would like to offer.

So, here is it, a script to search AD for the last time every user in 3 separate OU's logged on and dump it into Excel, once I get this part working at least I'm modify it to search all the other DC's and then compare times.

Anyways, getting an error on line 119, which is the line I have highlighted below, specifically: "Microsoft VBScript runtime error: Object required: 'objRecordSet.Fields(...).Value'" It's also worrth adding that it works perfectly fine for the 1st SubRoutine (AdminUsers), gets all the info it should and populates Excel.

Option Explicit

'***** Declare Variables
Dim objCommand, objConnection, objRecordSet, objExcel, objAdminSheet, objPowerSheet, objStdSheet, objRange, objlastLogon
Dim strExcelFilePath, strUserName, strLastLogon, strDate, strCN
Dim intRow, intLastLogonTime

'***** Declare Constants
Const ADS_SCOPE_SUBTREE = 2

'***** Open Excel and format Sheet to receive data
strExcelFilePath = "c:\lastlogon.xls"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
	Set objAdminSheet = objExcel.ActiveWorkbook.Worksheets(1)
	objAdminSheet.Name = "Admin Users"
	objAdminSheet.Cells(1,1).Value = "User"
	objAdminSheet.Cells(1,2).Value = "UserName"
	objAdminSheet.Cells(1,3).Value = "Last Logon Date & Time"
	objAdminSheet.Range("A1:C1").Font.Bold = True
	objAdminSheet.Range("A1:C1").Font.Underline = True
	
	Set objPowerSheet = objExcel.ActiveWorkbook.Worksheets(2)
	objPowerSheet.Name = "Power Users"
	objPowerSheet.Cells(1,1).Value = "User"
	objPowerSheet.Cells(1,2).Value = "UserName"
	objPowerSheet.Cells(1,3).Value = "Last Logon Date & Time"
	objPowerSheet.Range("A1:C1").Font.Bold = True
	objPowerSheet.Range("A1:C1").Font.Underline = True

Set objStdSheet = objExcel.ActiveWorkbook.Worksheets(3)
	objStdSheet.Name = "Standard Users"
	objStdSheet.Cells(1,1).Value = "User"
	objStdSheet.Cells(1,2).Value = "UserName"
	objStdSheet.Cells(1,3).Value = "Last Logon Date & Time"
	objStdSheet.Range("A1:C1").Font.Bold = True
	objStdSheet.Range("A1:C1").Font.Underline = True
	
	
	
AdminUsers()
PowerUsers()
StdUsers()

Sub AdminUsers
'***** Set Active Directory Search
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

'***** SQL Syntx for AD Search for Admin Users and populate Excel
objCommand.CommandText = _
    "SELECT sAMAccountName, lastLogon, cn FROM 'LDAP://OU=Admin Users,OU=User Accounts,DC=xxxx,DC=xxx,DC=xx,DC=xxx' WHERE objectCategory='user' ORDER BY employeeID"  
    	
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst

intRow = 2
Do Until objRecordSet.EOF
	strUserName = objRecordSet.Fields("sAMAccountName").Value
	strCN = objRecordSet.Fields("cn").Value
	Set objlastLogon = objRecordSet.Fields("lastLogon").Value
	
	intLastLogonTime = objLastLogon.HighPart * (2^32) + objLastLogon.LowPart 
	intLastLogonTime = intLastLogonTime / (60 * 10000000)
	intLastLogonTime = intLastLogonTime / 1440
	
	objAdminSheet.Cells(intRow, 1).Value = strCN
	objAdminSheet.Cells(intRow, 2).Value = strUserName
	objAdminSheet.Cells(intRow, 3).Value = intLastLogonTime + #1/1/1601#
	intRow = intRow + 1
	objRecordSet.MoveNext
Loop
End Sub

Sub PowerUsers()
'***** Set Active Directory Search
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

'***** SQL Syntx for AD Search for Power Users and populate Excel
objCommand.CommandText = _
    "SELECT sAMAccountName, lastLogon, cn FROM 'LDAP://OU=Power Users,OU=User Accounts,DC=xxxx,DC=xxxx,DC=xxx,DC=xxx' WHERE objectCategory='user' ORDER BY employeeID"  
    	
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst

intRow = 2
Do Until objRecordSet.EOF
	strUserName = objRecordSet.Fields("sAMAccountName").Value
	strCN = objRecordSet.Fields("cn").Value
	Set objlastLogon = objRecordSet.Fields("lastLogon").Value   <--------- This is line 119
	
	intLastLogonTime = objLastLogon.HighPart * (2^32) + objLastLogon.LowPart 
	intLastLogonTime = intLastLogonTime / (60 * 10000000)
	intLastLogonTime = intLastLogonTime / 1440
	
	objPowerSheet.Cells(intRow, 1).Value = strCN
	objPowerSheet.Cells(intRow, 2).Value = strUserName
	objPowerSheet.Cells(intRow, 3).Value = intLastLogonTime + #1/1/1601#
	intRow = intRow + 1
	objRecordSet.MoveNext
Loop
End Sub

Sub StdUsers()
'***** Set Active Directory Search
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

'***** SQL Syntx for AD Search for Std Users and populate Excel
objCommand.CommandText = _
    "SELECT sAMAccountName, lastLogon, cn FROM 'LDAP://OU=Standard Users,OU=User Accounts,DC=xxxxx,DC=xxxx,DC=xxx,DC=xxx' WHERE objectCategory='user' ORDER BY employeeID"  
    	
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst

intRow = 2
Do Until objRecordSet.EOF
	strUserName = objRecordSet.Fields("sAMAccountName").Value
	strCN = objRecordSet.Fields("cn").Value
	Set objlastLogon = objRecordSet.Fields("lastLogon").Value
	
	intLastLogonTime = objLastLogon.HighPart * (2^32) + objLastLogon.LowPart 
	intLastLogonTime = intLastLogonTime / (60 * 10000000)
	intLastLogonTime = intLastLogonTime / 1440
	
	objStdSheet.Cells(intRow, 1).Value = strCN
	objStdSheet.Cells(intRow, 2).Value = strUserName
	objStdSheet.Cells(intRow, 3).Value = intLastLogonTime + #1/1/1601#
	intRow = intRow + 1
	objRecordSet.MoveNext
Loop
End Sub

'***** Save and Close Excel
objExcel.ActiveWorkbook.SaveAs strExcelFilePath
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Echo "Done"
WScript.Quit

I'm sure it's probbaly something really easy for anyone who has some expeirence, but at the moment I just can't seem to find what I'm doing wrong.

Post #: 1

RE: objCommand Error - 6/25/2008 8:19:32 PM

Asurya

Posts: 4
Score: 0
Joined: 9/10/2007
Status: offline

No-one got a clue?

(in reply to Asurya)

Post #: 2

RE: objCommand Error - 6/26/2008 12:15:08 AM

dm_4ever

Posts: 2639
Score: 46
Joined: 6/29/2006
From: Orange County, California
Status: offline

Maybe that value is null for some account

If Not IsNull(objRecordSet.Fields("lastLogon").Value) Then
   Set objlastLogon = objRecordSet.Fields("lastLogon").Value

   intLastLogonTime = objLastLogon.HighPart * (2^32) + objLastLogon.LowPart
   intLastLogonTime = intLastLogonTime / (60 * 10000000)
   intLastLogonTime = intLastLogonTime / 1440
End IF

_____________________________

dm_4ever

My philosophy: K.I.S.S - Keep It Simple Stupid
Read Me: http://www.visualbasicscript.com/m_24727/tm.htm
Frequently Asked Stuff: http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to Asurya)