VB script / Excel question

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

VB script / Excel question

Logged in as: Guest

arrSession:exec spGetSession 2,2,66724

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

Printable Version

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

Page: [1]

Message

<< Older Topic Newer Topic >>

VB script / Excel question - 11/20/2008 6:02:49 AM

Edorian

Posts: 16
Score: 0
Joined: 11/17/2008
Status: offline

Hello all,

Ive been working on a script and finally have one that actually works and does what I want it to do....mostly.

The problem : We need to be able to query active directory. I need to enter a login name as input, and have various fields returned on that user to a text file.

This script I have currently queries AD when run, and copies all of the fields I need to an excel spreadsheet, but it does so for EVERY user in AD. I would like to be able to target a user and get this information, rather than get it for every user.

My newest approach involved a script or method to parse through the excel file that gets created here, and find the info I need. This seems horrendously inefficient...it would be so much better if I had 1 script that just pulled the info from AD that we need.

Here is the script I have:

Const ADS_SCOPE_SUBTREE = 2
dtmDate = Date
strMonth = Month(Date)
strDay = Day(Date)
strYear = Right(Year(Date),2)
strFileName = "D:\Scripts\" & strMonth & "-" & strDay & "-" & strYear & ".xlsx"

Set objExcel = CreateObject("Excel.Application")

'objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Last name"
objExcel.Cells(1, 2).Value = "First name"
objExcel.Cells(1, 3).Value = "Display name"
objExcel.Cells(1, 4).Value = "Employee ID"
objExcel.Cells(1, 5).Value = "Employee Type"
objExcel.Cells(1, 6).Value = "Employee Number"
objExcel.Cells(1, 7).Value = "Login Name"
objExcel.Cells(1, 8).Value = "Phone number"
objExcel.Cells(1, 9).Value = "Street Address"
objExcel.Cells(1, 10).Value = "City"
objExcel.Cells(1, 11).Value = "State"
objExcel.Cells(1, 12).Value = "Postal Code"
objExcel.Cells(1, 13).Value = "Description"
objExcel.Cells(1, 14).Value = "Department"
objExcel.Cells(1, 15).Value = "Create Time Stamp"
objExcel.Cells(1, 16).Value = "Modify Time Stamp"
objExcel.Cells(1, 17).Value = "Home Directory"
objExcel.Cells(1, 18).Value = "Profile Path"
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
objCommand.CommandText = _
   "SELECT givenName, SN, Displayname, EmployeeID, EmployeeType, EmployeeNumber, sAMAccountNAme, telephonenumber, streetaddress, l, st, postalcode, Description, Department, CreateTimeStamp, ModifyTimeStamp, homeDirectory, ProfilePath FROM " _
       & "'LDAP://dc=CSC,dc=com' WHERE " _
           & "objectCategory='user'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
x = 2
Do Until objRecordSet.EOF
   objExcel.Cells(x, 1).Value = _
       objRecordSet.Fields("givenName").Value
   objExcel.Cells(x, 2).Value = _
       objRecordSet.Fields("SN").Value
   objExcel.Cells(x, 3).Value = _
       objRecordSet.Fields("Displayname").Value
   objExcel.Cells(x, 4).Value = _
      objRecordSet.Fields("EmployeeID").Value
   objExcel.Cells(x, 5).Value = _
       objRecordSet.Fields("EmployeeType").Value
   objExcel.Cells(x, 6).Value = _
       objRecordSet.Fields("EmployeeNumber").Value
   objExcel.Cells(x, 7).Value = _
       objRecordSet.Fields("SamAccountname").Value
   objExcel.Cells(x, 8).Value = _
       objRecordSet.Fields("telephonenumber").Value
   objExcel.Cells(x, 9).Value = _
       objRecordSet.Fields("streetaddress").Value
   objExcel.Cells(x, 10).Value = _
       objRecordSet.Fields("l").Value
   objExcel.Cells(x, 11).Value = _
       objRecordSet.Fields("st").Value
   objExcel.Cells(x, 12).Value = _
       objRecordSet.Fields("Postalcode").Value
   objExcel.Cells(x, 13).Value = _
       objRecordSet.Fields("Description").Value
   objExcel.Cells(x, 14).Value = _
       objRecordSet.Fields("Department").Value
   objExcel.Cells(x, 15).Value = _
       objRecordSet.Fields("CreateTimeStamp").Value
   objExcel.Cells(x, 16).Value = _
       objRecordSet.Fields("ModifyTimeStamp").Value
   objExcel.Cells(x, 17).Value = _
       objRecordSet.Fields("homeDirectory").Value
   objExcel.Cells(x, 18).Value = _
       objRecordSet.Fields("ProfilePath").Value
   x = x + 1
   objRecordSet.MoveNext
Loop

Set objRange = objExcel.Range("A1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("C1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("D1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("E1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("F1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("G1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("H1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("I1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("J1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("K1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("L1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
' Set objRange = objExcel.Range("M1")
' objRange.Activate
' Set objRange = objExcel.ActiveCell.EntireColumn
' objRange.Autofit()
Set objRange = objExcel.Range("N1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("O1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("P1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("Q1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("A1").SpecialCells(11)
Set objRange2 = objExcel.Range("C1")
Set objRange3 = objExcel.Range("A1")

Set objWorkbook = objExcel.Workbooks.Add()
objWorkbook.SaveAs(strFileName)
objworkbook.close(strFilename)

Can anyone show me a cleaner method, maybe so it asks for user in inputbox, then just searchs for that user and writes the results to a spreadsheet?

Post #: 1

RE: VB script / Excel question - 11/20/2008 6:23:55 AM

ebgreen

Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline

Search for InputBox to see about getting a user ID from the user. As for limiting your search, add the ID requirement to the WHERE clause of your query. This question almost exactly was answered at least once in the last week.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to Edorian)

Post #: 2

RE: VB script / Excel question - 11/20/2008 6:37:29 AM

Edorian

Posts: 16
Score: 0
Joined: 11/17/2008
Status: offline

Again, thanks.

I was the one who asked the question this week, Im still having problems.

I modified the script to what you suggested, here it is:

Const ADS_SCOPE_SUBTREE = 2
dtmDate = Date
strMonth = Month(Date)
strDay = Day(Date)
strYear = Right(Year(Date),2)
strFileName = "D:\Scripts\" & strMonth & "-" & strDay & "-" & strYear & ".xlsx"

Set objExcel = CreateObject("Excel.Application")
strName = InputBox("Enter UserID")
'objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Last name"
objExcel.Cells(1, 2).Value = "First name"
objExcel.Cells(1, 3).Value = "Display name"
objExcel.Cells(1, 4).Value = "Employee ID"
objExcel.Cells(1, 5).Value = "Employee Type"
objExcel.Cells(1, 6).Value = "Employee Number"
objExcel.Cells(1, 7).Value = "Login Name"
objExcel.Cells(1, 8).Value = "Phone number"
objExcel.Cells(1, 9).Value = "Street Address"
objExcel.Cells(1, 10).Value = "City"
objExcel.Cells(1, 11).Value = "State"
objExcel.Cells(1, 12).Value = "Postal Code"
objExcel.Cells(1, 13).Value = "Description"
objExcel.Cells(1, 14).Value = "Department"
objExcel.Cells(1, 15).Value = "Create Time Stamp"
objExcel.Cells(1, 16).Value = "Modify Time Stamp"
objExcel.Cells(1, 17).Value = "Home Directory"
objExcel.Cells(1, 18).Value = "Profile Path"
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
objCommand.CommandText = _
"SELECT givenName, SN, Displayname, EmployeeID, EmployeeType, EmployeeNumber, sAMAccountNAme, telephonenumber, streetaddress, l, st, postalcode, Description, Department, CreateTimeStamp, ModifyTimeStamp, homeDirectory, ProfilePath FROM " _
      & "'LDAP://dc=rphi,dc=com' WHERE " _
          & "objectCategory='user' AND employeeid='" & strName & "'"
Set objRecordSet = objCommand.Execute
'objRecordSet.MoveFirst
x = 2
Do Until objRecordSet.EOF
objExcel.Cells(x, 1).Value = _
      objRecordSet.Fields("givenName").Value
objExcel.Cells(x, 2).Value = _
      objRecordSet.Fields("SN").Value
objExcel.Cells(x, 3).Value = _
      objRecordSet.Fields("Displayname").Value
objExcel.Cells(x, 4).Value = _
     objRecordSet.Fields("EmployeeID").Value
objExcel.Cells(x, 5).Value = _
      objRecordSet.Fields("EmployeeType").Value
objExcel.Cells(x, 6).Value = _
      objRecordSet.Fields("EmployeeNumber").Value
objExcel.Cells(x, 7).Value = _
      objRecordSet.Fields("SamAccountname").Value
objExcel.Cells(x, 8).Value = _
      objRecordSet.Fields("telephonenumber").Value
objExcel.Cells(x, 9).Value = _
      objRecordSet.Fields("streetaddress").Value
objExcel.Cells(x, 10).Value = _
      objRecordSet.Fields("l").Value
objExcel.Cells(x, 11).Value = _
      objRecordSet.Fields("st").Value
objExcel.Cells(x, 12).Value = _
      objRecordSet.Fields("Postalcode").Value
objExcel.Cells(x, 13).Value = _
      objRecordSet.Fields("Description").Value
objExcel.Cells(x, 14).Value = _
      objRecordSet.Fields("Department").Value
objExcel.Cells(x, 15).Value = _
      objRecordSet.Fields("CreateTimeStamp").Value
objExcel.Cells(x, 16).Value = _
      objRecordSet.Fields("ModifyTimeStamp").Value
objExcel.Cells(x, 17).Value = _
      objRecordSet.Fields("homeDirectory").Value
objExcel.Cells(x, 18).Value = _
      objRecordSet.Fields("ProfilePath").Value
x = x + 1
objRecordSet.MoveNext
Loop

Set objRange = objExcel.Range("A1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
'Set objRange = objExcel.Range("C1")
'objRange.Activate
'Set objRange = objExcel.ActiveCell.EntireColumn
'objRange.Autofit()
Set objRange = objExcel.Range("D1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("E1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("F1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("G1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("H1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("I1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("J1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("K1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("L1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
' Set objRange = objExcel.Range("M1")
' objRange.Activate
' Set objRange = objExcel.ActiveCell.EntireColumn
' objRange.Autofit()
Set objRange = objExcel.Range("N1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("O1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("P1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("Q1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("A1").SpecialCells(11)
Set objRange2 = objExcel.Range("C1")
Set objRange3 = objExcel.Range("A1")
Set objWorkbook = objExcel.Workbooks.Add()
objWorkbook.SaveAs(strFileName)
objworkbook.close(strFilename)

When I run the script now though, nothing gets returned. The file gets created, but its blank, and I know the records Im searching for exist. I also had to REM out this line after I made those changes or the script just choked:

'objRecordSet.MoveFirst

One other issue - The file that gets created is named after the current date - 11-20-08.xlsx When I open that file it opens a blank workbook though and the data I need is being saved as Book4.xls., like a seperate worksheet. Anyway I can have it saved directly to the xls spreadsheet rather than saved as a workbook?

Any ideas?

< Message edited by Edorian -- 11/20/2008 6:49:25 AM >

(in reply to ebgreen)

Post #: 3

RE: VB script / Excel question - 11/20/2008 6:58:02 AM

ebgreen

Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline

Here is sample code that works for gettinformation about one specific user:

On Error Resume Next
Set objUser = GetObject _
("LDAP://cn=myerken,ou=management,dc=fabrikam,dc=com")
objUser.GetInfo

strProfilePath = objUser.Get("profilePath")
strScriptPath = objUser.Get("scriptPath")
strHomeDirectory = objUser.Get("homeDirectory")
strHomeDrive = objUser.Get("homeDrive")

WScript.echo "profilePath: " & strProfilePath
WScript.echo "scriptPath: " & strScriptPath
WScript.echo "homeDirectory: " & strHomeDirectory
WScript.echo "homeDrive: " & strHomeDrive

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to Edorian)

Post #: 4

RE: VB script / Excel question - 11/20/2008 7:12:54 AM

Edorian

Posts: 16
Score: 0
Joined: 11/17/2008
Status: offline

ok lol Im not getting very far :(

How can that code be modified to ASK for the users info, rather than having to have it coded into the script? Something like this, but this doesnt work..

On Error Resume Next
strName = InputBox("Enter UserID")
Set objUser = GetObject _
("LDAP://cn='strName',dc=fabrikam,dc=com")
objUser.GetInfo

strProfilePath = objUser.Get("profilePath")
strScriptPath = objUser.Get("scriptPath")
strHomeDirectory = objUser.Get("homeDirectory")
strHomeDrive = objUser.Get("homeDrive")

WScript.echo "profilePath: " & strProfilePath
WScript.echo "scriptPath: " & strScriptPath
WScript.echo "homeDirectory: " & strHomeDirectory
WScript.echo "homeDrive: " & strHomeDrive

I know that wont work, but thats the idea I think of what Im trying to do.

(in reply to ebgreen)

Post #: 5

RE: VB script / Excel question - 11/20/2008 7:18:00 AM

ebgreen

Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline

Look at the way that you altered your previous code to include the user ID. Understand what every single character in this line does and why it is there:

objCommand.CommandText = _
"SELECT givenName, SN, Displayname, EmployeeID, EmployeeType, EmployeeNumber, sAMAccountNAme, telephonenumber, streetaddress, l, st, postalcode, Description, Department, CreateTimeStamp, ModifyTimeStamp, homeDirectory, ProfilePath FROM " _
& "'LDAP://dc=rphi,dc=com' WHERE " _
& "objectCategory='user' AND employeeid='" & strName & "'"

Once you understand that, apply it to this line:

Set objUser = GetObject _
("LDAP://cn='strName',dc=fabrikam,dc=com")

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to Edorian)

Post #: 6

RE: VB script / Excel question - 11/20/2008 7:49:49 AM

Edorian

Posts: 16
Score: 0
Joined: 11/17/2008
Status: offline

Here is what I tried:

On Error Resume Next

strName = InputBox("Enter UserID")

Set objUser = GetObject _
& "SELECT givenName, SN, Displayname, EmployeeID, EmployeeType, EmployeeNumber, sAMAccountNAme, telephonenumber, streetaddress, l, st, postalcode, Description, Department, CreateTimeStamp, ModifyTimeStamp, homeDirectory, ProfilePath FROM " _
& "'LDAP://dc=rphi,dc=com' WHERE " _
& "objectCategory='user' AND employeeid='" & strName & "'"

objUser.GetInfo

strdisplayname = objuser.Get("DisplayName")
strProfilePath = objUser.Get("profilePath")
strScriptPath = objUser.Get("scriptPath")
strHomeDirectory = objUser.Get("homeDirectory")
strHomeDrive = objUser.Get("homeDrive")

wscript.echo "Display Name: " & strDisplayname
WScript.echo "profilePath: " & strProfilePath
WScript.echo "scriptPath: " & strScriptPath
WScript.echo "homeDirectory: " & strHomeDirectory
WScript.echo "homeDrive: " & strHomeDrive

I dont get an error, but nothing gets echoed, it just has blank boxes with labels where the data should be.

I think I understand that entire line, except maybe the first command, objCommand.CommandText=...

The SELECT statement specifies which attributes I want to get.
The LDAP part is saying where to start my search, in the root of rphi
The last line is saying the object category to search is user and employeeid field should = the name we asked for in the input box.

(in reply to ebgreen)

Post #: 7

RE: VB script / Excel question - 11/20/2008 7:55:22 AM

TNO

Posts: 1402
Score: 16
Joined: 12/18/2004
From: thenewobjective.com
Status: offline

On Error Resume Next will hide any errors. Remove it to see what crops up

_____________________________

To iterate is human, to recurse divine. -- L. Peter Deutsch

(in reply to Edorian)

Post #: 8

RE: VB script / Excel question - 11/20/2008 8:03:38 AM

Edorian

Posts: 16
Score: 0
Joined: 11/17/2008
Status: offline

I removed that On Error Resume Next....while Im learning that line is out the window from now on hehe.

After removing the line Im getting an error saying:

wrong number of arguments or invalid propery assignment: 'GetObject'

I also tried changing the code so the attributes match up like this:

strName = InputBox("Enter UserID")

Set objUser = GetObject _
& "SELECT givenName, SN, Displayname, EmployeeID, EmployeeType FROM" _
& "'LDAP://dc=rphi,dc=com' WHERE objectCategory='user' AND employeeid='" & strName & "'"

objUser.GetInfo

strdisplayname = objuser.Get("givenName")
strProfilePath = objUser.Get("SN")
strScriptPath = objUser.Get("Displayname")
strHomeDirectory = objUser.Get("EmployeeID")
strHomeDrive = objUser.Get("EmployeeType")

wscript.echo "Display Name: " & strDisplayname
WScript.echo "profilePath: " & strProfilePath
WScript.echo "scriptPath: " & strScriptPath
WScript.echo "homeDirectory: " & strHomeDirectory
WScript.echo "homeDrive: " & strHomeDrive

Getting the same error though.

< Message edited by Edorian -- 11/20/2008 8:23:20 AM >

(in reply to TNO)

Post #: 9

RE: VB script / Excel question - 11/20/2008 8:36:02 AM

Edorian

Posts: 16
Score: 0
Joined: 11/17/2008
Status: offline

Can this problem be that Im trying to set an objUser, but in the LDAP provider path Im not giving the path to any users? Is there another method that I should be using or something?

(in reply to Edorian)

Post #: 10

RE: VB script / Excel question - 11/20/2008 8:54:18 AM

ebgreen

Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline

Use this:

Set objUser = GetObject _
("LDAP://cn='strName',dc=fabrikam,dc=com")

Then look at this to see how you incorporate a variable into a string:

objCommand.CommandText = _
"SELECT givenName, SN, Displayname, EmployeeID, EmployeeType, EmployeeNumber, sAMAccountNAme, telephonenumber, streetaddress, l, st, postalcode, Description, Department, CreateTimeStamp, ModifyTimeStamp, homeDirectory, ProfilePath FROM " _
& "'LDAP://dc=rphi,dc=com' WHERE " _
& "objectCategory='user' AND employeeid='" & strName & "'"

Use your new found knowledge about using variables in strings to modify the first line that I said to use:

Set objUser = GetObject _
("LDAP://cn=" & strName & ",dc=fabrikam,dc=com")

You will also have to change the DCs and/or OUs in the string to be right for your environment.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to Edorian)

Post #: 11

RE: VB script / Excel question - 11/21/2008 3:35:48 AM

Edorian

Posts: 16
Score: 0
Joined: 11/17/2008
Status: offline

In the hopes of learning this, Im just trying to get that piece of code you posted to work, and then Ill try and apply it to my needs. This is what I tried:

Const ADS_SCOPE_SUBTREE = 2
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

strName = InputBox("Enter UserID")

Set objUser = GetObject _
& "SELECT givenName, SN, Displayname, EmployeeID, EmployeeType FROM" _
& "'LDAP://cn=" & strName & ", dc=csc,dc=com' WHERE objectCategory='user'"

objUser.GetInfo
strdisplayname = objuser.Get("givenName")
strProfilePath = objUser.Get("SN")
strScriptPath = objUser.Get("Displayname")
strHomeDirectory = objUser.Get("EmployeeID")
strHomeDrive = objUser.Get("EmployeeType")

wscript.echo "Display Name: " & strDisplayname
WScript.echo "profilePath: " & strProfilePath
WScript.echo "scriptPath: " & strScriptPath
WScript.echo "homeDirectory: " & strHomeDirectory
WScript.echo "homeDrive: " & strHomeDrive

I also tried with AND employeeid='" & strName & "'" appended to the LDAP query line.
I get an error on line 14 char 1 stating "Wrong number of arguments or invalid proprty assignment 'Getobject'

(in reply to ebgreen)

Post #: 12

RE: VB script / Excel question - 11/21/2008 3:49:27 AM

Edorian

Posts: 16
Score: 0
Joined: 11/17/2008
Status: offline

And here is my latest rendition:

Const ADS_SCOPE_SUBTREE = 2
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

strName = InputBox("Enter UserID")
wscript.echo "Login name: " & strName

Set objUser = GetObject _
& "SELECT givenName, SN, Displayname, EmployeeID, EmployeeType FROM" _
& "'LDAP://cn=" & strName & ", dc=cablevision,dc=com' WHERE objectCategory='user'"
objUser.GetInfo
strgivenname = objuser.Get("givenName")
strsn = objUser.Get("SN")
strdisplayname = objUser.Get("Displayname")
stremployeeID = objUser.Get("EmployeeID")
stremployeetype = objUser.Get("EmployeeType")

wscript.echo "Given Name: " & strgivenname
WScript.echo "SN: " & strsn
WScript.echo "Display Name: " & strdisplayname
WScript.echo "Employeeid: " & stremployeeID
WScript.echo "Employee Type: " & stremployeetype

I tried having all of the attributes match, but I get the same error. I also made sure that the strName is working, after I input the name a box appears echoing my name. Then I get the error after that.

Its a runtime error I get, so I guess its not a syntax problem, more like a logic problem, some sort of name/attribute mismatch or something?

I think I discovered something, I ran the ADSI edit tool from the DC, and pulled up a user accounts properties. The ObjectCategory attributes syntax says it should be in distinguished name form. My code has objectcategory=user. Can that be the problem? and if it is, I STILL dont see how I can just get the user I inputs information. Whats the DN of the root of the domain so everything gets searched?

< Message edited by Edorian -- 11/21/2008 4:07:28 AM >

(in reply to Edorian)

Post #: 13

RE: VB script / Excel question - 11/21/2008 5:50:53 AM

ebgreen

Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline

GetObject is not a query. Look back at the very first place that I posted that get object code. There is no SELECT statement and no WHERE statement. You simply give it a string that descibes the AD object that you want.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to Edorian)

Post #: 14

RE: VB script / Excel question - 11/21/2008 6:11:11 AM

Edorian

Posts: 16
Score: 0
Joined: 11/17/2008
Status: offline

Ok Im starting to see.

I tried this:

Const ADS_SCOPE_SUBTREE = 2
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
strName = InputBox("Enter UserID")
wscript.echo "Login name: " & strName
Set objUser = GetObject ("LDAP://cn=" & strName & ",ou=BDC Admins, dc=csc, dc=com")
objUser.GetInfo
strgivenname = objuser.Get("givenName")
strsn = objUser.Get("SN")
strdisplayname = objUser.Get("Displayname")
'stremployeeID = objUser.Get("EmployeeID")
'stremployeetype = objUser.Get("EmployeeType")
wscript.echo "Given Name: " & strgivenname
WScript.echo "SN: " & strsn
WScript.echo "Display Name: " & strdisplayname
'WScript.echo "Employeeid: " & stremployeeID
'WScript.echo "Employee Type: " & stremployeetype

But here is the deal....maybe Im not explaining myself quite right. This script above works, horray! BUT - see how I had to put ou=BDC Admins in the LDAP query? If I enter a name that is actually in the BDC Admins container, it reurns and all is good. But what if I have a user and I dont know what container they are in? Thats what I need, to be able to search all containers for the user, not just the OU i have coded there. When I do that with this script it errors out and says it cannot find the object. I need a mechanism that traverses all of AD.

Does that even make sense?

(in reply to ebgreen)

Post #: 15

RE: VB script / Excel question - 11/21/2008 7:30:05 AM

ebgreen

Posts: 5251
Score: 31
Joined: 7/12/2005
Status: offline

Research querying the global catalog.

_____________________________

"... when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick
Goog places to start:http://www.visualbasicscript.com/m_24727/tm.htm
http://www.visualbasicscript.com/m_47117/tm.htm

(in reply to Edorian)

Post #: 16

RE: VB script / Excel question - 11/24/2008 7:35:24 AM

Edorian

Posts: 16
Score: 0
Joined: 11/17/2008
Status: offline

I thought the GC was used if you need to search an entire forest, multiple domains?

All of the info Im looking for is within our domain.

Or were you suggesting something like this:

Set objUser = GetObject ("GC://cn=" & strName & ",ou=BDC Admins, dc=csc, dc=com") ?

(in reply to ebgreen)

Post #: 17

RE: VB script / Excel question - 11/24/2008 8:10:22 AM