Atlanta .NET Regular Guys

Automated text editing with Microsoft Word and Microsoft Excel

Let's say that you have a script that does a task, say for example a script to create an account in Active Directory.  That script would look like this:

Set objOU = GetObject("LDAP://OU=myOrganizationalUnit,dc=MyDomain,dc=com")
Set objUser = objOU.Create("User", "cn=Test User")
objUser.Put "sAMAccountName", "test.user"
objUser.Put "userPrincipalName", "test.user@culminis.com"
objUser.SetInfo

objUser.AccountDisabled = FALSE
objUser.SetInfo

Now this is fantastic.  But what if you need to run this script for hundreds of users?  You don't want to be manually be editing this thing hundreds of times.  Now, if you've got a list of e-mail addresses (for example, from another e-mail system which has been shut down due to a virus written by this ***).  How can you take this list of e-mail addresses and merge it into a single script file?

You could write a quick little program to rip through your list of e-mail addresses, extract the first name and last name from an address that looks like this - first.last@somewhere.com, dynamically executing the script.  But that introduces lots of potential spots for failure - your name extraction, your looping, etc.  Risky on a production Active Directory system.

Instead you could use Word and Excel to do this task for you.

1. Save your list of e-mail addresses as a text file
2. In Microsoft Excel, import the text file as a delimited file like this:  1) on a new workbook sheet, select Data --> Import External Data --> Import Data.  2) Select your text file from step 1.  3) Accept the default delimited option and press Next.  4) In the delimiters section, indicate that your delimiter is the '@' symbol and be sure the Other box is checked.  5) Press Finish.
3. You now have a two column list in Excel with the first.last in one column and the domain.com in the other.  Copy the contents of the first column out and save it to a new text file
4. Repeat step 2 on a blank worksheet page, but this time select the text file you created in step 3 above as the data to be imported.  In substep 4, specify that your Other delimiter is a period ('.').  When you press Finish you'll have a new pair of columns with the first in one column and the last in the other.
5. Copy both columns out to a new text file.  This will create a tab delimited text file for you.  At the top of the file, put in column headers - FirstName <tab> LastName.  This helps in the next steps.  Save the text file
   
6. Now open Word and copy your script into a blank document.  Right click on the toolbars area of Word and make sure you have the Mail Merge toolbar checked and visible.
7. In Word, using the Mail Merge toolbar, create a datasource for your document.  Do this by clicking the button second from the left ("Open Data Source") and select the text file you created in step 5 above.  This should enable the rest of the buttons in the Mail Merge toolbar.
8. Everywhere you have the word test (ex Test User and test.user) you want to replace the text with a database field.  Highlight the word Test and hit the sixth button from the left ("Insert Merge Fields").  You should see a dialog box with the option to select either database fields or address fields.  Database fields are chosen by default.  In the fields list, you should see Firstname and Lastname as they are the column headers you added in step 5 above.  Select Firstname and press Insert.  In your document, you should now see the word <<Firstname>> with a grey background indicating that it is a replacable field.  If you don't see the grey background, press the 9th button from the left, "Highlight Merge Fields", to ensure you are successful.  Do this everywhere you see the word test.
9. Repeat step 8, but this time replace the word user from Test User and test.user with the <<Lastname>> field.
10. Press the 4th button from the right, "Merge to New Document", to have Microsoft Word loop through all the user names and create a script for each one.  The new document will pop up with a single script per page.  Select everything and copy it to a new text file (page breaks will be ignored).
11. *********************************************************************
12. You now have a script in which each address can be checked and verified.  Save it as a .vbs file and run it!
13. *********************************************************************

I'm sure that there are better ways to do this work, but when you have 10 minutes to get the work done, you use the tools you know.  I hope this little exercise in Microsoft Office automation adds another tool to your toolbox.

-- Matt Ranlett