Computer geek, and self-appointed know-it-all, Westley Annis answers all those hard
questions about anything related to computers and technology, as well as business and
political questions.

Have a Word Macro Detect Your Thumb Drive

Question asked on February 4, 2009 8:55 AM :: Permalink :: Comments (0) :: TrackBacks (0)

Although not directly a part of Visual Basic for Applications (VBA), the macro language of the Microsoft Office Suite which includes Word and Excel, VBA can detect all sorts of things about your current hardware configuration using the Windows Management Instrumentation (WMI) API.

Through the WMI, you can get a list of all logical drives your computer is currently recognizing and search through them looking for your thumb or flash drive. You want to search through the logical drives because the actual physical drives may be partitioned into two or more logical drives and your templates can only be accessed through the logical drive.

The first step in your macro is to create the connection to the WMI API so that you can query it for the list of drives. This is done using the following code:

Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")

Once the connection to the WMI is made, you can query the Win32_LogicalDisk class to get a list of all logical drives.

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

The Win32_LogicalDisk will answer your query with an array that can be looped through until your thumb drive is found. There are two fields that can be used to determine if the drive being looked at is your thumb drive or not, the VolumeName or VolumeSerialNumber.

Using the VolumeSerialNumber would be the ultimate way to ensure you had the correct drive, since it is nearly impossible for two drives to have the same serial number, it is a little more complicated to get the serial number, so we will just stick with the volume name.

Since you will need the volume name for the next piece of code, go ahead and open Windows Explorer (quickest way is to hold down the Windows key on your keyboard and press the letter E). Windows Explorer shows you a list of all your drives with the volume names first followed by the drive letter inside of parenthesis.

Now that you have your drive letter, you can use the following code to loop through the collection of drives until your drive is found.

For Each objdisk In colDisks
    On Error Resume Next
    If objdisk.VolumeName = "ThumbDrive" Then
        strMyDrive = objdisk.DeviceID
        Exit For
    End If
Next

You would just need to replace ThumbDrive in the code above with the name of your thumb drive.

Combining the code with the macro you sent in your question, the entire code would now look like this:

Sub AttachMyTemplate()
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")
Set colDisks = objWMIService.ExecQuery("Select * from Win32_LogicalDisk")
For Each objdisk In colDisks
    On Error Resume Next
    If objdisk.VolumeName = "PocketDrive" Then
        strMyDrive = objdisk.DeviceID
        Exit For
    End If
Next
With ActiveDocument
    .UpdateStylesOnOpen = True
    .AttachedTemplate = strMyDrive & "\Templates\MyTemplate.dot"
End With
End Sub