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 = "KutoolsforExcel"
    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