Search code examples
csvvbscriptwmiwql

WMI Query returning null collection when querying Win32_Directory

I'm trying to use VBScript to select all csv's in a specific folder and then concatenate them into one. I am adding all of the csv's to a collection using ExecQuery on Win32_Directory, specifying the path and the extension properties. I have five csv's in the folder to test. But the collection returned is always null.

Here's my code:

strComputer = "."
Set wshShell = WScript.CreateObject( "WScript.Shell" )
Set objWMIService = GetObject("winmgmts:" _
   & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

'Options for CreateTextFile
boolOverwrite = True
boolUnicode = True

'Options for OpenTextFile
constForReading = 1
constForWriting = 2
constForAppending = 8
boolCreate = False
constTristate = -1

strPath = "C:\Users\adam\Documents\Test\Temp.csv"
strDrivePath = "C:\Users\adam\Documents\Test"

'Creates object reference to files in relevant folder.
Set objFSO = CreateObject ("Scripting.FileSystemObject")

'Creates new CSV to write others' contents to.
Set objNew = objFSO.CreateTextFile(strPath,boolOverwrite,boolUnicode)

Set colCSV = objWMIService.ExecQuery _
 ("SELECT * FROM Win32_Directory WHERE Path = strDrivePath AND Extension = 'csv'")

'Concatenates contents of CSVs
For Each objCSV in colCSV
 Set objTemp = objFSO.OpenTextFile(objCSV.Path & objCSV.FileName & objCSV.Extension,constForReading,boolCreate,constTristate)
 Set strLine = objTemp.ReadLine
 objNew.Write strLine 

Next

I am also unsure of whether the way I've specified the path for OpenTextFile is going to work or not. But my main concern right now is getting the query to return the files I want.

Thanks for any help!

Solution

  • You've got a few issues here.

    1. If you want to select files, use the CIM_DataFile class. Win32_Directory is for, you guessed it, directories.

    2. Backslashes in the Path property must be escaped (\\).

    3. strDrivePath is a variable but you're using it literally in your WMI query.

    4. Is your intention to run this script on a remote machine? If not, why not use the FileSystemObject methods? You already have an FSO object created.

    Here is a similar question I answered in the past showing how you can use a FileSystemObject or a WMI query to find files matching a specification.

    In this situation, your query might look something like this:

    strFileSpec = "C:\\Users\\Adam\\Documents\\Test\\%.csv"

Set colCSV = objWMIService.ExecQuery _
 ("select * from CIM_DataFile where Name like '" & strFileSpec & "'")

    But your query will run faster (often noticeably) if you specify values for Drive and Path in your statement. See my linked post for an example.

    Edit: I just realized that you were the OP of the question I linked to as well!