Combine Multiple Excel Spreadsheets

I create a lot of Excel audit reports in a multi-domain environment.  When they go out, I need to combine the reports from each domain into a single Excel workbook with multiple worksheets.  I found some VBA code on mrexcel.com which was pretty easy to port to vbscript.  I put the script on the desktop and then select all of the files which I want combined, and drop them onto the script.  A new workbook opens with each of the original files as a worksheet page.  The original files are not deleted.  Note that if you line the files up in the order you want them before selecting them, you will get them in the order you want.  I hope you find CombineXLSheets.vbs makes this task a little easier. Script Text

Remote Windows Update 3.1

WindowsUpdate.hta version 3.1 is an HTML application which allows you to connect to a remote machine, determine what patches it requires from Windows Update, and install the patches.  You can schedule a reboot time. This version allows you to look at he Windows Update log, and the log created by the program itself.  There is a button to allow you to change the update source to windowsupdate.com, which is helpful in places where WSUS or SUP is not working properly.  You can install all security patches, or select patches individually.

HTA files are best run from your local drive. Version 3.0 was released in 2011, version 3.1 only changes the background color to blue.  The transition color method I had used for the background is no longer supported in IE, and the program appeared to be broken.

Change _hta.txt extension to .HTA.
Script Text

Delete Inactive User Profiles with PowerShell

Written to replace DelProf, this script deletes inactive user profiles from a local or remote computer.  It supports arguments by position, and has a test parameter.  If you run it locally, you may supply host name, localhost or “.” The logfile is tab delimited, you may use XLS extension to open in Excel.  If you run the script interactively the log opens when the action is complete.  This is written to be loaded with “dot sourcing”, ex:

PS C:\>. "c:\mypsscripts\Delete-InactiveProfiles.ps1"
									

After you have loaded the module you can use Get-Help Delete-InactiveProfiles.ps1 for more information on syntax.

Delete-InactiveProfiles.ps1 is heavily commented so you can see what it is doing.  I am using WMI to get the list of local users, and query NTUser.DAT for last logon time.  Remember to rename from Delete-InactiveProfiles_ps1.txt to Delete-InactiveProfiles.ps1.

Open a Hyperlink URL from Clipboard

I write scripts when I see a way to automate a task that I find myself doing repetitively.  Because I force email to text, I frequently get emails with URLs that have a line break.  Occasionally they will have a space, tab or even a greater than sign “>” if forwarded from another source.  I found myself highlighting the address then pasting it into notepad, removing the junk, recopying and then launching the browser.  Truly annoying.

Back in 2004, Scripting Guy Ed Wilson write a script to launch a URL from the clipboard, but it did not clean up the text.  I also thought it would be nice to avoid the Internet Explorer clipboard warning when possible.  One of the things I wanted to be able to determine was the default browser.  Before Windows 8, you could find this in “HKCR\http\shell\open\ddeexec\Application\”. Finding the entry in Windows 8 had me stumped.  Ed and Jason (a PFE at Microsoft) located it here: “HKCU\Software\Microsoft\Windows\Shell\Associations\UrlAssociations\http\UserChoice\ProgId”

The resulting script, OpenClipboardURL.vbs,  is way too big for the simple task.  But it will take advantage of Word’s clipboard if installed, and understands alternate browsers.  I use it every day, putting it into Alan’s Favorites.

Convert data from Get-ADUser and Get-ADComputer for Export-CSV

If you have been following my blog, you know that I am an experienced vbscripter, but am relatively new to PowerShell.  I was excited that my long vbscripts to export data from Active Directory would be now one liners which I could send to a CSV file with the Export-CSV cmdlet.   Unfortunately, I found that getting the data I wanted was not as easy as I expected.  There is no automatic way of handling the different date strings in Active Directory.  And, although some of the data looked okay when output to the screen, values stored other than a simple string often gave me results like “Microsoft.ActiveDirectory.Management.ADPropertyValueCollection”.

Because of these issues, I set out to create my first PowerShell function.  My goals were to write something that I could use every day which would take the output of Get-ADUser,  Get-ADComputer and other Get-AD* cmdlets within the ActiveDirectory module and convert the data to strings which would properly output to Export-CSV.  As far as I can tell from my internet search, no one has written such a PowerShell function.   And, of course, the best way to learn a new programming language is to have a real project instead of just exercises.

I had some help with direction by the Scripting Guy himself, Ed Wilson.  I have been truly fortunate to be a member of a PowerShell user group with him as a member and regular speaker.  (For more about Ed and the group, read my previous post.)  Some of my discussion with Ed led to Scripting Guy articles about Active Directory and Export-CSV. Some of the code he sent me prior to the publication of these articles sent me on the right track to create Convert-ADValues.ps1.

Convert-ADValues pre-processes the output of the AD cmdlets, such as Get-ADUser and Get-ADComputer so that the output works with Export-CSV.  All dates appear properly.  ProxyAddresses and PostalAddress appear properly.  Binary data appears a a comma delimited string.

Example 1:
$u = Get-ADUser -Filter {surname -eq “smith”} -properties *
Convert-ADValues $u

Example 2:
$u = Get-ADUser -Filter {surname -eq “smith”} -properties *
$u | Convert-ADValues | Export-CSV -notypeInformation -Path $env:userprofile\desktop\ADInfo.csv

Example 3:
Get-ADComputer -Identity $env:COMPUTERNAME -Properties * |Convert-ADValues

To learn about running scripts and “dot sourcing”, examine the basics here.  I hope the comments within the script are adequate to explain what is being done.  Don’t hesitate to comment or email me with questions.

Changelog:
Updated 8-6-13. I fixed handling of binary, null and empty values, arrays in item, and changed delimiter to semi-colon for values with commas.  And yes, it isn’t pretty.
Updated 1/1/14. I added a switch for enumerating ACLs for the nTSecurityDescriptor attribute, changed names of variables away from users, improved comment based help.
Updated 1/2/14   Removed write-host of Security type added while debugging. Added Online Help.  Moved Load-Module ActiveDirectory to Begin statement, clarified Help to make clear that you must return nTSecurityDescriptor for it to be expanded with -GetSecurity. Removed return of PropertyNames and PropertyCount unless you use ReportPropertyNamesAndCount parameter. Changed output type to PSObject from Array. Added Requires statement for Version 3.

Get User Information

NTUserInfo.vbs is a script based on Ralph Montgomery’s NTUser.wsf file. It gives a good summary about a user account, including user name, description, password status, and more.   I have made a number of changes, including the ability to copy the results to the clipboard using IE.  Rename from .txt to .vbs. New version 10/23/2009 allows entry of user name by samaccount name or UPN.   This entry was originally posted on March 23, 2009.  The new (3/3/13) alternative version, NTUserInfo_IE.vbs outputs to IE instead of a MSGBox.  This way you can copy the information to your clipboard.