Swap First and Last Name

Try this

Chris

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

Select allOpen in new window

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

Chris,

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

Thanks,

Cook

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

Chris

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

Select allOpen in new window

What does strdetails look like?

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)

Select allOpen in new window

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

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

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

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

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

Cook

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

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

Here it is.

Retrieve-Other-AD-Data.xls

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

This is what I see.

strdetails.jpg

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

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

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

                                                                 

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

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

This is what comes back after going through the Function

Cook

So in sheet you are looking for:

Name      DisplayName      samAccountName
Ron Cook     Cook, Ron                         cookr3

Is that the nature of the output required?

Chris

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

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

Select allOpen in new window

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

Cook

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

Select allOpen in new window

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

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

Chris

Thanks, I will and appreciate your sticking with me.

Cook

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))

Select allOpen in new window

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

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))

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

Cook

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?

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