Active Directory Export - VBScript Need Help

Posted on 2004-11-05
Last Modified: 2009-02-13

I've been working on a vbscript to export user objects that our controller uses for accounting purposes. Everything works great for the most part. The only problem I have is with the street address field. Because you can enter multiple lines in the street address it breaks the line in my excel document.

The code in question

OutPutFile.Write oUser.streetAddress & vbtab

For instance

123 Main Street
Suite 2300

In the excel document the line will break whenever it hits the "Suite 2300" and go to the next row.

Is there a way I can modify my script to handle returns in the Street Address attribute? I'm not very much of a programmer, so please be gentle. =)

My apologies if the information isn't clear, please let me know if more info is required.



' This script exports the following fields
' samaccountname, displayname, description, streetaddress, I,
' st, postalcode, mail, mailnickname, homedb, homedrive, homedirectory,
' whencreated, employeeid extensionattribute4, manager, telephone

'Global variables
Dim oContainer
Dim OutPutFile
Dim FileSystem

'Initialize global variables
Set FileSystem = WScript.CreateObject("Scripting.FileSystemObject")
Set OutPutFile = FileSystem.CreateTextFile("User Report.xls", True)
OutPutFile.WriteLine      "Username" & vbtab & "Description" & vbtab & "Street Address" & vbtab & "City" & vbtab & "State" & vbtab & "Zip Code" & vbtab & "Display Name" & vbtab & "Email Address" & vbtab & "Email Alias" & vbtab & "Exchange Home Server" & vbtab & "Home Drive" & vbtab & "Home Directory" & vbtab & "Created" & vbtab & "EmployeeID" & vbtab & "extensionAttribute4" & vbtab & "Manager" & vbtab & "Telephone Number"
Set oContainer = GetObject("LDAP://OU=Accounts,DC=Corp,DC=Contoso,DC=com")

'Enumerate Container
EnumerateUsers oContainer

'Clean up
Set FileSystem = Nothing
Set oContainer = Nothing

WScript.Echo "Finished"
Sub EnumerateUsers(oCont)
Dim oUser
For Each oUser In oCont
Select Case LCase(oUser.Class)
Case "user"

' User Account Login
If Not IsEmpty(oUser.sAMAccountName) Then
OutPutFile.Write oUser.sAMAccountName & vbtab
End If

' User Account Description
OutPutFile.Write oUser.description & vbtab

' User Account Address Information
OutPutFile.Write oUser.streetAddress & vbtab
OutPutFile.Write oUser.L & vbtab
OutPutFile.Write & vbtab
OutPutFile.Write oUser.postalCode & vbtab

' User Account Display Name
OutPutFile.Write oUser.displayName & vbtab

'Email Information
OutPutFile.Write oUser.mail & vbtab
OutPutFile.Write oUser.mailNickname & vbtab
' Distinguished Path of Mailbox Home Server
OutPutFile.Write oUser.msExchHomeServerName & vbtab

' User Home Directory Information
OutPutFile.Write oUser.homedrive & vbtab
OutPutFile.Write oUser.homeDirectory & vbtab

' User Account General
OutPutFile.Write oUser.whencreated & vbtab
OutPutFile.Write oUser.employeeID & vbtab
OutPutFile.Write oUser.extensionAttribute4 & vbtab
OutPutFile.Write oUser.manager & vbtab

' User Account Phone Number
OutPutFile.Write oUser.telephoneNumber & vbtab

Case "organizationalunit" , "container"

EnumerateUsers oUser
End Select
End Sub
Question by:BNettles73
    LVL 76

    Expert Comment

    by:David Lee
    I'm not clear on how you want to fix the problem.  If all you want to do is strip the returns from the address, then the code below will do that.

        strAddress = Replace(oUser.streetAddress, vbCrLf, " ")

    This will replace each of the returns with a space.  If you don't want a space, then you can substitute whatever character you want in its place.
    LVL 12

    Author Comment

    That is exactly what I want to do ... I would like the Suite address to be appended on to the primary line of the street address ...


    123 Main Street
    Suite 2300

    will show as 123 Main Street Suite 2300, instead of breaking to a new row in the spreadsheet ...

    How would I place the code you list into the script? I've been basically working off of stuff I copied from MSDN ..

    Thanks in advance for your help!
    LVL 76

    Accepted Solution

    Hi, Brian.

    Replace this line of code
        OutPutFile.Write oUser.streetAddress & vbtab


        strAddress = Replace(oUser.streetAddress, vbCrLf, " ")
        OutPutFile.Write strAddress & vbtab

    That should do it.
    LVL 12

    Author Comment

    Works like a charm ... thanks for the help!

    LVL 76

    Expert Comment

    by:David Lee
    Glad to hear it.  No problem.

    Expert Comment

    Can this be written to an excel file? With the condition to write only those objects that are email enabled?


    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

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    732 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

    21 Experts available now in Live!

    Get 1:1 Help Now