[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3948
  • Last Modified:

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.



' 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 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
End Sub
  • 3
  • 2
1 Solution
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.
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 ...


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!
David LeeCommented:
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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

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


Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now