Working with Files and Directories

Here’s some more functionality I’ve discovered in the .Net Framework that makes it hard for me when I have use VBA.  In a recent post I talked about the functionality to manipulate full paths that’s supported by the Path class of the System.IO namespace.  Two other very useful classes supported by System.IO are Directory and File.  In VBA there are several functions that work with files but they’re harder to use and don’t have the same capabilities as what’s supported by System.IO.

A workhorse function in VBA is the Dir function.  You can use it to check for the existence of a file.  This isn’t much code but it’s not particularly intuitive that you can use Dir for this.

Dim filename As String      filename = "C:TempPart1.ipt"       If Dir(filename) = "" Then           MsgBox "Specified file does not exist."       Else           MsgBox "File exists."       End If

Using VB.Net and System.IO I can accomplish the same thing with the code below.  In lines of code there’s not any difference, but it’s easier to understand what it’s supposed to be doing.

Dim filename As String      filename = "C:TempPart1.ipt"       If System.IO.File.Exists(filename) Then           MsgBox("File exists.")       Else           MsgBox("Specified file does not exist.")       End If

The File class that’s used above supports much more than just the Exists function.  You can delete, open for read, create, get and set various file attributes, etc.

In addition to the File class the System.IO class also supports the Directory class.  Using this class you can check for the existence of a directory, create a directory, delete a directory and much more.  Here’s a VBA example using the Dir function to iterate over all of the part files within a directory.  This does the job but isn’t particularly intuitive.  If you also want to process the contents of subdirectories then it gets much more complicated because you can’t have nested Dir calls.

Public Sub ProcessPartsVBA()          Dim processPath As String           processPath = "C:Temp"                    ' Get the files in the specified directory.       Dim filename As String         filename = Dir(processPath & "*.ipt")         Do While filename  ""             Debug.Print "Processing " & processPath & filename                 filename = Dir         Loop     End Sub

Here’s the same thing using the Directory class.  Here a single call to the GetFiles function returns all of the files matching the search pattern as an array of strings.  I think this is much easier to read, maintain, and is more flexible.

Public Sub ProcessPartsVBNet()          Dim processPath As String           processPath = "C:Temp"        ' Get the files in the specified directory.           Dim filenames() As String         filenames = System.IO.Directory.GetFiles(processPath, "*.ipt")         For Each filename As String In filenames             Debug.Print("Processing " & filename)         Next     End Sub

There’s also a version of the GetFiles function that gets the files in the current directory and all subdirectories.  This is messy to do with VBA.

Public Sub ProcessAllPartsVBNet()          Dim processPath As String           processPath = "C:Temp"        ' Get the files in the specified directory and subdirectories.           Dim filenames() As String         filenames = System.IO.Directory.GetFiles(processPath, _                              "*.ipt", SearchOption.AllDirectories )         For Each filename As String In filenames             Debug.Print("Processing " & filename)         Next     End Sub