Solved

VBS script to modify displayname from an excel sheet

Posted on 2009-07-02
3
649 Views
Last Modified: 2013-12-24
I need to modify every displayname in my domain. The display name will be a mixture of the existing display name, Firstname, Lastname and Initial. I did a CSVDE export into an excel document and then used the existing fields to create the new display name . Now I need to modify all the user objects and update the new display name. I found this vbs code but I'n not sure where to put the correct user fields.
Set oNetwork = CreateObject("wscript.network")

Domain = oNetwork.UserDomain

xlsname = "path to my spreadsheet.xls"

Set oExcelApp = CreateObject("Excel.Application")

Set oWorkbook = oExcelApp.Workbooks.Open(xlsname)

Set oWorksheet = oWorkbook.Worksheets(1)

oWorksheet.Activate

oExcelApp.Visible = False

iCounter = 2

bEmpty = False

Do Until bEmpty = True    

   strCell = oWorksheet.Cells(iCounter, 1)

   If strCell = "" Then

        bEmpty = True

   Else

    	EditUser oWorksheet.Cells(iCounter, 1), oWorksheet.Cells(iCounter, 2), oWorksheet.Cells(iCounter, 3)

	iCounter = iCounter + 1

   End If

Loop

oExcelApp.Quit

WScript.Echo("Done!")

 

Sub EditUser(SAMAccountName, Manager, Department)

	UserADSPath = GetObjectDN(SAMAccountName, Domain)

         If UserADSPath <> "?" Then

	       Set oUser = GetObject(UserADSPath)

	       oUser.Manager = Manager

	       oUser.Department = Department

	       oUser.SetInfo()

         Else

                WScript.Echo("User " & SAMAccountName & " not found")

         End If

End Sub

 

Function GetObjectDN(strObject, strDomain)

	On Error Resume Next : Err.Clear

	Set objNameTranslate = CreateObject("NameTranslate")

	objNameTranslate.Init 3, ""

	objNameTranslate.Set 3, strdomain & "\" & strObject

	strObjectDN = objNameTranslate.Get(1)

	If Err.Number <> 0 Then

		strObjectDN = "?"

	End If

	Set objNameTranslate = Nothing

	On Error Goto 0

	GetObjectDN = strObjectDN

End Function

Open in new window

CSVDE-Export.xls
0
Comment
Question by:leche671
  • 2
3 Comments
 
LVL 1

Expert Comment

by:nailgg
ID: 24761538
Hi. Leche671
Actually i didn't understand what do u need completely, but as i understand u can use "EditUser" sub to edit the fields(on 23. line of the code)
0
 

Author Comment

by:leche671
ID: 24769328
Sorry for such a late response I just got back into to work. My question is where in the script do I reference the proper fields. I don't see where the script looks up the fileds in the excel document. I want to make sure when the script runs it doesn't put the "department" field in the "telephone" field. I really don't know scripting
0
 
LVL 1

Accepted Solution

by:
nailgg earned 500 total points
ID: 24769552
Do Until bEmpty = True    
   strCell = oWorksheet.Cells(iCounter, 1)
   If strCell = "" Then
        bEmpty = True
   Else
        EditUser oWorksheet.Cells(iCounter, 1), oWorksheet.Cells(iCounter, 2), oWorksheet.Cells(iCounter, 3)
        iCounter = iCounter + 1
   End If
do u understand this code? it edits the fields until it founds an empty field. on the 16. line of code it calls the edituser sub.

this is the answer, or i still don't understand your question.
0

Join & Write a Comment

In this article, we will see the basic design consideration while designing a Multi-tenant web application in a simple manner. Though, many frameworks are available in the market to develop a multi - tenant application, but do they provide data, cod…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now