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