VBA and Excel for Engineers and Scientists – Part 3

Working With Files

This sections seems kind of anti-climactic after finishing our Monte Carlo example. But whether you realize it or not right now, manipulating files and folders from VBA can be a nice tool to have in your programming toolbox. I’m mostly interested in showing you how to read and write text files, but as it turns out, the reference you need for reading/writing text files also has some other nice capabilities such as moving, copying, renaming or deleting files or directories. The object you need to do all of these things is called the File System Object.

Getting Access to the File System Object (FSO)

The first thing you need to do is add a “Reference” to the “Microsoft Scripting Runtime”. To do this, in the VBE, go to the Tools Menu, choose References. Look for the “Microsoft Scripting Runtime”, it should be “scrrun.dll” and check the box next to it.

References menu in Visual Basic Editor

Figure 5 – Launching References Dialog in VBE

Microsoft Scripting Runtime

Figure 6 – Microsoft Scripting Runtime

Using the File System Object

The easiest way to explain how to use this object is with a few simple examples.

Listing 11: FileSystemObject Examples

'First, you always need to Initialize the FileSystemObject  Dim oFSO As New FileSystemObject  'Copy a file oFSO.CopyFile "C:\Source\Path\Filename.ext", _       "P:\Destination\Path\Newfilename.ext", blnOverwriteFile  'Move a file oFSO.MoveFile "C:\Source\Path\Filename.ext", _       "P:\Destination\Path\Newfilename.ext", blnOverwriteFile  'Create a folder Dim oFolder As Folder Set oFolder = oFSO.CreateFolder("C:\Path\Folder")  'Delete a File oFSO.DeleteFile "C:\Path\File.ext"  'Delete a Folder oFSO.DeleteFolder "C:\Path\Folder"  'Move a folder oFSO.MoveFolder "C:\Source\Path", "P:\Destination\NewPath" 

Text Files

Even in this day and age of Windows Vista, 64-bit programs, quad-core processors etc, there are still programs out there that used to run on DOS prompts and still use text files for input and/or output. With your new VBA programming skills and the info in this section on how to read and write text files, you can code your own preprocessor, postprocessor or batch files to run in Excel. Even with more modern programs, plain ASCII text files are still a tried and trued way of exchanging information.

TextStream Object

The TextStream Object requires the FileSystemObject described previously (don’t forget that reference). And don’t forget to initialize the object (Dim oFSO as New FileSystemObject). To open a file for reading:

Listing 12: Text Files for Reading

'Open a text file for reading Dim oTextFile As TextStream Set oTextFile = oFSO.OpenTextFile(strPath) 'Loop through each line of the file Do Until oTextFile.EndOfFile 	strLine = oTextFile.ReadLine   'Do something with the line Loop 

If you want to write to a text file:

Listing 13: Text Files for Writing or Appending

Set oTextFile = oFSO.OpenTextFile(strPath, IOMode, blnCreate) oTextFile.WriteLine 'repeat using a loop oTextFile.Close 

IOMode is an optional parameter, it can be one of 3 constants:

  • ForAppending – Append to the file if it exists
  • ForReading – Read only (the default)
  • ForWriting – Overwrites if exists

blnCreate is an optional boolean flag, if it is true, the file will be created if it doesn’t already exist.