[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

VBS script to modify displayname from an excel sheet

Posted on 2009-07-02
3
Medium Priority
?
660 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office 365 is a subscriptions based service which includes services like Exchange Online and Skype for business Online. These services integrate with Microsoft's online version of Active Directory called Azure Active Directory.
Active Directory can easily get cluttered with unused service, user and computer accounts. In this article, I will show you the way I like to implement ADCleanup..
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 from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
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…
Suggested Courses

649 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