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=co m")
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
appreciate for your help
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=co
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"
appreciate for your help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi, sorry for my delay. This would allow you to display it vertically.
Rob.
Get-User-Attributes-From-samAcco.xlsm
Rob.
Get-User-Attributes-From-samAcco.xlsm
ASKER
thanks