Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: VBA Active Directory Query and export questions

    Hi, I've been assigned a task in which I have to export the First and Last names of a specific department in my companies active directory to an excel spreadsheet. Id preferably need both of them in the same cell with a space in between. I've looked a lot of places online but usually most code snippits offer exporting groups and I don't know how to manipulate that to export departments.

    Any help would be much appreciated!

    Thanks in advance.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You question is a little ambiguous, but to return the FrstName and LastName with a space in one cell (I assume that other data will be imported into other cells?), then the query for the export data should have the name concatenated together something like this

    SELECT FirstName & " " & LastName as [Employee Name], OtherFile1, OtherFiled2 FROM YourTable

    Dores that help?

    MTB

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    Quote Originally Posted by MikeTheBike View Post
    Hi

    You question is a little ambiguous, but to return the FrstName and LastName with a space in one cell (I assume that other data will be imported into other cells?), then the query for the export data should have the name concatenated together something like this

    SELECT FirstName & " " & LastName as [Employee Name], OtherFile1, OtherFiled2 FROM YourTable

    Dores that help?

    MTB
    Thanks for the quick response Mike,

    That definitely helps but I'm also looking for the query portion of the code. I have the DN for my domain so how would I use that to get the First and Last names of a specific department in my Active Directory. I also don't need any other fields exported so if I can just get those two that would be great, if that's not possible I can work around it as well.

    Also any suggestions on how to make this question less ambiguous, is there any specifics that I need to put into it to make it more clear? Here I'll try to explain exactly what I'll be using it for so that might help clear it up as well:

    I need to make a form in excel where one of the fields will have a drop down list of all the "Electrical Engineers" in my workplace, so that a project manager can pick the person that is working on the particular assignment. My boss wants this list to be queried from the active directory and not just a simple list because he doesn't want to update it when we lose or hire employees.

    Let me know if that makes a little more sense now.

    Thanks again.

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The attached demo files, 1 Access and 1 Excel (2003) file, may assist you in developing your solution. I tested it with the AC file stored in c:\Temp.
    The application includes an Excel form with code that retrieves the dept. name, and first and last names from the database. The information is copied
    to an empty worksheet, then the employee names are concatenated, and the list is sorted by dept. and last & first name. The form presents the list of
    departments in a combo box and employee names in a list box. The list box is updated with a change to the combo box.
    Jerry
    Code:
    'Module1 Code:
    Sub Get_Names()
    'retrieve department and employee names from data base.
    'concatenate last name with first name in worksheet, sort by dept and last name.
    Dim sQRY As String, strFilePath As String, tblName As String
    Dim destSheet As String, currSheet As String
    Dim i As Long
    Dim conn, rst
    
        strFilePath = "c:\Temp\dbEngineers.mdb"
        tblName = "Table2"
        destSheet = "Data"
        
        currSheet = ActiveSheet.Name
        
        Set conn = CreateObject("ADODB.Connection")
        Set rst = CreateObject("ADODB.Recordset")
        
        conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & strFilePath & ";"
        
        sQRY = "SELECT First_Name, Last_Name, DEPT FROM " & tblName
        
        rst.Open sQRY, conn
        
        Sheets(destSheet).Cells.ClearContents
        Sheets(destSheet).Range("A1").CopyFromRecordset rst
        
        rst.Close
        Set rst = Nothing
        conn.Close
        Set conn = Nothing
        
        i = 1
        Do While Sheets(destSheet).Cells(i, 1).Value <> ""
            Sheets(destSheet).Cells(i, 4).Value = _
                Sheets(destSheet).Cells(i, 2).Value & ", " & _
                Sheets(destSheet).Cells(i, 1).Value
            i = i + 1
        Loop
        
        Application.ScreenUpdating = False
        Sheets(destSheet).Select
        Range("A1:D1").End(xlDown).Select
        Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Key2:=Range("D1") _
            , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
            False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
            :=xlSortNormal
        
        Range("A1").Select
        Sheets(currSheet).Select
        Application.ScreenUpdating = True
        
    End Sub
    Code:
    UserForm1 Code:
    Private Sub UserForm_Activate()
    Dim rowCount As Long, i As Integer
    Dim srceWks As String, deptName As String, saveValue As String
    
        srceWks = "Data"
        
        Call Get_Names
           
        rowCount = Sheets(srceWks).UsedRange.Rows.Count
        If rowCount = 0 Then
            MsgBox "An error occurred retrieving names from the database." & vbCrLf & _
                "Please contact your administrator."
            Exit Sub
        End If
        
        'populate combo box with department names
        For i = 1 To rowCount
            deptName = Sheets(srceWks).Cells(i, 3).Value
            If deptName <> saveValue Then Me.ComboBox1.AddItem Sheets(srceWks).Cells(i, 3).Value
            saveValue = deptName
        Next
        
        Me.ComboBox1.ListIndex = 0
        
        Call Pop_ListBox
        
    End Sub
    
    Function Pop_ListBox()
    'populate the list box with names assigned to the department selected in the combo box
    Dim srceSheet As String, deptName As String
    Dim i As Integer, numRows As Long
    
        srceSheet = "Data"
        numRows = Sheets(srceSheet).UsedRange.Rows.Count
        deptName = Me.ComboBox1.List(Me.ComboBox1.ListIndex)
        
        Do While Me.ListBox1.ListCount > 0      'empty the list box
            Me.ListBox1.RemoveItem (0)
        Loop
        
        For i = 1 To numRows
            If Sheets(srceSheet).Cells(i, 3).Value = deptName Then
                Me.ListBox1.AddItem Sheets(srceSheet).Cells(i, 4).Value
            End If
        Next
         
        Me.cmdCancel.SetFocus       'remove the focus from the combo box
        
    End Function
    Attached Files Attached Files
    Last edited by JerryDal; 03-19-12 at 12:41.

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    Hey Jerry,

    Thanks for that code and file. That is a huge step for me in the right direction, now all I need is the code to access (query) my Active Directory database. I'll continue trying my luck with it and looking around online as well. Thanks again though, shouldn't be to difficult from this point (I hope haha).

  6. #6
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The Excel 2003 query code I provided in module1 queries the database. You would change the database path and name, table name and field names
    to adapt the code to your application. Also have a blank worksheet available in the workbook to hold the imported fields. Change the code, where I've
    specified "Data" as the worksheet name, to whatever worksheet name you want to use.

    If your application needs to do a query after selecting a name from the list box, I would start with creating a query in Access, look at that query in
    SQL mode, and see if that can be put into a SQL statement in the Excel file (like what is done in module1), which is:
    Code:
    sQRY = "SELECT First_Name, Last_Name, DEPT FROM " & tblName
    The form would have another command button, I'll call "Report", that would be disabled until a name is selected from the list box. Or you could have
    the button always enabled and pop up a message box if it is clicked before a name is selected. After a button-click, you get your report, which you
    design in code how you want it presented.
    Some of the report possibilities are:
    1) if the report has a relatively small fixed length, display the information in the same form, UserForm1, which could be made larger
    - it is possible to have a panel of information displayed in a large label that covers (hides) controls on UserForm1. When using the
    [Report] button, the label caption is assigned the report text and the label is made visible. The label click-event is used to hide
    the label when done viewing the report.
    2) display in a new form
    3) put the report in its own workbook
    4) there is also a way to present the report in Print Preview. This requires closing UserForm1 and opening Print Preview from the code
    behind the button that opened UserForm1. In the code, a sheet dedicated to display reports would be tested to see if there is any data,
    and if there is, display the sheet in Print Preview. Prior to opening UserForm1, all cells in the dedicated reporting sheet would be cleared.

    I hope this helps. Everything I've suggested is done in code. If the above applies to your situation and you decide to use any of my suggestions,
    post back if you encounter any roadblocks in developing you code.
    Last edited by JerryDal; 03-19-12 at 14:01.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •