Link to home
Start Free TrialLog in
Avatar of Mandy_
Mandy_

asked on

VBS - modifying AD-Query to insert user from excel

hi folks,

the code below is an "ad query" for different user attributes to import into excel.

What i exactly want:
1. Implement the code into excel VBS and get the the userID to search for, from
worksheet Cell "A1"

instead from the ldap string ("LDAP://cn=z555555,cn=Users,dc=contoso,dc=com")

2. The better choice would be a windows popup to enter the userid

3. Great would be to search for 2 userIDs to compare the data in excel


Option Explicit

Dim objUser, strExcelPath, objExcel, objSheet, k, objGroup

Const xlExcel7 = 39

' User object whose group membership will be documented in the
' spreadsheet.
Set objUser = GetObject("LDAP://cn=Z555555,cn=Users,dc=contoso,dc=com")

' Spreadsheet file to be created.
strExcelPath = "c:\temp\User.xls"

' Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If (Err.Number <> 0) Then
    On Error GoTo 0
    Wscript.Echo "Excel application not found."
    Wscript.Quit
End If
On Error GoTo 0

' Create a new workbook.
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "User Data"

' Populate spreadsheet cells with user attributes.
objSheet.Cells(1, 1).Value = "User Common Name"
objSheet.Cells(2, 1).Value = "sAMAccountName"
objSheet.Cells(3, 1).Value = "Display Name"
objSheet.Cells(4, 1).Value = "Distinguished Name"
objSheet.Cells(5, 1).Value = "Fullname"
objSheet.Cells(6, 1).Value = "Description"
objSheet.Cells(7, 1).Value = "Office"
objSheet.Cells(8, 1).Value = "Phone"
objSheet.Cells(9, 1).Value = "Mobile Phone"
objSheet.Cells(10, 1).Value = "Fax"
objSheet.Cells(11, 1).Value = "eMail"
objSheet.Cells(12, 1).Value = "City"
objSheet.Cells(13, 1).Value = "State"
objSheet.Cells(14, 1).Value = "Department"
objSheet.Cells(15, 1).Value = "ZipCode"
objSheet.Cells(16, 1).Value = "Company"
objSheet.Cells(17, 1).Value = "ExchSRV"
objSheet.Cells(18, 1).Value = "whenCreated"
objSheet.Cells(19 , 1).Value = "whenChanged"
objSheet.Cells(20, 1).Value = "AccountDisabled"
objSheet.Cells(21, 1).Value = "eMail-Address"
objSheet.Cells(22, 1).Value = "Mail-DB"
objSheet.Cells(23, 1).Value = "ProxyAddresses"


objSheet.Cells(1, 2).Value = objUser.cn
objSheet.Cells(2, 2).Value = objUser.sAMAccountName
objSheet.Cells(3, 2).Value = objUser.displayName
objSheet.Cells(4, 2).Value = objUser.distinguishedName
objSheet.Cells(5, 2).Value = objUser.FullName
objSheet.Cells(6, 2).Value = objUser.Description
objSheet.Cells(7, 2).Value = objUser.physicalDeliveryOfficeName
objSheet.Cells(8, 2).Value = objUser.TelephoneNumber
objSheet.Cells(9, 2).Value = objUser.mobile
objSheet.Cells(10, 2).Value = objUser.facsimileTelephoneNumber
objSheet.Cells(11, 2).Value = objUser.mail
objSheet.Cells(12, 2).Value = objUser.l
objSheet.Cells(13, 2).Value = objUser.st
objSheet.Cells(14, 2).Value = objUser.department
objSheet.Cells(15, 2).Value = objUser.postalCode
objSheet.Cells(16, 2).Value = objUser.Company
objSheet.Cells(17, 2).Value = objUser.msExchHomeServerName
objSheet.Cells(18, 2).Value = objUser.whenCreated 
objSheet.Cells(19, 2).Value = objUser.whenChanged
objSheet.Cells(20, 2).Value = objUser.AccountDisabled
objSheet.Cells(21, 2).Value = objUser.EmailAddress
objSheet.Cells(22, 2).Value = objUser.homeMDB 
objSheet.Cells(23, 2).Value = objUser.proxyAddresses


' Format the spreadsheet.
objSheet.Range("A1:A25").Font.Bold = True
objSheet.Select
objSheet.Range("B5").Select
objExcel.ActiveWindow.FreezePanes = True
objExcel.Columns(1).ColumnWidth = 20
objExcel.Columns(2).ColumnWidth = 30

' Save the spreadsheet and close the workbook.
' Specify Excel7 File Format.
objExcel.ActiveWorkbook.SaveAs strExcelPath, xlExcel7
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit

Wscript.Echo "Done"

Open in new window


appreciate for your help
ASKER CERTIFIED SOLUTION
Avatar of RobSampson
RobSampson
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mandy_
Mandy_

ASKER

great! thank. where can i change to set the attributes in vertical?

thanks
Hi, sorry for my delay.  This would allow you to display it vertically.

Rob.
Get-User-Attributes-From-samAcco.xlsm