Link to home
Create AccountLog 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
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
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