TechBookReport logo

Using The FileSystemObject With VB and VBA - Part 3

By Pan Pantziarka


A new text file is created as a TextStream by using the CreateTextFile method of the FSO. In addition to the obvious parameter of the file name, the CreateTextFile method has a couple of optional arguments. The first argument after the file name is a Boolean to indicate whether an existing file can be over-written or not. By default this argument is set to True, which means effectively that an existing file with the same name is simply deleted and a new file created. If you specify an Overwrite argument of False then an error occurs if you attempt to create a file with the same name and path as an existing file. The second optional argument specifies whether the new text file is created as Unicode or ASCII. The default value of False gives an ASCII file, a value of True gives a Unicode file.

To create an ASCII file called temp.txt in the C:\Temp folder, with the option to overwrite an existing file, we would use the following command:

Set txtstr = fso.CreateTextFile("C:\Temp\temp.txt", True)

Alternatively if we had a folder variable pointing to C:\Temp, we could use the following:

Set txtstr = fld.CreateTextFile("temp.txt", True)

Once we have created a TextStream we can write out to it. There are in fact three methods for writing out to a TextStream. The simplest is to use the WriteLine method, which, as its name suggests, writes out an entire line to the file, complete with a terminating new line character at the end. Alternatively the Write method can be used to write out text without terminating with a new line character. Finally the WriteBlankLines method can be used to write out one or more blank lines to a file.

To see all of these write methods in action we can craft a macro - which will work both in Word and Excel - to write out the file names and sizes of the files in \Windows\system to a TextStream called temp.txt in the C:\Temp folder:

Sub test6()
	Dim fso As New FileSystemObject
	Dim flds As Files
	Dim fld As Folder
	Dim txtstr As TextStream
	Set flds = fso.GetFolder("C:\Windows\system").Files
	Set fld = fso.GetFolder("C:\Temp")
	Set txtstr = fld.CreateTextFile("temp.txt", True)
	With txtstr
	    .Write "File Name"
	    .Write Chr(9)
	    .Write "File Size"
	    .WriteBlankLines 2
	    For Each f In flds
	        .WriteLine (f.Name & Chr(9) & f.Size)
	End With
End Sub

Once the TextStream has been created the Write method is used to write out a header line consisting of 'File Name' and 'File Size' separated by a tab character (Chr(9)). Of course this header could have been assembled into a single string variable and written out in one hit. Next the WriteBlankLines method is used to write out two new line characters, the first terminates the header line and the second is indeed a blank line. Finally within the 'For Each …' loop the WriteLine method is used to write out the file name and size, again separated by a tab character. At the end of the loop the .Close method is used to Close the file.

As well as writing to text files, the FSO can read from them. The command to open a file is OpenTextFile, and this can be used to open an existing file or, optionally, to create a new file should one not exist. The syntax for the command is:

.OpenTextFile(filename[, iomode[, create[, format]]])

The filename is the full path and file name of the file to open. The 'iomode' argument can take three values, ForReading, ForWriting or ForAppending, (these constants have a value of 1, 2 or 8 respectively). ForReading means that you can only read from the opened file, ForAppending means that you can write to the file, all new writes are appended to the end of the file. If you use the ForWriting argument then the file is opened, the contents discarded and then all new writes start from the beginning of the file. The 'create' argument is simply a Boolean, with a default value of False to indicate that an error should be flagged if the file you are opening doesn't exist. Setting the value to True means that the file will be created if it doesn't already exist. Finally the 'format' argument has three possible values: TristateUseDefault (value -2) means open the file using the system default, TristateTrue (value -1) means the format of the text file is Unicode, while TristateFalse (value 0) opens the file as ASCII.

An alternative to the OpenTextFile method is the OpenAsTextStream method of the 'File' object. This uses the same 'iomode' and 'format' arguments as OpenTextFile. Assuming we have a file variable set to point to a file, we could use the following code to open it:

f.OpenTextFile(ForReading, TristateUseDefault)

Once we have a TextStream open for reading we can use the ReadLine or Read methods to read entire lines or a set number of characters. We could also, if we wanted to, read the entire TextStream into a single string variable using the ReadAll method. Obvious caution must be used if reading in entire files as very large files can cause memory or resource problems. What if we want to skip part of a file? The Skip and SkipLine methods can be used to skip a specified number of characters or an entire line from a file.

Having used our previous macro to create the file C:\Temp\temp.txt, we'll now read the contents back in our next macro:

Sub test7()
	Dim fso As New FileSystemObject
	Dim fld As Folder
	Dim ts As TextStream
	Const ForReading = 1, ForWriting = 2, ForAppending = 8
	Set ts = fso.OpenTextFile("C:\Temp\temp.txt", ForReading)
	While Not ts.AtEndOfStream
	    Debug.Print ts.ReadLine
End Sub

Like our pervious macro, this one too is valid in either Word or Excel. Note how this extremely simple macro uses the Const directive to define the values for the 'iomode' arguments. It would have been equally valid to have simply used the integer values of 'iomode' in the OpenTextFile command, but it would have been less readable to anyone not familiar with the FSO commands. Once the text file has been opened the macro simply uses a loop which terminates when the end of the TextStream has been reached. Within the loop the Debug.Print command is used to send each line in the file to the 'immediate' window of the Visual Basic Editor.

A TextStream need not be read line by line, it can also be read character by character or by group of characters. In addition to the AtEndOfStream property which we used in the macro there is also an AtEndOfLine property which can be used when processing through a line from a file.

A current limitation of the TextStream is that it can only be processed one way. Unlike the other file reading functions supported in VBA and VB, there is no 'rewind' option to go back to a portion of a file that has been previously read. If you need to go back to part of the file that has been previously read then the TextStream must be closed and re-opened again. This limitation means that should your code need to have totally random access to a file, rather than strictly sequential access, then some of the older non-FSO VB functions should be used.

Temporary Files

There are times when complex macros or Visual Basic programs may need to create temporary files. Many programs do this as a matter of course, including Word, which creates a number of such files whenever it is opened. Should you need to do this in your own code you will be faced with the task of coming up with a unique file name. This facility has, however, been added to the FSO, which includes a GetTempName method which returns a randomly generated file or folder name. Note that it returns a name only, to actually create the folder or file then the CreateFolder or CreateTextFile functions must be used.

Where should temporary files or folders be created? In whatever folder has been designated the Temp folder, usually \Windows\Temp, but in theory it could be any folder that is stored in the TMP environment variable. Again the FSO has taken some of the headache out of the process of finding out where the Temp folder is with a method that returns the locations of a number of important Windows folders. The GetSpecialFolder method takes a single argument of 'folder spec' which can take three values: WindowsFolder (value 0), SystemFolder (value 1) or TemporaryFolder (value 2).

To put all of this into action we can create a macro which will illustrate the use of the Dir() command mentioned previously as a way of circumventing the lack of wild-card support in the files collection, and which will spool a list of files to a temporary file in the temp directory. The function of this macro is to scan the files in the Temporary Internet Folder to return a list of pdf files that it contains:

Sub test8()
	Dim fso As New FileSystemObject
	Dim fls As Folders
	Dim strTIF As String
	Dim strTempFile As String
	Dim strFile As String
	Dim ts As TextStream
	Dim strWildCard As String
	Const WindowsFolder = 0, SystemFolder = 1, TemporaryFolder = 2
	strTIF = "\Temporary Internet Files\Content.IE5"
	strWildCard = "*.pdf"
	strTempFile = "\" & fso.GetTempName
	With fso
	    Set ts = .CreateTextFile((.GetSpecialFolder(TemporaryFolder) & strTempFile))
	    Set fls = .GetFolder((.GetSpecialFolder(WindowsFolder)) & strTIF).SubFolders
	    For Each fl In fls
	        strFile = Dir(fl.Path & "\" & strWildCard)
	        Do While strFile <> ""   
	            If strFile <> "." And strFile <> ".." Then
	                ts.WriteLine strFile
	            End If
	            strFile = Dir   ' Get next entry.
	End With
End Sub

Although this is a much more complex macro than any of the others created so far, it is also potentially the most useful, as anyone who has ever tried to retrieve files from an internet cache will know.

The macro begins by declaring the variables to be used, and also the constants required for the call to the fso.GetTempName method. The files in the internet cache for Internet Explorer 5 are stored in a set of cache folders under the directory 'Temporary Internet Files\Content.IE5', which is assigned to the variable strTIF. The wild-card '*.pdf' is assigned to the variable strWildCard but this can be changed in the macro to any standard DOS wild-card. Once these two variables have been set the GetTempName method is called and it returns a unique file name (which will have an extension of .tmp).

Although we have the name for a temp file, we do not have a path for it yet, so before we create the file we need to decide where to put it. Using the GetSpecialFolder command with an argument of 'Temporary Folder' returns the location of the temp folder, which we can then use with the temp file name in the CreateTextFile command. The GetFolder method is used to get the collection of sub-folders for the internet cache, and, being extra cautious we use the GetSpecialFolder method to get the Windows directory which hosts the cache.

Finally, looping through each of the cache folders, we use the Dir command to return the files which match our wild card in the Dir() command. This VB command returns an empty string if no file matches the wild card, as well as returning the . and .. files which exist in every DOS directory. A 'Do&hekkip;Loop' is used to cycle through each folder which has files matching the wild card, and after eliminating the . and .. files, the valid file names are written to the text file. Once the Dir command has been called with a wild card, further calls to Dir - without any argument - simply return the next file which matches.

Obviously there is not much point in writing this information out to a temporary file, except for illustrating how the various FSO commands work. In reality this macro would be more useful if it prompted the user for a file wild card, and if it copied the matching files to a specified directory.

<<1: Introduction, Installation, Using FSO
<<2: Drives, Folders, Files

Return to home page

Contents copyright of Pan Pantziarka. If you like it link it, don't lift it. No copying for commercial use allowed. Site © 2006.