Extract all CN='Name' from a Active Directory data-dumped-cell

[mateo561]

One of my Administrator types is dumping information from AD so some
mid-level manager can justify his position and salary (too sarcastic?? ;-)..
anyway.. AD dumps very verbose information into his cells.

Example: In cell A2 you have...

CN=Daniel ****,OU=Desktops,OU=Users,OU=Executive
Offices,OU=FCC,DC=Sugar,DC=corp;CN=Rich ******,OU=Laptops,OU=Users,OU=Service
Center,OU=FCC,DC=Sugar,DC=corp;CN=Charles ******... and on and on.

(names changed to protect the innocent)

these text stings get very long.. some even hit the character limit for
cells.. but all that my guy wants are the names of the users, which of course
are represented after the "CN=" and ends with the next ","

Desired Result: In cell B2 show...
Daniel ****, Rich ******, Charles ******

I can easily pull "Daniel ****" using text functions but I lose it when I
have then find Rich, and Charles, and Tom, Dick and Harry.. until somewhere,
someplace it all ends with the LAST name which could be 7 names, 32 names, or
124 names.

So off to the Excel Discussion Groups to nab.. Do'h... 'use' some VBA from
someone who really knows what they are doing.

I have been searching through your forums, reading VB questions from the
unwashed masses and checking out the suggestions from the enlightend ones.
Visited sites like contextures, peltiertech, cpearson and more. I have tried
to modify some code that was posted both on this forum and others and learned
alot of good, although ancillary, stuff in the process.

I now post this questions to the world, as I am brain fried, body tired, mad
at myself for not being smarter (or better looking) and besides, I am out of
alcohol.

Any help in this issue would be much appreciated and it sure would make me
look good to those mid-level managers as well. I have used this forum
numerous times in the past and you folks have always come through to
enlighten my brain and boost my intellect (at least in the eyes of those
managers).

So thanks in advance and I hope you make me look good yet again!

Version: Excel 2003 SP2

 

[Jim Cone]

Following requires a comma at the end of each name.
The Split function will not work on XL97
It is up to you to do something with the names as
they are displayed in a message box in the code below ...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub GetThoseGuys()
Dim strNames As String
Dim varNames As Variant
Dim N As Long

strNames = "CN=Daniel ****,OU=Desktops,OU=Users,OU=Executive Offices," & _
"OU=FCC,DC=Sugar,DC=corp;CN=Rich ******," & _
"OU=Laptops,OU=Users,OU=Service Center,OU=FCC," & _
"DC=Sugar,DC=corp;CN=Charles ******,... and on and on."
varNames = Split(strNames, "CN=")
strNames = vbNullString

For N = LBound(varNames) To UBound(varNames)
If Len(varNames(N)) Then
strNames = strNames & vbCr & Mid$(varNames(N), 1, InStr(4, varNames(N), ",", _
vbTextCompare) - 1)
End If
Next 'N
MsgBox strNames
End Sub
'------------------------------



"mateo561"
<[email protected]>
wrote in message
One of my Administrator types is dumping information from AD so some
mid-level manager can justify his position and salary (too sarcastic?? ;-)..
anyway.. AD dumps very verbose information into his cells.

Example: In cell A2 you have...

CN=Daniel ****,OU=Desktops,OU=Users,OU=Executive
Offices,OU=FCC,DC=Sugar,DC=corp;CN=Rich ******,OU=Laptops,OU=Users,OU=Service
Center,OU=FCC,DC=Sugar,DC=corp;CN=Charles ******... and on and on.

(names changed to protect the innocent)
these text stings get very long.. some even hit the character limit for
cells.. but all that my guy wants are the names of the users, which of course
are represented after the "CN=" and ends with the next ","

Desired Result: In cell B2 show...
Daniel ****, Rich ******, Charles ******

I can easily pull "Daniel ****" using text functions but I lose it when I
have then find Rich, and Charles, and Tom, Dick and Harry.. until somewhere,
someplace it all ends with the LAST name which could be 7 names, 32 names, or
124 names.
So off to the Excel Discussion Groups to nab.. Do'h... 'use' some VBA from
someone who really knows what they are doing.
I have been searching through your forums, reading VB questions from the
unwashed masses and checking out the suggestions from the enlightend ones.
Visited sites like contextures, peltiertech, cpearson and more. I have tried
to modify some code that was posted both on this forum and others and learned
alot of good, although ancillary, stuff in the process.
I now post this questions to the world, as I am brain fried, body tired, mad
at myself for not being smarter (or better looking) and besides, I am out of
alcohol.
Any help in this issue would be much appreciated and it sure would make me
look good to those mid-level managers as well. I have used this forum
numerous times in the past and you folks have always come through to
enlighten my brain and boost my intellect (at least in the eyes of those
managers).
So thanks in advance and I hope you make me look good yet again!
Version: Excel 2003 SP2

 

[Tom Ogilvy]

Sub BBB()
Dim ss As String, s As String, s1 As String
Dim v As Variant, v1 As Variant, v2 As Variant
Dim i As Long, j As Long, iloc As Long
ss = ""
's = Cell.Value
s = "CN=Daniel ****,OU=Desktops,OU=Users,OU=Executive " & _
"Offices,OU=FCC,DC=Sugar,DC=corp;CN=Rich ******," & _
"OU=Laptops,OU=Users,OU=Service " & _
"Center,OU=FCC,DC=Sugar,DC=corp;CN=Charles ******"
v = Split(s, ";")
For i = LBound(v) To UBound(v)
s = v(i)
v1 = Split(s, ",")
For j = LBound(v1) To UBound(v1)
s1 = v1(j)
iloc = InStr(1, s1, "CN=", vbTextCompare)
If iloc <> 0 Then
s1 = Mid(s1, iloc + 3, Len(s1))
ss = ss & Application.Trim(s1) & Chr(10)
End If
Next
Next
MsgBox ss
End Sub

 

[NickHK]

Not tested much, but here's something that should give you a start. If you
will only ever need to pull out the names (marked by "CN=") then you can
simplity the code a lot. Also, if the delimeters will never change, you can
remove those optional arguments. You should add error/input checking.
Depending how you intend to use the data, you could output an array of
strings instead.

Public Function GetInfoFromADDump(InCell As Range, _
Optional BlockDelim As String = ";",
_
Optional FieldDelim As String = ",",
_
Optional FieldIndex As Long = 0, _
Optional FieldMarker As String =
"CN=", _
Optional OutDelim As String = ",") _
As String

Dim Arr As Variant
Dim Arr2 As Variant

Dim i As Long
Dim j As Long

Dim TempStr As String

Arr = Split(InCell, BlockDelim)
For i = 0 To UBound(Arr)
If FieldIndex >= 0 Then
'Only a single filed from each block, defined by the index
TempStr = TempStr & Mid(Split(Arr(i), FieldDelim)(FieldIndex),
Len(FieldMarker) + 1) & OutDelim
Else
'Multiple fields from each Block defined by FieldMarker
Arr2 = Split(Arr(i), FieldDelim)
For j = 0 To UBound(Arr2)
If Left(Arr2(j), Len(FieldMarker)) = FieldMarker Then
TempStr = TempStr & Mid(Arr2(j), Len(FieldMarker) + 1) &
OutDelim
End If
Next
End If
Next

'Strip the last ","
GetInfoFromADDump = Left(TempStr, Len(TempStr) - 1)

End Function

Also, you can deal directly with AD from VB/VBA, if you feel so inclined :
http://www.codeguru.com/forum/archive/index.php/t-309184.html

NickHK

 

[Roger Govier]

Hi Luc

I have never used RegExp before and thought I would give this a try.
I get a compile error - User defined type not defined as soon as I try
to run it.

Could you please tell me what I need to do to make it work?

--
Regards

Roger Govier

In a module:

Function extractUsers(s As String)
Dim re As New RegExp

re.Global = True
re.Pattern = "CN=([^,]+,?)([^;]+?"
extractUsers = re.Replace(s, "$1")
End Function

In B2:
=extractUsers(a2)

--
Regards,
Luc.

"Festina Lente"

One of my Administrator types is dumping information from AD so some
mid-level manager can justify his position and salary (too
sarcastic?? ;-)..
anyway.. AD dumps very verbose information into his cells.

Example: In cell A2 you have...

CN=Daniel ****,OU=Desktops,OU=Users,OU=Executive
Offices,OU=FCC,DC=Sugar,DC=corp;CN=Rich
******,OU=Laptops,OU=Users,OU=Service
Center,OU=FCC,DC=Sugar,DC=corp;CN=Charles ******... and on and on.

(names changed to protect the innocent)

these text stings get very long.. some even hit the character limit
for
cells.. but all that my guy wants are the names of the users, which
of course
are represented after the "CN=" and ends with the next ","

Desired Result: In cell B2 show...
Daniel ****, Rich ******, Charles ******

I can easily pull "Daniel ****" using text functions but I lose it
when I
have then find Rich, and Charles, and Tom, Dick and Harry.. until
somewhere,
someplace it all ends with the LAST name which could be 7 names, 32
names, or
124 names.

So off to the Excel Discussion Groups to nab.. Do'h... 'use' some VBA
from
someone who really knows what they are doing.

I have been searching through your forums, reading VB questions from
the
unwashed masses and checking out the suggestions from the enlightend
ones.
Visited sites like contextures, peltiertech, cpearson and more. I
have tried
to modify some code that was posted both on this forum and others and
learned
alot of good, although ancillary, stuff in the process.

I now post this questions to the world, as I am brain fried, body
tired, mad
at myself for not being smarter (or better looking) and besides, I am
out of
alcohol.

Any help in this issue would be much appreciated and it sure would
make me
look good to those mid-level managers as well. I have used this
forum
numerous times in the past and you folks have always come through to
enlighten my brain and boost my intellect (at least in the eyes of
those
managers).

So thanks in advance and I hope you make me look good yet again!

Version: Excel 2003 SP2

 

[Roger Govier]

Thank you Luc, that fixed it.

Now, all I have to do is try and understand it!!!!!

--
Regards

Roger Govier

Sorry, I should have mentioned it.
You need to add a reference to "Microsoft VBScript Regular Expressions
5.5"
You do it through "Tools menu -> References..." in the VBE window...
--
Regards,
Luc.

"Festina Lente"

Hi Luc

I have never used RegExp before and thought I would give this a try.
I get a compile error - User defined type not defined as soon as I
try
to run it.

Could you please tell me what I need to do to make it work?

--
Regards

Roger Govier

In a module:

Function extractUsers(s As String)
Dim re As New RegExp

re.Global = True
re.Pattern = "CN=([^,]+,?)([^;]+?"
extractUsers = re.Replace(s, "$1")
End Function

In B2:
=extractUsers(a2)

--
Regards,
Luc.

"Festina Lente"


:

One of my Administrator types is dumping information from AD so
some
mid-level manager can justify his position and salary (too
sarcastic?? ;-)..
anyway.. AD dumps very verbose information into his cells.

Example: In cell A2 you have...

CN=Daniel ****,OU=Desktops,OU=Users,OU=Executive
Offices,OU=FCC,DC=Sugar,DC=corp;CN=Rich
******,OU=Laptops,OU=Users,OU=Service
Center,OU=FCC,DC=Sugar,DC=corp;CN=Charles ******... and on and on.

(names changed to protect the innocent)

these text stings get very long.. some even hit the character
limit
for
cells.. but all that my guy wants are the names of the users,
which
of course
are represented after the "CN=" and ends with the next ","

Desired Result: In cell B2 show...
Daniel ****, Rich ******, Charles ******

I can easily pull "Daniel ****" using text functions but I lose it
when I
have then find Rich, and Charles, and Tom, Dick and Harry.. until
somewhere,
someplace it all ends with the LAST name which could be 7 names,
32
names, or
124 names.

So off to the Excel Discussion Groups to nab.. Do'h... 'use' some
VBA
from
someone who really knows what they are doing.

I have been searching through your forums, reading VB questions
from
the
unwashed masses and checking out the suggestions from the
enlightend
ones.
Visited sites like contextures, peltiertech, cpearson and more. I
have tried
to modify some code that was posted both on this forum and others
and
learned
alot of good, although ancillary, stuff in the process.

I now post this questions to the world, as I am brain fried, body
tired, mad
at myself for not being smarter (or better looking) and besides, I
am
out of
alcohol.

Any help in this issue would be much appreciated and it sure would
make me
look good to those mid-level managers as well. I have used this
forum
numerous times in the past and you folks have always come through
to
enlighten my brain and boost my intellect (at least in the eyes of
those
managers).

So thanks in advance and I hope you make me look good yet again!

Version: Excel 2003 SP2

 

[PapaDos]

LOL
Not easy to see what the Replace method does...
I suggest you play with the Execute method to learn how regular expressions
match...

By the way, my little function will probably leave a "," at the end of the
users list with "real" data...

--
Regards,
Luc.

"Festina Lente"

Thank you Luc, that fixed it.

Now, all I have to do is try and understand it!!!!!

--
Regards

Roger Govier

Sorry, I should have mentioned it.
You need to add a reference to "Microsoft VBScript Regular Expressions
5.5"
You do it through "Tools menu -> References..." in the VBE window...
--
Regards,
Luc.

"Festina Lente"

Hi Luc

I have never used RegExp before and thought I would give this a try.
I get a compile error - User defined type not defined as soon as I
try
to run it.

Could you please tell me what I need to do to make it work?

--
Regards

Roger Govier


In a module:

Function extractUsers(s As String)
Dim re As New RegExp

re.Global = True
re.Pattern = "CN=([^,]+,?)([^;]+?"
extractUsers = re.Replace(s, "$1")
End Function

In B2:
=extractUsers(a2)

--
Regards,
Luc.

"Festina Lente"


:

One of my Administrator types is dumping information from AD so
some
mid-level manager can justify his position and salary (too
sarcastic?? ;-)..
anyway.. AD dumps very verbose information into his cells.

Example: In cell A2 you have...

CN=Daniel ****,OU=Desktops,OU=Users,OU=Executive
Offices,OU=FCC,DC=Sugar,DC=corp;CN=Rich
******,OU=Laptops,OU=Users,OU=Service
Center,OU=FCC,DC=Sugar,DC=corp;CN=Charles ******... and on and on.

(names changed to protect the innocent)

these text stings get very long.. some even hit the character
limit
for
cells.. but all that my guy wants are the names of the users,
which
of course
are represented after the "CN=" and ends with the next ","

Desired Result: In cell B2 show...
Daniel ****, Rich ******, Charles ******

I can easily pull "Daniel ****" using text functions but I lose it
when I
have then find Rich, and Charles, and Tom, Dick and Harry.. until
somewhere,
someplace it all ends with the LAST name which could be 7 names,
32
names, or
124 names.

So off to the Excel Discussion Groups to nab.. Do'h... 'use' some
VBA
from
someone who really knows what they are doing.

I have been searching through your forums, reading VB questions
from
the
unwashed masses and checking out the suggestions from the
enlightend
ones.
Visited sites like contextures, peltiertech, cpearson and more. I
have tried
to modify some code that was posted both on this forum and others
and
learned
alot of good, although ancillary, stuff in the process.

I now post this questions to the world, as I am brain fried, body
tired, mad
at myself for not being smarter (or better looking) and besides, I
am
out of
alcohol.

Any help in this issue would be much appreciated and it sure would
make me
look good to those mid-level managers as well. I have used this
forum
numerous times in the past and you folks have always come through
to
enlighten my brain and boost my intellect (at least in the eyes of
those
managers).

So thanks in advance and I hope you make me look good yet again!

Version: Excel 2003 SP2

 

[Roger Govier]

Thanks Luc.

Have to go out for the day now, and most of tomorrow, but may get time
to play with it at the weekend.
I added a
=SUBSTITUTE(extractUsers(a2),"*","")

Not sure whether the real data had names that the OP blanked out with
"*", or whether it was part of the format.


--
Regards

Roger Govier

LOL
Not easy to see what the Replace method does...
I suggest you play with the Execute method to learn how regular
expressions
match...

By the way, my little function will probably leave a "," at the end of
the
users list with "real" data...

--
Regards,
Luc.

"Festina Lente"

Thank you Luc, that fixed it.

Now, all I have to do is try and understand it!!!!!

--
Regards

Roger Govier

Sorry, I should have mentioned it.
You need to add a reference to "Microsoft VBScript Regular
Expressions
5.5"
You do it through "Tools menu -> References..." in the VBE
window...
--
Regards,
Luc.

"Festina Lente"


:

Hi Luc

I have never used RegExp before and thought I would give this a
try.
I get a compile error - User defined type not defined as soon as I
try
to run it.

Could you please tell me what I need to do to make it work?

--
Regards

Roger Govier


In a module:

Function extractUsers(s As String)
Dim re As New RegExp

re.Global = True
re.Pattern = "CN=([^,]+,?)([^;]+?"
extractUsers = re.Replace(s, "$1")
End Function

In B2:
=extractUsers(a2)

--
Regards,
Luc.

"Festina Lente"


:

One of my Administrator types is dumping information from AD so
some
mid-level manager can justify his position and salary (too
sarcastic?? ;-)..
anyway.. AD dumps very verbose information into his cells.

Example: In cell A2 you have...

CN=Daniel ****,OU=Desktops,OU=Users,OU=Executive
Offices,OU=FCC,DC=Sugar,DC=corp;CN=Rich
******,OU=Laptops,OU=Users,OU=Service
Center,OU=FCC,DC=Sugar,DC=corp;CN=Charles ******... and on and
on.

(names changed to protect the innocent)

these text stings get very long.. some even hit the character
limit
for
cells.. but all that my guy wants are the names of the users,
which
of course
are represented after the "CN=" and ends with the next ","

Desired Result: In cell B2 show...
Daniel ****, Rich ******, Charles ******

I can easily pull "Daniel ****" using text functions but I lose
it
when I
have then find Rich, and Charles, and Tom, Dick and Harry..
until
somewhere,
someplace it all ends with the LAST name which could be 7
names,
32
names, or
124 names.

So off to the Excel Discussion Groups to nab.. Do'h... 'use'
some
VBA
from
someone who really knows what they are doing.

I have been searching through your forums, reading VB questions
from
the
unwashed masses and checking out the suggestions from the
enlightend
ones.
Visited sites like contextures, peltiertech, cpearson and more.
I
have tried
to modify some code that was posted both on this forum and
others
and
learned
alot of good, although ancillary, stuff in the process.

I now post this questions to the world, as I am brain fried,
body
tired, mad
at myself for not being smarter (or better looking) and
besides, I
am
out of
alcohol.

Any help in this issue would be much appreciated and it sure
would
make me
look good to those mid-level managers as well. I have used
this
forum
numerous times in the past and you folks have always come
through
to
enlighten my brain and boost my intellect (at least in the eyes
of
those
managers).

So thanks in advance and I hope you make me look good yet
again!

Version: Excel 2003 SP2