Search This Blog

Showing posts with label Reverse OU path. Show all posts
Showing posts with label Reverse OU path. Show all posts

Mar 20, 2014

Script to reverse OU path so it can be sorted from top down - Excel macro & vb procedure

' This excel macro reverse OU path so it can be sorted from domain top to OU trees

' For example: CN=John Lan,OU=IT,OU=accounts,DC=johnlan,DC=com
' Will be reversed to: DC=com,DC=johnlan,OU=accounts,OU=IT,CN=John Lan

' if you want only a function that can reverse OU path (distinguished name), use function ReverseOU
' if you want to use it in excel, copy both ReverseOU and ReverseText as your macro

' How to use it in Excel
'
' 1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
' 2. Click Insert > Module, and paste the following macro in the Modulewindow.
' 3. Then press F5, a dialog is displayed on the screen, and you need select a range to work with.
' 4. And then press OK, and all the text strings have been reversed.
' (c) JohnLan@gmail.com

' ReverseOU can be called from any vb script
Function ReverseOU(s)
    Dim temp
    Dim arrValue
        arrValue = Split(s, ",")
        xLen = UBound(arrValue) + 1
        'wscript.echo xLen
     
        For i = 0 To ((xLen - 1) / 2)
            'wscript.echo i
            'wscript.echo xLen-i-1
            temp = arrValue(i)
            arrValue(i) = arrValue(xLen - i - 1)
            arrValue(xLen - i - 1) = temp
            'wscript.echo "----------"
        Next
        ReverseOU = Join(arrValue, ",")
End Function

'Below is for excel
Sub ReverseText()
    Dim Rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "by JohnLan@gmail.com"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    For Each Rng In WorkRng
        xOut = ReverseOU(Rng.Value)
        Rng.Value = xOut
    Next
End Sub