?
Solved

VB script to update targetAddress from Excel data

Posted on 2008-10-01
11
Medium Priority
?
1,289 Views
Last Modified: 2013-12-24
I have 650 users who are leaving my Exchange organization. I want to forward email from their old mailbox to their new mailbox by setting the targetAddress attribute on their existing mailbox to forward to their new email address. I have a spreadsheet with two columns - Column A is their existing primary emai address and column B is their new email address.

I need a vb script that will do the following:

1) Read email address from Excel spreadsheet in Column 1.
2) Search Active Directory for user with matching 'mail' attribute.
3) Once user has been found with matching 'mail' attribute, use email address in column A2 to update the'targetAddress' attribute of that same user.

I've attached an example of the .xls file that I have  to work with.
targetAddress.xls
0
Comment
Question by:sgcarnes
[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
  • 6
  • 5
11 Comments
 
LVL 67

Expert Comment

by:sirbounty
ID: 22634627
This should do what you ask...I can't test it completely, so use a test user and give it a try...
I went under the assumption that the data was to be laid out exactly as in your attachment.
The ExcelFile variable should point to the path\filename where the excel sheet is located and the sheet name should be, as in your example, named IHS_to_be_migrated.

Good luck!
Const ADS_PROPERTY_APPEND = 3
Const ADS_SCOPE_SUBTREE = 2
 
Dim objRoot : Set objRoot = GetObject("LDAP://RootDSE")
Dim objDomain : Set objDomain = GetObject("LDAP://" & objRoot.Get("defaultNamingContext"))
Dim dicData : Set dicData = CreateObject("Scripting.Dictionary")
 
Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
Dim objCommand : Set objCommand =   CreateObject("ADODB.Command")
Dim objRS : Set objRS = CreateObject("ADODB.Recordset")
 
ExcelFile = "C:\targetAddress.xls"
 
With objConnection
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .ConnectionString="Data Source=" & ExcelFile & ";Extended Properties=""Excel 8.0;HDR=No;"";"
  .Open
End With
 
strSQL = "SELECT * FROM [IHS_to_be_migrated$]"
 
objRS.Open strSQL, objConnection
 
objRS.MoveFirst
 
Do While Not objRS.EOF
  If IsNull(objRS.Fields(0)) Then Exit Do
    MailAddress = objRS.Fields(0).Value
    TargetAddress = objRS.Fields(1).Value
    dicData.Add MailAddress, TargetAddress
    objRS.MoveNext
Loop
 
objRS.Close
objConnection.Close
 
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
 
For Each item in dicData.Keys
  objCommand.CommandText = "SELECT aDSPath FROM '" & objDomain.aDSPath & "' WHERE mail='" & item & "'"
  Set objRS = objCommand.Execute
  objRS.MoveFirst
  Do Until objRS.EOF
    Dim objUser : Set objUser = GetObject(objRS.Fields(0))
    objUser.PutEx ADS_PROPERTY_APPEND, "targetAddress", Array(dicData(item))
    objRS.MoveNext  
  Loop
Next
 
Set objRS = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
Set dicData = Nothing
Set objDomain = Nothing
Set objRoot = Nothing

Open in new window

0
 

Author Comment

by:sgcarnes
ID: 22635007
I think this is exactly what I want but I need to run it against multiple target domains. Can you add a couple of constants that I could modify to change the target domain? I'm just not sure of the syntax.
1
 
LVL 67

Accepted Solution

by:
sirbounty earned 2000 total points
ID: 22635097
Sure...just alter the third line to read a similar path for your domain...
Const ADS_PROPERTY_APPEND = 3
Const ADS_SCOPE_SUBTREE = 2
strPath = "dc=domain,dc=com"
 
Dim dicData : Set dicData = CreateObject("Scripting.Dictionary")
 
Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
Dim objCommand : Set objCommand =   CreateObject("ADODB.Command")
Dim objRS : Set objRS = CreateObject("ADODB.Recordset")
 
ExcelFile = "C:\targetAddress.xls"
 
With objConnection
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .ConnectionString="Data Source=" & ExcelFile & ";Extended Properties=""Excel 8.0;HDR=No;"";"
  .Open
End With
 
strSQL = "SELECT * FROM [IHS_to_be_migrated$]"
 
objRS.Open strSQL, objConnection
 
objRS.MoveFirst
 
Do While Not objRS.EOF
  If IsNull(objRS.Fields(0)) Then Exit Do
    MailAddress = objRS.Fields(0).Value
    TargetAddress = objRS.Fields(1).Value
    dicData.Add MailAddress, TargetAddress
    objRS.MoveNext
Loop
 
objRS.Close
objConnection.Close
 
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
 
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 
 
For Each item in dicData.Keys
  objCommand.CommandText = "SELECT aDSPath FROM 'LDAP://" & strPath & "' WHERE mail='" & item & "'"
  Set objRS = objCommand.Execute
  objRS.MoveFirst
  Do Until objRS.EOF
    Dim objUser : Set objUser = GetObject(objRS.Fields(0))
    objUser.PutEx ADS_PROPERTY_APPEND, "targetAddress", Array(dicData(item))
    objRS.MoveNext  
  Loop
Next
 
Set objRS = Nothing
Set objCommand = Nothing
Set objConnection = Nothing
Set dicData = Nothing

Open in new window

0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:sgcarnes
ID: 22635691
I get script error Line45 Char3
One or more errors occurred during processing of command.
Code 80040E14
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 22635706
Can you post what you used for strPath? (mask any company/domain names as needed).
0
 

Author Comment

by:sgcarnes
ID: 22635795
strPath = "dc=abc,dc=xyz,dc=com"
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 22635956
and abc is a child domain of xyz?
0
 

Author Comment

by:sgcarnes
ID: 22636906
Correct.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 22636943
Modify this portion of the code:

For Each item in dicData.Keys
  objCommand.CommandText = "SELECT aDSPath FROM 'LDAP://" & strPath & "' WHERE mail='" & item & "'"

to read:
For Each item in dicData.Keys
  objCommand.CommandText = "SELECT aDSPath FROM 'LDAP://" & strPath & "' WHERE mail='" & item & "'"
wscript.echo objCommand.CommandText
 
'please post the output - mask any domain names that you need to...

Open in new window

0
 

Author Comment

by:sgcarnes
ID: 22637030
Specifying the strPath as "domain_controller/dc=abc,dc=xyz,dc=com" seems to do the trick.
0
 
LVL 67

Expert Comment

by:sirbounty
ID: 22637055
Happy to help - thanx for the grade! :^)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

777 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