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

That VbScript worked in XP, Why Not Now?

Do you have a script that worked with your x32 XP computer, but no longer works in Windows 7 or 8 x64?  I am finding that some of the scripts I wrote have this problem.  Somethings may rely on an ActiveX control that was native to XP but not a later version.  What I am finding is that I have some scripts, such as this fragment fail:

Set OConnMDB = CreateObject("ADOX.Catalog")
 	OConnMDB.Create _
 	    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
 	        "Data Source = " &dbName 
 	Set OConnMDB = Nothing

A quick fix for this is to run the script with the X32 version of cscript or wscript.  You can try running the scripts with c:\windows\SysWow64\cscript.exe.  Otherwise, back to the debugger.

Delete a List of Computer Accounts from Active Directory

Although I have spent most of my time recently writing PowerShell code, I still get requests from the field for vbscripts.  The security model differences make it more time consuming to explain how to run a PowerShell script than vbscript’s “double click this”.  DisablePCsFromList.vbs is an updated version of a 2006 vbscript which reads a text file with a list of computer accounts, and deletes the list.  A log file is written to the user desktop.

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.

Manage Nework Recycle Bin Size

If you redirect the Documents folder (or My Documents on XP) to a network drive, an instance of the Recycle Bin is created on the user’s home directory.  And, because the default size for the Recycle Bin is 10% of available space, you can end up with a huge amount of space chewed up by these hidden folders.

NetBinCleanup.vbs is based on a PowerShell script (http://baldwin-ps.blogspot.com/2013/07/empty-recycle-bin-with-retention-time.html).  I decided to use it as an guideline for a vbscript which would only delete aged items on network based Recycle Bins.  Why vbscript?  Because in my environment, vbscript is easier to deploy an run on all computers than PowerShell.

There are some interesting things in the script, such as the StripHigh function which removes high ASCII and strips Unicode from a string.  The author of the PowerShell script suggest running the cleanup at logoff, but I suggest running it at logon with the command start /b cscript.exe netbincleanup.vbs.  If you set this as a logoff script, you will may delay logoff for an annoying amount of time.

I have hardcoded the retention days at 7 days as a constant in the script.  There is also a test variable which you can set to popup the results of the script.  I am sure that you would never put into production something you had not read an tested — so I put a lot of comments in this file to make clear what is going on.

Note:  The original version only deleted files.  The updated version of  NetBinCleanup.vbs adds deletion of recycled folders, too.

Another User Export Script

I have already written a script to dump user information into a spreadsheet here, so why do it again?  User AD Export.vbs is different in that it uses the modern LDAP interface (instead of the lame WinNT interface I used earlier), and is therefore much faster.  It was written for a user audit, so it does not export things like phone numbers and addresses, but you do get the user’s name, display name, description, mail address, whether a smart card is required, SamAccount name, UPN, whether the account is disabled,  the date created, date password last set, last logon, password age, expiration date, and AD path.

It will allow you to navigate to the starting OU in any domain in your forest.  It does not require elevated rights to run, and exports the data to Excel.

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.