OK so I finally got it working. I've been testing it over a VPN so its been fairly slow but not too bad. So I assume running on the LAN it wont take too long but cant make any promises (especially if you have over 2000 users for it to query).
This is probably going to seem quite long winded and complicated but trust me its not, its just spaced out and full of explanations :)
I decided to use an Excel file like you so that it was easier for you to use, however this does mean that to be able to use my code your going to need to install the Microsoft Office 2003 PIAs (pretty small install) from here:
http://www.microsoft.com/downloads/d...displaylang=en
My spreadsheet looks like this:
http://www.vbforums.com/
Hopefully its pretty similar to yours.
Anyway, once you've installed the PIAs, just open up a new project in VB 2008/2005 and leave the initial form name as Form1 and add a reference to
System.DirectoryServices and
Microsoft Excel 11.0 Object Library then just copy and paste this into your form code (overwritting anything thats already in there).
Read the explanation below the code before using this code on your spreadsheet!
vb Code:
Imports System.DirectoryServices
Imports Microsoft.Office.Interop
Public Class Form1
Private Sub Gobtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Gobtn.Click
Try
Dim RootEntry As New DirectoryEntry("LDAP://OU=UsersOU,dc=yourdomain,dc=local", "domainadmin", "password") '### CHANGE ###
Dim AccountSearcher As New DirectorySearcher
Dim UserList As New Collections.ArrayList
Dim excelapp As New Excel.Application
Dim UserSheet As Excel.Worksheet = excelapp.Workbooks.Open("C:\Userlist.xls").Worksheets("Sheet1")
Dim Counter As Integer = 0
Dim Foundlist As New Collections.ArrayList
For i As Integer = 3 To 8 '### CHANGE ###
UserList.Add(UserSheet.Cells(i, "B").Value & UserSheet.Cells(i, "C").Value) '### CHANGE ###
Next
With AccountSearcher
.SearchRoot = RootEntry
.Filter = "(ObjectClass=User)"
.PropertiesToLoad.Add("sAMAccountname")
.PropertiesToLoad.Add("givenName")
.PropertiesToLoad.Add("sn")
.PageSize = 1000
End With
'Loop through each account found by the ADsearcher
For Each account As SearchResult In AccountSearcher.FindAll
'Store the current first and last name so we dont have to keep fetching them
Dim CurrentGN As String = account.GetDirectoryEntry.Properties("givenName").Value
Dim CurrentSN As String = account.GetDirectoryEntry.Properties("sn").Value
'Check to see if first and last name attributes are emtpy
If CurrentGN <> Nothing AndAlso CurrentSN <> Nothing Then
'If the first and last name are not empty then we store the username
Dim CurrentSAM As String = account.GetDirectoryEntry.Properties("sAMAccountName").Value
'Loop through every name in the excel file and compare it with the current account's first and last name
For i As Integer = 0 To UserList.Count - 1
If UserList(i).ToString = (CurrentGN & CurrentSN) Then
Counter = i + 3 '### CHANGE ###
If Foundlist.Contains(CurrentGN & CurrentSN) Then
'If we already searched for this first name and last name then highlight the cell yellow
UserSheet.Cells(Counter, "D").Interior.ColorIndex = 36 '### CHANGE ###
End If
UserSheet.Cells(Counter, "D").Value = CurrentSAM '### CHANGE ###
Foundlist.Add(CurrentGN & CurrentSN)
RichTextBox1.AppendText("Found Username " & CurrentSAM & " for " & CurrentGN & " " & CurrentSN & vbNewLine)
Application.DoEvents()
End If
Next
End If
Next
'Show alert when finished looping through users
MessageBox.Show("Processing Complete", "Complete", MessageBoxButtons.OK, MessageBoxIcon.Information)
'Save and close Excel file
excelapp.Workbooks("Userlist.xls").Save() '### CHANGE ###
excelapp.Workbooks.Close()
'Alert if error occurrs
Catch ex As Exception
MessageBox.Show(ex.Message, "Oh Dear...", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
'Clean up by killing all Excel processes
For Each excelproc As Process In Process.GetProcessesByName("EXCEL")
excelproc.Kill()
Next
End Try
End Sub
End Class
As you can see I've added a few comments in there but not really enough to fully explain whats going on.
Basically, if you arent that bothered about understanding it and just want it to work then the bits you need to change have all got a comment on that line saying
'### CHANGE ### so hopefully you can spot them.
Basically, you need to change the domain and OU its looking in and the location/name of the Excel file. These should be fairly obvious to you, but the bits that might not be so obvious are the counter values. I'm sure there will be a better way of doing it but all I am doing in this line:
vb Code:
For i As Integer = 3 To 8 '### CHANGE ###
is saying loop through rows 3 to 8 in the excel document. You can see the column letters in the line below that one, so obviously change both of these to suit your spreadsheet.
Essentially all you need to do is go through the code and look for any lines with ###CHANGE### at the end of them and alter the column name or row numbers to reflect what is in your spreadsheet.
This line:
vb Code:
Counter = i + 3 '### CHANGE ###
just needs changing to i + whatever number of rows down from 0 your first user's name is. As you can see from my spreadsheet screenshot, my names start on row 3, hence the i + 3.
Make a backup of your spreadsheet first just in case you miss one of the values off or get it wrong etc.
As we mentioned earlier, there is no way for the program to determine which names match which username in the event of two names being the same so the best I could do was alert you to which names have been querried more than once so that you know one of them is incorrect. I do this in this line:
vb Code:
'If we already searched for this first name and last name then highlight the cell yellow
UserSheet.Cells(Counter, "D").Interior.ColorIndex = 36 '### CHANGE ###
So any usernames that might not be correct will be highlghted in yellow. Any usernames that could not be found will just be left blank. You can see this in my final output spreadsheet here:
http://www.vbforums.com/
Oh and you will also need a RichTextBox control on your form named RichTextBox1 as I use that to output the progress of the process (as you can see in the screenshot further down).
Make sure you havent got any other excel documents open when you run this because at the end it will terminate all EXCEL.EXE processes :)
I designed the GUI like this just so it was usable:
http://www.vbforums.com/
Obviously I've blanked out the real names and usernames but you get the idea. The progress log gets updated each time a new username is added to the spreadsheet, I just thought it would be a good idea so that you could see how far it had got or if it was going wrong etc.
I hope that helps! It better do after it took me nearly 2 hours :P haha
Oh yeah, its not designed to be run more than once without being closed and opened again - Yeah I know thats pretty naff but its getting late and I figured you could just implement some "clean up" code yourself :)
PS anyone else feel free to correct my code if I have done anything wrong or if you have any better ideas :) but it all worked fine when I tested it so hopefully its all good.
Chris
