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.
Figure 5 – Launching References Dialog in VBE
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.
