Converting Dates in Excel 2010

So, you have dates that are formatted M/D/YYYY (month/day/year) and Excel thinks they're text? There MAY be a better way to do this but Excel 2010 help is seriously dysfunctional so I came up with this.

So, a quick formula to convert them to values is:

=DATE(RIGHT(B2,4),LEFT(B2,FIND("/",B2)-1),MID(B2,FIND("/",B2)+1,FIND("/",B2,FIND("/",B2)+1)-(FIND("/",B2)+1)))

=DATE( 

YYYY >   RIGHT(B2,4),

MM >     LEFT(B2,FIND("/",B2)-1),

DD >     MID(B2,FIND("/",B2)+1,

             FIND("/",B2,FIND("/",B2)+1)-(FIND("/",B2)+1)

         )

)

The Year: The date (i.e. "1/24/1945") is recognised not as a date, but a text value. To get the year we grab the rightmost 4 digits and capture that as the first parameter in the DATE() function, 1945. That was easy.

The Month: This is nearly as easy, but we use FIND() to determine where the first slash is we use the FIND() function, including the parameter "/" (slash) and the source cell (B2), we subtract one (1) because we don't want the slash included in what we're enumerating. Then we use LEFT() to take what we need from the beginning (left-side) of the text string. as per the above date example, the month would be 1.

To get the day we use MID() because we need to grab a MIDdle segment of the string, we will use FIND() recursively to determine where to start the text segment, then again to find the second slash (starting after the first slash) then determine how many characters to capture by finding the second slash, then subtracting the two positions. Using the example above the day would be 24.

** Source cell in this example is B2

The Poor Man's Site Management Tools

If you're an "I.T. Guy" you know about PS Tools from SysInternals (now part of Microsoft). These tools offer great power for remote administration and with great power comes great responsibility so please be aware of whose life and livelihood you're affecting when you perform remote tasks. There are some built-in tools that can make life easy, as long as you've prepared for it.

To prepare for this, your PCs should be similar, if not the same. The consistency of the PCs is a huge benefit, but don't let minor variations bother you, simply ensure that the account (AD account and local support account) are available and consistent across your organisation. I don't recommend giving your helpdesk staff "local admin" capabilities, but find that giving them an alternate account with super-powers that is not their default login is best. For example, John Doe's account is jdoe, but his admin account may be a-jdoe. Only the "a-" User ID has the abilities to add a computer to the domain, and is the local admin for user PCs. Separating this out offers some degree of auditing and not sharing passwords on these premium service accounts.

Generic accounts are NEVER preferred. Your account is your identity, you should never impersonate anyone, nor should you share your identity (user ID and credentials) with others.

Okay, sorry about the security lecture.

What I want to show you is how I use Windows tools to gather and distribute information to people across my office.

Collecting PC names:

@echo off
SET IPRoot=172.25.117.
SET iSubNet=1
IF NOT EXIST newPCList.csv ECHO "COMPUTERNAME", "MAC Address", "IP Address" > newPCList.csv
IF NOT EXIST newPCList.txt ECHO COMPUTERNAME > newPCList.txt
IF NOT EXIST newPCList.txt ECHO ============ > newPCList.txt
:START
SET /A iSubNet=%iSubNet% + 1
Echo Checking IP [%IPRoot%%iSubNet%]:
SET IPAddress=%IPRoot%%iSubNet%
NBTSTAT -A %IPRoot%%iSubNet% > fpcdata.txt
::echo Err: %ERRORLEVEL%
FOR /F "tokens=1,2,3,4* delims=<> " %%a in (fpcdata.txt) do call :subPI %%a %%b %%c %%d %%e
IF /I 0%iSubNet% LSS 255 GOTO START
GOTO :END
:subPI
SET varX=%2
SET varY=%1
set varW=%3
::ECHO DATA: 1:%1 2:%2 3:%3 4:%4 5:%5
::IF "%varX%"=="20" echo COMPUTERNAME: %varY% %IPAddress%
IF "%varX%"=="20" SET CN=%varY%
IF "%varX%"=="Address" echo %CN% [%varW%/%IPAddress%]
IF "%varX%"=="Address" echo %CN%, %varW%, %IPAddress% >> newPCList.csv
IF "%varX%"=="Address" echo %CN% >> newPCList.txt
::IF "%varX%"=="Address" pause
goto :eof
:END
goto :eof

This .cmd (batch) file scans the IP Address range my PCs are on, collects some essential information from the NBTSTAT.EXE program, and creates a .txt file and .csv listing the PCs found. This expects the PCs to be on, of course, but the result is a fairly accurate count of PCs on the network and a workable list to go from.

So, I have a list of PCs. What do I do now?

Having a list is not the end of it, in fact you may need to run that routine over a few days then remove the duplicates from the list to get an accurate picture. Either way, after the list is complete you need to be able to work with all of those PCs. I have a batch file for that:

@echo off
SET target=\\%1\C$\ITAdmin\
FOR /F "skip=1" %%a in (newPCList.txt) do call :updatePC %%a
goto :eof
:updatePC
IF NOT EXIST \\%1\C$ GOTO :PCNotFound
IF EXIST \\%1\C$\Users GOTO :Win7
:WinXP
echo %1 is WinXP
goto :doUpdate
:Win7
echo %1 is Win7
goto :doUpdate
:doUpdate
IF NOT EXIST %target% MKDIR %target%
IF NOT EXIST %target%scripts MKDIR %target%scripts
ROBOCOPY deployme\setup %target%setup /XO /E /Z /MIR /R:3 /W:5
ROBOCOPY deployme\scripts %target%scripts /XO /E /Z /MIR /R:3 /W:5
REG ADD \\%1\HKLM\Software\Microsoft\Windows\CurrentVersion\Run /v PCInventory /f /d "C:\ITAdmin\scripts\WritePCInfo.vbs"
ECHO %DATE% %TIME%> %target%LastUpdate.txt
goto :eof
:PCNotFound
echo %DATE% %TIME%, %1 >> PCFailed.LOG
echo PC inaccessible: %1
goto :eof

(more to come...)

Streamlining The Logon Script

I am a firm believer in using groups to define rights and access, some people go over the top on this because they lack logical thinking, or the time to determine the logic required, but rights and access should ALWAYS (read as 'as much as possible) be handled by group membership.

To that end whether through the GPO, or the NETLOGON method of calling a logon script, group membership can determine what resources you connect to (i.e. printers and drive letters), as archaic as the drive letter is, it is still a logical manner for people to find their stuff. This topic may get covered by my other blog "My Life in I.T.," (http://bankingonthechaos.wordpress.com/) from a higher altitude, this blog covers what I believe is a streamlined logon script that can be implemented with simplicity and should run fairly quickly.

@echo off
:: net time /setsntp:192.168.10.99
:: net time /domain:mydomain /set /yes

cscript [full UNC path to script]LoginScript.vbs

Firstly, I tried to conform to the ideals of my current employer when I started this process, so the use of a command (.cmd) batch file is simply fluff, the commands, all two of them, might be best executed through calls from VBScript, but frankly they're remarked out ('::') anyway. Pointless lines in a pointless file. I don't have the rights to a test environment at present so I simply mimicked the NETLOGON methodology because the "powers that be" don't like to share information.

So, we can ignore the .cmd file and call cscript.exe, the command-line variant of wscript.exe, to execute the logon script.

The Script: LoginScript.vbs

' ====== Meatballs, the Logon Script in VBScript
' ====== By: C. Stevens, April 12th, 2013
' ====== Last Modified: 20130412 - evens
On Error Resume Next
set objShell = WScript.CreateObject( "WScript.Shell" )
Set objNetwork = CreateObject("WScript.Network")
Dim groupListD
Meatballs ' --------------- This is the main routine
Set objShell = Nothing
Set objNetwork = Nothing
WScript.Quit ' -------------------------------- END

This is simply clean, a good start, and a defined variable. We now define the routine I call Spaghetti, it's the substance of the meal, er, script. It's the functional part of making things easy to use, and re-use. This is what get's customized to your needs.

Sub Spaghetti
wscript.echo "-------------------------------------------"
wscript.echo "Making drives/printer connections..."
' ===== LOGIN MAPPINGS: START
' ---- DO NOT MODIFY ABOVE THIS LINE ----

 ~ STUFF ~

' ---- DO NOT MODIFY BELOW THIS LINE ----
' ===== LOGIN MAPPINGS: END
End Sub

We'll get to the "Stuff" later. This is like adding Parmesan Cheese to the meal, it's last and added to taste. What any great plate of Spaghetti needs is Meatballs! In reality you start with great meatballs, the pasta is just an excuse to have them, so you may notice we actually start by calling Meatballs, when then adds Spaghetti, or your past of choice.

Sub Meatballs
 ADSPath = EnvString("userdomain") & "/" & EnvString("username")
 Set userPath = GetObject("WinNT://" & ADSPath & ",user")
 wscript.echo ADSPath & " group membership:"
 wscript.echo "-------------------------------------------"
 If IsEmpty(groupListD) then
  Set groupListD = CreateObject("Scripting.Dictionary")
  groupListD.CompareMode = TextCompare
  For Each listGroup in userPath.Groups
   groupListD.Add listGroup.Name, "-"
   wscript.echo " " & listGroup.Name, "-"
  Next
 End if

 Spaghetti ' calls the Spaghetti routine. 

End Sub

 Meatballs' purpose is to retrieve the list of groups the user has from AD, build the list into a dictionary item called groupListD, then call the Spaghetti routine to work out the Stuff we need to connect for the user. Before we can top this meal off with cheese, we need some helpers in place, subroutines and functions that we'll use in Spaghetti to attach to the resources.

' *****************************************************
' This function returns a particular environment variable's value.
' for example, if you use EnvString("username"), it would return
' the value of %username%.
Function EnvString(variable)
  variable = "%" & variable & "%"
  EnvString = objShell.ExpandEnvironmentStrings(variable)
End Function

' *****************************************************
' This function connects a particular drive letter to the 
' specified UNC path. If the drive letter is already 
' connected to another UNC connection it will disconnect it
' and then try again using the function 'driveAlreadyConnected'
Sub ConnectDrive(driveLetter, UNCpath)
 ' Map network drive script
 driveLetter=left(driveLetter,1) & ":"
 if driveAlreadyConnected(driveLetter) then
  'wscript.echo "Already Connected to " & driveLetter
  objNetwork.RemoveNetworkDrive driveLetter
 end if
 objNetwork.MapNetworkDrive driveLetter, UNCPath, True
 if driveAlreadyConnected(driveLetter) then
  wscript.echo driveLetter & "... OK"
 else
  wscript.echo driveLetter & "... Failed!"
 end if
end Sub

' *****************************************************
' This function will check if the drive letter is in use and
' attempt to disconnect it, allowing for the desired connection
' be replace it. It is used by the ConnectDrive routine.
Function driveAlreadyConnected(strDL)
 Set CheckDrive = objNetwork.EnumNetworkDrives()
 driveAlreadyConnected = False
 For intDrive = 0 To CheckDrive.Count - 1 Step 2
  If CheckDrive.Item(intDrive) = strDL Then
   driveAlreadyConnected = True
  end if
 Next 
end Function

Don't sweat the small stuff...

The Stuff we ignored earlier fits between those two lines:

' ---- DO NOT MODIFY ABOVE THIS LINE ----

 and

' ---- DO NOT MODIFY BELOW THIS LINE ---- 

This is where the logic your organisation needs, your group memberships for the current user, will determine what resources they will be connected to. In the following example the group membership is by location. Frankly you could determine the location by IP Address if you have good information to go by, but in this example we'll use group membership. For the user's that are members of the Halifax group this is their code.

' HALIFAX
If CBool(groupListD.Exists(" Halifax Users")) then
 wscript.echo "Connecting Drive (HAL) G:, S:, KL..."
 ConnectDrive "G:", "\\halfile.mydomain.com\Groups"
 ConnectDrive "S:", "\\halfile.mydomain.com\Shared"
 ConnectDrive "K:", "\\FS01.mydomain.com\Shared"
 ConnectDrive "L:", "\\FS01.mydomain.com\Group"
end if

You can see how it works, it is downright simple. the wscript.echo is unnecessary and more for you and I reading the code, or during testing. The IF... THEN is the key to determining membership. It reads as, "if the groupListD list created earlier using Meatballs, contains the group "Halifax Users", then connect these drives."

You can add sections like this for each location, and it the beauty of Meatballs is it only checks with AD once, to grab the list of groups, the rest is simply a searching a local variable array (dictionary object).

' MONTREAL
If CBool(groupListD.Exists(" Montreal Users")) then
 wscript.echo "Connecting Drive (MTL) G:, S:, K:, L:, N:..."
 ConnectDrive "G:", "\\mtlfile.mydomain.com\Group"
 ConnectDrive "S:", "\\mtlfile.mydomain.com\Shared"
 ConnectDrive "K:", "\\FS01.mydomain.com\Shared"
 ConnectDrive "L:", "\\FS01.mydomain.com\Group"
 ConnectDrive "N:", "\\halfile.mydomain.com\Shared"
end if

' TORONTO (320 Bay Street Users)
If CBool(groupListD.Exists(" 320 Bay Users")) then
 wscript.echo "Connecting Drive (TOR) G: and S:..."
 ConnectDrive "G:", "\\FS01\Group"
 ConnectDrive "S:", "\\FS01\Shared"
end if

If you have specialty connections for certain roles that are not location based, but still based on group membership, add them in the same manner:

' ------ SPECIAL Connections
' ------ Human Resources (I: Drive)
If CBool(groupListD.Exists("Human Resources")) then
 wscript.echo "Connecting Drive H:..."
 ConnectDrive "H:", "\\FS01\HR"
end if
' --------------------------------------------- SPECIAL PROJECTS(M: Drive)
If CBool(groupListD.Exists("SPECIALProjects RW")) then
 wscript.echo "Connecting Drive M:..."
 ConnectDrive "M:", "\\FS01\CSTSpecial"
end if
If CBool(groupListD.Exists("SPECIALProjects RO")) then
 wscript.echo "Connecting Drive M:..."
 ConnectDrive "M:", "\\FS01\CSTSpecial"
end if

You can even add printers, though I do hope you have print servers in place in this case. You can make them location based selecting a local print server versus a remote one, or even the printer that's local to the site. Take some time to determine location based on other information, perhaps even the logon server and you can ensure the local printer is connected rather than one back home.

' ------ Connecting Printers -------------
' Example... These connections can take a while and slow down 
' the "logon" process.
' Note: This will not duplicate printers for the user.
objNetwork.AddWindowsPrinterConnection "\\PrintServer01\torORANGE01P"
objNetwork.SetDefaultPrinter "\\PrintServer01\torORANGE01P"

Anyway, that's my logon script in VB based on group memberships

HTML Applications, VBScript, and Includes

Yes, you can use includes in HTA and VBScript applications, and there are a couple of ways to do this, but you need to control the environment quite well for success.

First off someone very helpful (ebgreen) posted a function you can use to add an include here, and while I mean him or her no disrespect, the delusion of this being called a class is bothersome. It's an include, plain and simple, not a class. Of course, his routine is hardly plain and simple. What I have gleaned from various sources on the Internet, and created is this:

' VBScriptWithIncFileFunctionBase.vbs
Option Explicit
Dim fso, oFile, sText
set fso = CreateObject("Scripting.FileSystemObject")
IncFile "" 

sub IncFile(strFileName)
set oFile = fso.OpenTextFile(strFileName,1)
sText = oFile.ReadAll
oFile.close
ExecuteGlobal sText
end sub

Simple, but EBGreen's routine has error checking/handling. Our projects at work have employed EBGreen's function and it works well.

No matter which method you choose to employ you must pay close attention to location. If you deploy this to  a system, you'll need to be specific about where that IncFile is located and either specify it explicitly, or change to that folder to have it available when you begin this process.

Set WshShell = WScript.CreateObject("WScript.Shell") 

'Change to the Install Folder...
strSourceDir = "C:\ITSupport\Scripts\MyScript"
WshShell.CurrentDirectory = strSourceDir

Prefixing your first call to IncFile with a change of directory should set you up nicely to proceed with success. Alternatively, you can be explicit about the location of the file name specified:

IncFile strSourceDir & "\" 

Removing Firefox (BATCH)

For whatever reason, I had/have the task of removing Mozilla Firefox from our PCs in an automated way. I figured I could do it from a batch file, a .CMD file, but here's the proof:

@ECHO OFF
CLS
ECHO Checking for Firefox...
FOR /F "tokens=* skip=4 delims=" %%a in ('reg query "HKLM\Software\Mozilla\Mozilla Firefox" /v CurrentVersion') do SET DATA=%%a
IF "%DATA:~31,1%"=="." goto :longVersion
SET CVersion=%DATA:~26,5%
SET FVersion=%DATA:~26,20%
goto :versionCaptured
:longVersion
SET CVersion=%DATA:~26,7%
SET FVersion=%DATA:~26,20%
:versionCaptured

FOR /F "tokens=* skip=4" %%a in ('reg query "HKLM\Software\Mozilla\Mozilla Firefox\%FVersion%\Main" /v "Install Directory"') do SET DATA=%%a
SET IFolder=%DATA:~25%
ECHO *%CVersion%*
ECHO *%FVersion%*
ECHO *%IFolder%*
IF "%CVersion%"=="3.5.9" goto :DoNotRemove
goto :RemoveFirefox

:RemoveFirefox
echo The installed version is not approved for use in our environment.
TASKKILL /F /IM firefox.exe
CALL "%IFolder%\uninstall\helper.exe" /S
goto :eof

:DoNotRemove
echo This version is approved for use in our environment.
goto :eof

We like version 3.5.9, so we keep that version, but we need to revert or upgrade any other version. This is part of a bigger process, but...

Yesterday... Working with Dates

Recently we ran into an issue where we were looking into why the data wasn't being returned on the first of the month for a particular web report. What we found was simple bad math, but what I learned was that sometimes efficiencies are found in what might appear more complex.

Below you will find the method used. and the problem is the subtracting of one (1) from the day of the month (DAY(GETDATE())-1). On the first of the month, this would result in a ZERO (0) which is impossible and will not return a result. The same can be said for using this sort of math on the month when your month is January. Not a good plan.

/* ---------------------------------------------
 This is the method in use...
   --------------------------------------------- 
*/
SELECT DISTINCT [DateModified]
  FROM [ITDS].[dbo].[Tickets] a
WHERE
  YEAR(a.DateModified) = YEAR(GETDATE()) AND
  MONTH(a.DateModified) = MONTH(GETDATE()) AND
  DAY(a.DateModified) = DAY(GETDATE())-1
  ORDER BY [DateModified] Desc
GO

Another method is to use DATEDIFF() as a filter on the age (1=Yesterday). This is much heavier a load on the server and you might be better-off to avoid it.

/* ---------------------------------------------
 This method is inefficient and will impact 
 performance more with large tables
   --------------------------------------------- 
*/
SELECT DISTINCT [DateModified], DATEDIFF(dd,a.DateModified,GETDATE()) as Age
  FROM [ITDS].[dbo].[Tickets] a
WHERE
  DATEDIFF(dd,a.DateModified,GETDATE())=1
  ORDER BY [DateModified] Desc
GO

Now, this combination of methods, using YEAR(), MONTH(), and DAY(), with the proper calculation of Yesterday is the charm. By setting the variable at the beginning, before the query, it is calculated once and the values for Year, Month, and Day are a simple comparison rather than the more complex effort that goes into DATEDIFF() from our prior example.

/* ---------------------------------------------
 This is the method adopted...
   --------------------------------------------- 
*/
DECLARE @Yesterday as DateTime;
SET @Yesterday=DATEADD(dd,-1,GETDATE());

SELECT DISTINCT [DateModified]
  FROM [ITDS].[dbo].[Tickets] a
WHERE
  YEAR(a.DateModified) = YEAR(@Yesterday) AND
  MONTH(a.DateModified) = MONTH(@Yesterday) AND
  DAY(a.DateModified) = DAY(@Yesterday)
  ORDER BY [DateModified] Desc
GO

Another advantage of this method is that if you want to filter on last month's data rather than Yesterday's it's a few small changes.

/* ---------------------------------------------
 This is the method adopted... Last Month
   --------------------------------------------- 
*/
DECLARE @LastMonth as DateTime;
SET @LastMonth=DATEADD(mm,-1,GETDATE());

SELECT DISTINCT [DateModified]
  FROM [ITDS].[dbo].[Tickets] a
WHERE
  YEAR(a.DateModified) = YEAR(@LastMonth) AND
  MONTH(a.DateModified) = MONTH(@LastMonth)
  ORDER BY [DateModified] Desc
GO

The best scripters/programmers learn from their own mistakes as well as those others have made.


Addendum: 
There's another way to get "yesterday" that I saw, though I still believe the preferred method might be the adopted method above, where the WHERE condition would read/include:

(a.DateModified >= DATEADD(dd,-1,GETDATE()) AND a.DateModified <= DATEADD(dd,-0,GETDATE()))

The problem I see in this methodology is that you would get the last 24-period, not "yesterday" and the wastefulness of "DATEADD(dd,-0,GETDATE())" in that it really gives you nothing more that GETDATE() anyway. The result would give you, supposing that today might be August 10th, 2010 at 11:47:01 AM, August 9th, 2010 at 11:47:01AM through August 10th, 2010 at 11:47:01 AM. In effect the results could change through the day depending upon the time you ran the query. The adopted methodology would give you an absolute definition of yesterday, or last month, eliminating the time-of-day factor, which is preferred.

Adapting to the ever changing drive letters...

...by using unique volume labels.

Okay, this isn't a complete thought yet, just some ideas I've mulled over and found a solution for for myself, but feel free to adapt and play with it. The reason I came up with this is because I picked up a new toy, a pogoplug (v2) and it provides me with network attached storage of multiple USB Drives (External Hard Disks or USB Keys). That's great, and it also provides for the automated backup of files/folders on my PC to the device, so why would I want to control it any further?

Because I can. The drive letters can change from time to time, depending on the timing of this or that being able to adapt to the changing drive letter seemed like a good idea. So, I fired up Notepad++ and wrote myself a VBScript tool that can do a bunch of things, but in this instance it can locate the drive letter for the volume name and I can parse that using CScript (command-line VBScript, vs. WScript, which would run graphical). Now I first ran cscript //H:CScript to ensure that it runs like a "DOS App" but you could easily change the batch file portion of this to simply run this in cscript. The follwing is the VBScript:

Option Explicit
' Find My Drive (ListDrives.vbs)

DIM strVolume, strFound, objDictionary, objWMIService, colDisks, objDisk, strComputer
DIM flagHideList, strDrive, wshShell, wshSystemEnv, wshCurrentEnv, strSetVar
DIM i

flagHideList=false
strFound=""
strVolume=""
strComputer = "."

Set wshShell = CreateObject( "WScript.Shell" )
Set wshCurrentEnv = wshShell.Environment( "VOLATILE" ) ' PROCESS, SYSTEM, USER and VOLATILE
Set objDictionary = CreateObject("Scripting.Dictionary")
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

If Wscript.Arguments.Count = 0 Then
    ' Do Proceed as Normal
Else
    For i = 0 to Wscript.Arguments.Count - 1
  If lcase(Wscript.Arguments(i)) = "/volume" Then
            if i+1 <= Wscript.Arguments.Count then
    strVolume=Wscript.Arguments(i+1)
   end if
  end if
  If lcase(Wscript.Arguments(i)) = "/setvar" Then
            if i+1 <= Wscript.Arguments.Count then
    strSetVar=Wscript.Arguments(i+1)
   end if
  end if
  If lcase(Wscript.Arguments(i)) = "/hidelist" Then
   flagHideList=true
  end if
 next
End If

Set colDisks = objWMIService.ExecQuery("Select * from Win32_LogicalDisk")

For Each objDisk in colDisks
    objDictionary.Add objDisk.DeviceID, objDisk.VolumeName
 if NOT flagHideList then WScript.Echo objDisk.DeviceID & " " & objDisk.VolumeName
Next

For i = 67 to 90
    strDrive = Chr(i) & ":"
    If objDictionary.Exists(strDrive) Then
  if strVolume=ObjDictionary.Item(strDrive) then strFound=Chr(i) & ":"
    End If
Next
if strFound<>"" then
 WScript.Echo strVolume & "=" & strFound
 if strSetVar<>"" then
  ' Display the current value
  WScript.Echo strSetVar & "=" & wshCurrentEnv( strSetVar )
  ' Set the environment variable
  wshCurrentEnv( strSetVar ) = strFound
  WScript.Echo strSetVar & "=" & wshCurrentEnv( strSetVar )
 end if
end if
WScript.Quit 0

...and yes, this could be enhanced and/or streamlined.

So. To find a Volume name "Tom's Drive" you would type:

ListDrives.vbs /volume "Tom's Drive"

Now that's cute, but useless beyond yourself... what you can do is automate it... throw this into a batch file:

FOR /F "usebackq tokens=1,2 skip=3 delims==" %%i IN (`ListDrives.vbs /volume "Tom's Drive" /hidelist`) DO SET DL=%%j 

or

FOR /F "usebackq tokens=1,2 skip=3 delims==" %%i IN (`cscript ListDrives.vbs /volume "Tom's Drive" /hidelist`) DO SET DL=%%j 

You can now use the environment variable %DL% as the drive letter. You can use this dealing with All sorts of USB Keys, or even a memory card. There are other tricks too, but this was just an idea.