+ Reply to Thread
Results 1 to 8 of 8

Active Directory Query

  1. #1
    Registered User
    Join Date
    07-31-2019
    Location
    Minneapolis, Minnesota, USA
    MS-Off Ver
    2016 x64
    Posts
    4

    Active Directory Query

    Greetings,
    My manager wants departing employees at my company to be tracked in a specific format via excel document. Instead of manually typing everything in, I'd like to wow this person by using a query from active directory in microsoft excel to pull this information.
    My current testing query is set up so that when I enter the employee ID number in column A, there are serveral VLOOKUP formulas used to populate subsequent columns in the same row with employee first and last name, office location, computer serial number, etc. All this data is being pulled from a separate sheet within the same excel document, where my active directory query data resides.
    The problem I'm encountering while tinkering with this functionality is that once an employee leaves and a few weeks have elapsed, the active directory object is deleted and I'm left staring at a blank row where my excel data USED to be.
    Can anyone shed some light on what I'm doing wrong here? I'd like data to remain in my sheet even if an active directory user object is deleted.
    Thank You for any tips/pointers.
    Anthony
    Last edited by anthonyme12; 07-31-2019 at 09:28 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Active Directory Query

    Once you have completed the Lookup, immediately copy the the row of data and then paste special values to the same location.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    07-31-2019
    Location
    Minneapolis, Minnesota, USA
    MS-Off Ver
    2016 x64
    Posts
    4

    Re: Active Directory Query

    Sounds good, Alan, thank you for your suggestion. I had originally hoped to set the query so that it would automatically update ("refresh") upon opening the document. But your idea makes sense. I can just as easily work with this method.
    I'd like to allow time for any other suggestions here prior to marking your idea as the solution. I'm new to forums in general so I do apologize if that's not standard protocol.

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Active Directory Query

    This can be automated with a Worksheet Change Event in Visual Basic if you would like. I'm headed off on some errands and won't be back for several hours. If this is something you are interested in and it is not marked solved when I return, I will compose some code for you to automate this.

  5. #5
    Registered User
    Join Date
    07-31-2019
    Location
    Minneapolis, Minnesota, USA
    MS-Off Ver
    2016 x64
    Posts
    4

    Re: Active Directory Query

    Sure, Alan, I'd be up for that as your time allows!

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Active Directory Query

    Here is a bit of code that should do this for you.
    Please Login or Register  to view this content.
    Open the VBE (Alt + F11)
    In the left window, click on the sheet that you have the lookup in.
    The right window will open
    Copy the above code and Paste into the right window.
    Close and Save.

    To run this. After running the lookup, double click on any cell in the row and it will immediately copy and paste special as values and remove any formulas.

    You will need to save the workbook as an Macro enabled file .xlsm or .xlsb

  7. #7
    Registered User
    Join Date
    07-31-2019
    Location
    Minneapolis, Minnesota, USA
    MS-Off Ver
    2016 x64
    Posts
    4

    Re: Active Directory Query

    Looks great, thank you for sharing your knowledge with that snippet of code! BTW, I like your profile avatar (beating a dead horse)!
    Last edited by anthonyme12; 08-01-2019 at 09:03 AM.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Active Directory Query

    Thanks for the Rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help in getting the expiryDate from Active Directory.
    By mukund289 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-14-2014, 08:01 AM
  2. Replies: 0
    Last Post: 09-05-2013, 10:00 AM
  3. [SOLVED] Active Directory Last Log On Macro
    By Andrew.Trevayne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-11-2012, 08:23 AM
  4. Active Directory Query with Macro
    By Andrew.Trevayne in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-06-2011, 02:41 AM
  5. Active Directory Help
    By Macdave_19 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-26-2011, 07:27 AM
  6. Get User Name - Active Directory
    By jai2808 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-2007, 05:20 AM
  7. Active Directory References? help...
    By Judith in forum Excel General
    Replies: 1
    Last Post: 10-12-2006, 04:14 PM
  8. [SOLVED] Reading Active Directory from VBA?
    By Linn Kubler in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-06-2006, 04:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1