Retrieving Computer object information from AD

[stephenslong]

Hello

I have two functions in excel 2010. I found "ComputerDescription" online and it works fine. I attempted to copy/modify it to retrieve more information but my modified ("operatingSystem") version doesnt work. I'm not a complete novice but not an expert either. I feel like I'm just missing something. Can someone help please?

Function ComputerDescription(ComputerName) As String
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") = 2
objCommand.CommandText = "SELECT description FROM 'LDAP://ou=R_Shared Services,dc=nortrax-inc,dc=com' WHERE name = '" & ComputerName & "' AND objectClass = 'computer'"
Set objRecordSet = objCommand.Execute
If objRecordSet.RecordCount < 1 Then
ComputerDescription = "No results found"
End If
objRecordSet.MoveFirst
Set myVal = objRecordSet.Fields("description")
Dim desc
For Each desc In myVal.Value
ComputerDescription = desc
Next
objRecordSet.Close
objConnection.Close
End Function

Function operatingSystem(ComputerName) As String
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") = 2
objCommand.CommandText = "SELECT operatingSystem FROM 'LDAP://ou=R_Shared Services,dc=nortrax-inc,dc=com' WHERE name = '" & ComputerName & "' AND objectClass = 'computer'"
Set objRecordSet = objCommand.Execute
If objRecordSet.RecordCount < 1 Then
operatingSystem = "No results found"
End If
objRecordSet.MoveFirst
Set myVal = objRecordSet.Fields("operatingSystem")
Dim OS
For Each OS In myVal.Value
operatingSystem = OS
Next
objRecordSet.Close
objConnection.Close
End Function

[Simon Lloyd]

I dont know what you're attempting to do but it seems like overkill for the followingSub Environ_Vars()
' Get Environmental Variables using VBA
' Get the LOGON SERVER
sLOGONSERVER = Environ("LOGONSERVER")
' No of processors using VBA
sNUMBER_OF_PROCESSORS = Environ("NUMBER_OF_PROCESSORS")
' Get the Operating System using VBA
sOS = Environ("OS")
' Get the USER DOMAIN using VBA
sUSERDOMAIN = Environ("USERDOMAIN")
' Get the Windows Directory using VBA
swindir = Environ("windir")
'Get real logon Username using VBA
sUSERNAME = Environ("username")
'Get Network Computer name
sCOMPNAME = Environ("computername")
Range("A2") = sLOGONSERVER
Range("B2") = sNUMBER_OF_PROCESSORS
Range("C2") = sOS
Range("D2") = sUSERDOMAIN
Range("E2") = swindir
Range("F2") = sUSERNAME
Range("G2") = sCOMPNAME
End Sub