Hey, Scripting Guy! How can I tell whether a file exists on any CD or DVD drive connected to a computer?

— GH

Hey, GH. Well, one way to do this would be to search the entire file system – including any mapped network drives – and check to see if a file (let’s call it Budget.xls) can be found anywhere. Assuming you find such a file, you can then use some additional code to determine whether or not that file happens to be on a CD or DVD drive.

That would work, but it’s not very elegant and – depending on the size of your file system – could take awhile to complete. A much better method would be to tell your script to look for that file only on CD or DVD drives. And guess what: that’s exactly the approach we’re going to take.

What, you expected the Scripting Guys to do something that wasn’t elegant and sophisticated?

We’ll want to start by looking for the CD/DVD drives that are connected to the computer in question, and discovering the drive letter for each of those items. Fortunately, that’s easy to do; this little WMI script reports back the drive letters for all the CD/DVD drives connected to a computer:

strComputer = “.”
Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)

Set colItems = objWMIService.ExecQuery(“Select * from Win32_CDROMDrive”)
For Each objItem in colItems
    Wscript.Echo objItem.Drive
Next

After we’ve identified the drive letters we can put together a WMI query that looks for a file only on those specified drives. Let’s take a look at the completed script, and then we’ll explain how it works:

On Error Resume Next

strComputer = “.”
Set objWMIService = GetObject(“winmgmts:\\” & strComputer & “\root\cimv2”)
Set colItems = objWMIService.ExecQuery(“Select * from Win32_CDROMDrive”)
For Each objItem in colItems
    strDrive = objItem.Drive
    Set colFiles = objWMIService.ExecQuery _
        (“Select * From CIM_DataFile Where FileName = ‘Budget’ ” & _
            “AND Extension = ‘xls’ AND Drive = ‘” & strDrive & “‘”)
    If colFiles.Count > 0 Then
        For Each objFile in colFiles
            Wscript.Echo objFile.Name
        Next
    End If
Next

As you can see, we start off by retrieving a collection of all the CD/DVD drives on the computer. For each drive found (and there could be more than one), we assign the value of the Drive property (which, as we noted earlier, represents the drive letter) to a variable named strDrive. We then use a second WMI query to search that drive for a file named Budget.xls. Here’s the code that does that:

Set colFiles = objWMIService.ExecQuery _
        (“Select * From CIM_DataFile Where FileName = ‘Budget’ ” & _
            “AND Extension = ‘xls’ AND Drive = ‘” & strDrive & “‘”)

Admittedly, the query looks a little convoluted; that’s because WMI treats the file name (Budget) and the file extension (xls) as two separate properties. Because of that we have to use three parameters in our WHERE clause: the file name (Budget); the file extension (xls); and the drive we want to search. Notice that we don’t hard-code in a drive letter but instead we use the variable strDrive. Like we said, at first glance it’s a little complicated, but it works.

After issuing the query, and after waiting for the script to search the CD/DVD drive (which shouldn’t take more than 15-20 seconds, tops) we check to see how many files with the name Budget.xls were found. If the collection contains at least one file (colFiles.Count > 0) we echo the Name property for each file found. (If Count is equal to 0 that means we couldn’t find any files named Budget.xls, so we don’t do anything at all.) The script then loops around and searches the next CD/DVD drive in the collection. This continues until each CD/DVD drive has been searched, and any successes reported back.