Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract all CN='Name' from a Active Directory data-dumped-cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract all CN='Name' from a Active Directory data-dumped-cell
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract all CN='Name' from a Active Directory data-dumped-cell
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 -- Regards, Tom Ogilvy "mateo561" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract all CN='Name' from a Active Directory data-dumped-cell
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/archiv.../t-309184.html NickHK "mateo561" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Data From Active Directory | Excel Programming | |||
Data Connection - Active Directory | Excel Discussion (Misc queries) | |||
How to extract just the Column Name of the active cell | Excel Discussion (Misc queries) | |||
active directory? | Excel Programming | |||
Active Directory | Excel Programming |