Swap First and Last Name

Cook09
Cook09 used Ask the Experts™
on
I am using some code that extracts a User Name from the Active Directory.  However, the name is (Last, First).  How can I either swap the two names around and remove the "  ,  "  Or just have the last name extracted and placed in a Cell, and then the first name extracted and placed in an adjacent cell?

Thanks,

Cook
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Try this

Chris
    arr = Split("Smith, Fred", ",")
    myName = Trim(arr(1)) & " " & Trim(arr(0))

Open in new window

Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
ANd if three name components then this still works as:

    arr = Split("Fletcher, Norman Stanley", ",")
    myName = Trim(arr(1)) & " " & Trim(arr(0)) ' Norman Stanley Fletcher

Chris

Author

Commented:
Chris,

Does it matter if ("Smith,Fred"... is ("Username"...) which consists of "Smith,Fred"...?

Thanks,

Cook
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
as long the input equates to a string, (with a comma therein) then it is of no matter.

Chris

Author

Commented:
Chris,

For MyName I get an Error 9 "Subscript out of Range".  Why would that be?

Cook

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrDetails As Variant, myName As Variant

More Code....

   strDetails = Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)

    arrDetails = Split(strDetails, ",")
    myName = Trim(arrDetails(1)) & " " & Trim(arrDetails(0))
    MsgBox strDetails

    Application.EnableEvents = False
    For intCount = LBound(arrDetails) + 1 To UBound(arrDetails) + 1
     Cells(intRow, intCount).Value = arrDetails(intCount - 1)
For intCount = LBound(arrDetails) + 1 To UBound(arrDetails) + 1
    
        Cells(intRow, intCount).Value = arrDetails(intCount - 1)
    Next
    Application.EnableEvents = True
End Sub

Open in new window

Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
What does strdetails look like?

Author

Commented:
Sorry, I was busy working on this...

 

For this particular function, none of the variables are declared, but I declared these, I wonder if that makes a difference.

  strDetails = Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)

Open in new window

Author

Commented:
Chris,

This Could work, if I ignored Column C.  It will put Last Name in A, First & Last in B, then keep the login correct for D.  I don't know why it grabs the extra Ron.  What do you think or see?

Cook

Retrieve-Other-AD-Data-Based-On-.xls
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Not following you ... can you identify what is teh value stored in strdetails after execution of that command line?
Chris

Author

Commented:
No, it just lists a number of items in a Colum when I look at it in Locals

Further through the program it reads,
Cook, Ron Cook, Ron cookr3

The Last couple of lines in Worksheet Change seems to make a swap, which I didn't notice before because it had a   |   separating everything, plus, I remove a couple of items that were in D & E.  I start with cookr3 in "D" to get it like below.  I need or could use D, Need A and would like to use B.  C is rubbish.  

It now fills
A                   B                          C                  D
Cook         Ron Cook     Ron cookr3      cookr3

Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Just in case ... I can make nothing of the sheet as other than a header row there is nothing in the sheet hence reference to the extra 'ron' is just confusing to me.

Chris

Author

Commented:
It's confusing me as well.  I could try and put FirstName and see if it will take it as a column Header.

Cook

Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
SO are you trying to populate teh column A with the name "Ron Cook" because if so I suspect a different split on the string will help ... can you upload the string in strDetails after execution of the line:

strDetails = Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)

Replace anything sensitive with dummy data and identify which data you want extracting and i'll try to do it directly.

Chris

Author

Commented:
No that didn't work.  I would just leave it until I find out what's causing the extra Ron, or what the reference to First is.

Cook

Author

Commented:
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Don't know if you missed the post so just in case:

Can you upload the string in strDetails after execution of the line:

strDetails = Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)

Replace anything sensitive with dummy data and identify which data you want extracting and i'll try to do it directly.

Chris

Author

Commented:
This is what I see.

strdetails.jpg
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Cant read it ;<{

Try:

strDetails = Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)
debug.print strdetails
which will output the string to the immediate window, (ctrl + G) to display it.

Chris
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
YOu can cut and paste from there - editing if needed to null sensitive data ideally without changing the structure i.e. x in place of characters.

Chris

Author

Commented:
It didn't print, just went to the Function:  But I did a Snagit.



  strObjectType                              "user"                                                      Variant/String

     strSearchField                "samAccountName"                                Variant/String


     strObjectToGet                      "cookr3"                                       Variant/String


     strCommaDelimProps   "Name,DisplayName,samAccountName,samAccountName"                                 Variant/String

 
  Get_LDAP_User_Properties          Empty                    Variant/Empty


     Get_LDAP_User_Properties         Empty           Variant/Empty

     arrGroupBits              Empty           Variant/Empty
     strDC                          Empty                                     Variant/Empty


     strDNSDomain                    Empty                  Variant/Empty


     objRootDSE                Empty                          Variant/Empty

     strdetails                   Empty                      Variant/Empty


     strBase                                Empty                            Variant/Empty


     adoCommand                                     Empty                                  Variant/Empty

     ADOConnection                                Empty                        Variant/Empty


     strFilter                                      Empty                        Variant/Empty


     strAttributes                                      Empty                     Variant/Empty

     arrProperties                                    Empty                         Variant/Empty


     strQuery                                           Empty                          Variant/Empty

        intCount                                              Empty                         Variant/Empty

                                                                 

Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
If I follow then the query is returning strdetails as Empty

i.e. it isn't your details at that point - are you sure the code is broken at the line after the strdetails variable is populated ... and you really should be able to see the data in teh output window ... but blank is prinbted as blank of course so replace:

debug.print strdetails
with
debug.print ">>>" & strdetails & "<<<"

Shoud make it easire to find!

Chris

Author

Commented:
>>>Cook, Ron Cook, Ron cookr3 cookr3<<<
>>>Cook, Ron Cook, Ron cookr3 cookr3<<<

This is what comes back after going through the Function

Cook
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
So in sheet you are looking for:

Name      DisplayName      samAccountName
Ron Cook     Cook, Ron                         cookr3

Is that the nature of the output required?

Chris
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
I am however a little confused because if the script returns:

Cook, Ron Cook, Ron cookr3 cookr3

I would expect the response in myname to Trim(arrDetails(1)) & " " & Trim(arrDetails(0))
to be
Ron Cook Cook

Chris

Author

Commented:
You're right. But when I put "myname" in place of arrDetails, I get a type 13  mismatch error.

Cook



    arrDetails = Split(strdetails, ",")
    myname = Trim(arrDetails(1)) & " " & Trim(arrDetails(0))
    Debug.Print ">>>" & myname & "<<<"
    MsgBox strdetails
    Application.EnableEvents = False
    'For intCount = LBound(arrDetails) + 1 To UBound(arrDetails) + 1
    For intCount = LBound(myname) + 1 To UBound(myname) + 1
        Cells(intRow, intCount).Value = myname(intCount - 1)
    Next

Open in new window

Author

Commented:
Actually, it can be   Ron Cook     Cook        or     Cook      Ron Cook   which ever way is easier.

Cook
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
PLease excuse me as I think we may be getting crossed.

myname is established to be the fullname after converting an input of the form "cook, Ron" into "Ron Cook"

This means it assumes the return from the call to Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)
is expected to be the string .. ""cook, Ron.

Cook, Ron Cook, Ron cookr3 cookr3 looks to be the output into the worksheet but I am trying to break out the correct information using a different approach so are you sure you are returning the output from the immediate window to the code:

I have modified it to make two outputs to perhaps aid understanding
Private Sub Worksheet_Change(ByVal Target As Range) 
Dim arrDetails As Variant, myName As Variant 
 
More Code.... 
 
   strDetails = Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps) 
    Debug.Print ">>>>" & strDetails & "<<<<" 
    arrDetails = Split(strDetails, ",") 
    myname = Trim(arrDetails(1)) & " " & Trim(arrDetails(0)) 
    Debug.Print ">>" & myname & "<<" 
 
    arrDetails = Split(strDetails, ",") 
    myName = Trim(arrDetails(1)) & " " & Trim(arrDetails(0)) 
    MsgBox strDetails 
 
    Application.EnableEvents = False 
    For intCount = LBound(arrDetails) + 1 To UBound(arrDetails) + 1 
     Cells(intRow, intCount).Value = arrDetails(intCount - 1) 
For intCount = LBound(arrDetails) + 1 To UBound(arrDetails) + 1 
     
        Cells(intRow, intCount).Value = arrDetails(intCount - 1) 
    Next 
    Application.EnableEvents = True 
End Sub

Open in new window

Author

Commented:
I'm glad you checked in, although I'm close to leaving...been here over 24 hours.   This is what the debug.print showed.

>>>>Cook, Ron Cook, Ron cookr3 cookr3<<<<
>>Ron Cook Cook<<

In my last writing, I assumed that the cookr3 was still going to be under the samAccountName.

But overall yes...  Ron Cook     Cook     cookr3

Thanks for being patient with me.

Cook


Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Have a good kip I should have responded in the meantime!

Chris

Author

Commented:
Thanks, I will and appreciate your sticking with me.

Cook
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Name, display and sam are hopefully teh data you require for columns A:C respectively

Chris
Dim strDetails As String
Dim myArr() As String
Dim arrName() As String
Dim name As String
Dim display As String
Dim sam As String

        strDetails = "Cook, Ron Cook, Ron cookr3 cookr3"
        strDetails = Replace(strDetails, ", ", ",~")
        myArr = Split(strDetails, " ")
        arrName = Split(myArr(0), ",~")
        name = Trim(arrName(1)) & " " & Trim(arrName(0))
        display = Trim(Replace(myArr(0), "~", " "))
        sam = Trim(myArr(2))

Open in new window

Author

Commented:
Chris,

I"m still having difficulty with this, it keeps erroring out..  Part of the problem may be that strDetails is a result of the login ID or cookr3.  It could be anyone's ID.  When I was using Ron Cook     Cook    cookr3, that was specific for me, but it could be anyone's.  I've attached the worksheet so you can see the flow.

Two areas that I noticed is:

1. If "(arrdetails(2))" just returned cookr3 instead of [ Ron cookr3 cookr3 ]   or if the

2.   For intCount = LBound(arrDetails) + 1 To UBound(arrDetails) + 1

              Cells(intRow, intCount).Value = arrDetails(intCount - 1)
       Next

was written differently, because this is where the errors are occuring

     intCount = LBound(arrDetails) + 1 To UBound(arrDetails) + 1

  I'm not sure of the " +1 " beside each one, but the incount = 5 and we are only concerned with the first three.

Possibly if column C was just blank, that would be okay too.  Right now, I can use this, I just won't refer to C2, but I'd like for it to be "clean" .

I don't know if this helps.

Cook

Retrieve-Other-AD-Data-4.xls
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Since I don't have your exchange server I had to initialise strDetails hence:

        strDetails = "Cook, Ron Cook, Ron cookr3 cookr3"
In your case that will be your line:
   strDetails = Get_LDAP_User_Properties(strObjectType, strSearchField, strObjectToGet, strCommaDelimProps)  
        myArr = Split(strDetails, " ")
        arrName = Split(myArr(0), ",~")
        name = Trim(arrName(1)) & " " & Trim(arrName(0))
        display = Trim(Replace(myArr(0), "~", " "))
        sam = Trim(myArr(2))

Author

Commented:
Which one do I use to the  For...Next statement?

Cook
Software Quality Lead Engineer
Top Expert 2011
Commented:
This is a redesign ... there is no for next, the datums you requested are Name, DIsplay Name and SAM.  These shoudl be extracted within the snippet as name, display and sam respectively and you simply assign them to the columns in your worksheet.

Chris

Author

Commented:
Thanks Chris,

This was a lot for me to digest.
You are right, your involvement was much more than I originally requested.
Would you mind answering one more question?  Is it within the scope of "Excel" or another area within EE, if I want to extract something like: Fullname, LastName, User, Group, & Dept, from the Active Director?
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
It is possible to do so indeed I have done such in the past but not sure I would personally try to respond due to the lack of an exchange server these days.  There are however a lot of clever folk here.

Chris

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial