How to add a DSQuery script to Excel

active-directorydsquerymicrosoft excel

I have to update AD with home users number, etc. I've written the script and imported the usernames and phone numbers into Excel

Script: 

dsquery user -samid "username" "OU=Sys,OU=Prod Services,DC=win,DC=ite,DC=com" | dsmod user -mobile  "999" -hometel -u XXXXXX -p XXXXXXXXX

In Excel: A1 = Username B1 = Num

How do I get the above script working so it takes the usernames and numbers from Excel and puts them into a batch file?

Best Answer

Okay, this is definitely in the quick and dirty school of IT Engineering but we've all done it and, actually, Excel is mega handy for this. I do agree with @Pauska that Powershell and CSV's are the way forwards, though.

Anyway, the quickest and easiest way to do this (For a one off job, anyway) is to organise your excel sheet as follows:

     Cell A        Cell B       Cell C
  +------------+------------+------------+
1 +  Username  +  Telephone +     Fax    +
  +------------+------------+------------+
2 +   User1    +123456789012+987643210987+

And so on. Then, in column D, on Row 2 create a formula that uses concatenation to build your individual command line, ending with something along these lines:

="dsquery user -samid '" & A2 & "' 'OU=Sys,OU=Prod Services,DC=win,DC=ite,DC=com' | dsmod user -mobile '" & B2 & "'"

You can then select that whole column, copy and paste it notepad and voila - a batch file which will do what you need.

Related Topic