Active Directory Export - VBScript Need Help


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.

Thanks,
Brian



______________________________________________________________________________

' 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
OutPutFile.Close
Set FileSystem = Nothing
Set oContainer = Nothing

WScript.Echo "Finished"
WScript.Quit(0)
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 oUser.st & 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
OutPutFile.WriteLine
Next
End Sub
LVL 12
BNettles73Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David LeeCommented:
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.
0
BNettles73Author Commented:
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 ...

so

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!
Brian
0
David LeeCommented:
Hi, Brian.

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

with

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

That should do it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BNettles73Author Commented:
Works like a charm ... thanks for the help!

Brian
0
David LeeCommented:
Glad to hear it.  No problem.
0
ASAdminCommented:
Hi
Can this be written to an excel file? With the condition to write only those objects that are email enabled?


Regards,
Harsha
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.