Solved

VB script to update targetAddress from Excel data

Posted on 2008-10-01
11
1,248 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
  • 6
  • 5
11 Comments
 
LVL 67

Expert Comment

by:sirbounty
Comment Utility
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
Comment Utility
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.
0
 
LVL 67

Accepted Solution

by:
sirbounty earned 500 total points
Comment Utility
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
 

Author Comment

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

Expert Comment

by:sirbounty
Comment Utility
Can you post what you used for strPath? (mask any company/domain names as needed).
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:sgcarnes
Comment Utility
strPath = "dc=abc,dc=xyz,dc=com"
0
 
LVL 67

Expert Comment

by:sirbounty
Comment Utility
and abc is a child domain of xyz?
0
 

Author Comment

by:sgcarnes
Comment Utility
Correct.
0
 
LVL 67

Expert Comment

by:sirbounty
Comment Utility
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
Comment Utility
Specifying the strPath as "domain_controller/dc=abc,dc=xyz,dc=com" seems to do the trick.
0
 
LVL 67

Expert Comment

by:sirbounty
Comment Utility
Happy to help - thanx for the grade! :^)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

11 Experts available now in Live!

Get 1:1 Help Now