Sometimes your data isn’t a database; sometimes you need to get data from a flat file. Or maybe you need information about your users’ files. Chris Weber shows how to solve all of these problems with the FileSystemObject.
Have you ever needed to manipulate files from within Access? Or get directory information? Or just extract the extension of a file name that your user chose through an Open File dialog? If you have, you’ve probably used the traditional file I/O statements native to VBA. For example, the simple code snippet that follows uses VBA file statements to open a file for writing, writes strings to the file, closes the file, reopens it, reads the strings back to the Immediate (Debug) window, and finally deletes (Kill) the file:
Sub TheOldWay() Dim FileNo As Integer Dim MyString As String 'set up file I/O FileNo = FreeFile Open "E:\Test.txt" For Output As #FileNo 'write to it Write #FileNo, "this is the old way to write output" Write #FileNo, _ "using Write delimits the output for easier input" Print #FileNo, "using Print does not" 'clean up Close #FileNo Open "E:\Test.txt" For Input As #FileNo Do While Not EOF(FileNo) Input #FileNo, MyString Debug.Print MyString Loop Close #FileNo Kill "E:\Test.txt" End Sub
These file I/O statements are a subset of the VBA language added solely for the purpose of external file manipulation. As disparate language statements, it’s often difficult to decide when to use Write and when to use Print. To make matters even more aggravating, in later versions of Access they’re extremely difficult to find in the Help file. If you have a copy of Access 97 installed, you can find a complete list of the statements, along with links to each, by searching Help for “Input and Output Keyword Summary.” I’ve listed them in Table 1 as a service to developers using later versions of Access.
Table 1. Native VBA file I/O statements.
|Access or create a file||Open|
|Close files||Close, Reset|
|Control output appearance||Format, Print, Print #, Spc, Tab, Width #|
|Copy a file||FileCopy|
|Get information about a file||EOF, FileAttr, FileDateTime, FileLen, FreeFile, GetAttr, Loc, LOF, Seek|
|Manage files||Dir, Kill, Lock, Unlock, Name|
|Read from a file||Get, Input, Input #, Line Input #|
|Return length of a file||FileLen|
|Set or get file attributes||FileAttr, GetAttr, SetAttr|
|Set read-write position in a file||Seek|
|Write to a file||Print #, Put, Write #|
Breaking with tradition
After the release of Access 97, Microsoft decided to encapsulate these functions into an object. They’re all now methods of a file I/O object. The object is delivered in a DLL named scrrun.dll that’s installed with various software applications and updates into the Windows System folders. The Microsoft Support site states that you can obtain scrrun.dll by installing one of the following packages. I’m not sure which of these have been installed on my Windows 2000 machine, but I found the DLL in my WINNT\system32 folder:
- Windows Script Host
- Windows NT Option Pack
- Microsoft Internet Information Server 3.0
- Scripting 3.1 upgrade
- Visual Studio 6.0
- Visual Basic 6.0
This library is not-so-commonly known as the Microsoft Scripting Runtime because its purpose was to provide a set of objects to be used by VBScript. Since many VBA developers use this library simply to get the file-related objects, it’s also commonly known as the home of the FileSystemObject. If the library has been installed, a reference to it can be found through the VBA Tools | References dialog in Access (see Figure 1). In fact, you can use this DLL directly in any application that can reference objects, such as Access, Word, and Excel, without setting a reference. This includes Access 97.
If you add the reference, all of the objects within the library are exposed through the Object Browser. For example, in Figure 2 you can see all of the methods and properties of the FileSystemObject. Unfortunately, in Access 97, the Help button on the toolbar won’t work for this object (you’ll get an error message that the file BENLR98.CHM cannot be found). The button should have led you to an HTML Help file that would open in Internet Explorer, but the path to it is unknown. If you’re using Access 2000, a link in the Access/VBA Help may find the information on the FileSystemObject and all of its methods. It worked for me for a while and then it magically disappeared on me (now my Help screen opens completely blank for help for the Scripting library).
Having an object library to handle file I/O is a much better implementation than the earlier VBA language statements, much the same way that the earlier Access basic macro statements were enhanced by becoming methods of the DoCmd object. For one thing, the new I/O properties and methods have more intuitive names and are better organized. And, theoretically, once a reference is made to the library, you should be able to create a variable of type FileSystemObject and have IntelliSense support you while you write code. Unfortunately, Auto List Members does not work for the members of the Scripting library.
I’m going to fill the gap left by the Help system to show you how to perform the same activities you saw in my previous code. Because the referencing linkage doesn’t always work properly, you may have to use late binding on an object or variant variable to work with the FileSystemObject. Some support does exist in the editor for the FileSystemObject, though. For instance, even though I typed all of the code for TheNewWay() in lowercase, the VBA editor recognized each object and method and capitalized their names where appropriate. I find this a handy way to always verify objects, methods, and variables in my coding.
Here’s my routine from the start of this article, rewritten to use the FileSystemObject, using late binding:
Sub TheNewWay() Dim fso As Object Dim f As Object Set fso = CreateObject("Scripting.FileSystemObject") Set f = fso.OpenTextFile("e:\test.txt", _ ForAppending, True) f.WriteLine "this is the new way to write output" f.WriteLine Chr(34) & _ "just append your own delimiters if needed" & _ " like this line does" & Chr(34) f.Close Set f = fso.OpenTextFile("e:\test.txt", _ ForReading, True) Debug.Print f.ReadAll f.Close fso.DeleteFile ("e:\test.txt") Set f = Nothing Set fso = Nothing End Sub
Early binding will sometimes work. If so, the only code that changes is the first three lines of the routine:
Dim fso As Scripting.FileSystemObject Dim f As Scripting.TextStream Set fso = New Scripting.FileSystemObject
Looking at the code, the first thing you might notice is how much more readable the new way is. One small point: You must specify True in the argument list of the FSO’s OpenTextFile method to tell the system to create the file if it doesn’t already exist. If the file doesn’t exist, and you don’t specify True, you’ll generate a runtime error.
A real-world example
To walk you through using the FileSystemObject, I’ll use a typical programming task. Suppose I need to read in an unknown number of files on a daily basis and append the data to my tables. The data that I must read is downloaded from some application into a folder whose name indicates the date the data was downloaded. This isn’t as peculiar an application as it sounds. This scenario reflects a system that spends all day collecting customer addresses and then exports them to one or more flat files, depending on how often the system’s export routine is run. Customer addresses may be gathered multiple times in a day, and it’s possible that some customers already have their addresses in my application.
To make matters a little simpler, I’ll assume that all of the folders with the downloaded files are in the same directory as my Access back-end .mdb. Lastly, the data is non-delimited blocks of text that may repeat in other files on the same day or on subsequent days. Here’s a sample of the data that I need to import:
2221 Alberta Dr # 1110
Amherst, NY 14226
2230 E Main St
Arcade, NY 14009
34110 Main St # 200
Buffalo, NY 14221
Here’s the pseudo-code description of the solution:
- Be sure you’re connected to the back end
- Clear temp table zstblAgenciesTemp
- Search the back-end data directory for current day’s
folder whose name is in the format mm-dd-yy
- If folder exists
- Loop through all files in folder
- Loop through each line in file
- Parse data into record fields
- Append record into temporary table zstblAgenciesTemp
- Select DISTINCT entries in the temporary table to
remove potential duplicates
- Append the DISTINCT entries into the table
tblAgencies, where not already in tblAgencies
- Rename folder so that the data won’t be imported
- Clean up
For demonstration purposes, I’ve created a pair of databases for this article named FileSystemObject.mdb and FileSystemObject_be.mdb. The front end has two tables, tblAgencies (linked) and zstblAgenciesTemp (local). Both have the same data structure (see Figure 3).
The code for the entire process is in the Click event of the button cmdDownload on the form frmFileSystemObjectDemo. Here I’ve removed general comments and interface updates for space considerations. The code begins by declaring the error handler, the file objects that I’ll need, and other variables:
Private Sub cmdDownload_Click() On Error GoTo ErrorHandler Dim objFSO As Object Dim objFolder As Object Dim objFile As Object Dim objTextStream As Object Dim strPath As String Dim strTemp As String Dim rst As Recordset Dim i As Integer, Dim strAgency As String Dim strAgencyStreetAddr As String Dim strAgencyCity As String Dim strAcencyState As String Dim strAgencyZip As String Dim strAgencyPhone As String
Here, again, I’ve declared the FileSystemObject variables as generic objects for those environments where it’s not possible to declare them as their actual datatypes. The names that I’ve given them indicate their object types, so, if you can declare the objects, it should be easy to do (for example, objFile should be declared as Scripting.File).
I then check to be sure I’m connected to the back-end database by attempting to create a recordset on tblAgencies. If successful, the code continues and extracts the path to the back-end database using my GetDataDBPath() function:
Set rst = CurrentDb.OpenRecordset( _ "SELECT Count(*) FROM tblAgencies") strPath = GetDataDBPath("tblAgencies")
Now the fun begins. Using CreateObject(), I get a reference to the FileSystemObject. I then create the full name and path to today’s folder. If the FileSystemObject finds the folder, I clear zstblAgenciesTemp and open up a recordset on it:
Set objFSO = CreateObject( _ "Scripting.FileSystemObject") strPath = strPath & Format(Date, "mm-dd-yy") If objFSO.FolderExists(strPath) Then CurrentDb.Execute "DELETE * FROM zstblAgenciesTemp" Set rst = _ CurrentDb.OpenRecordset("zstblAgenciesTemp")
I then employ the GetFolder method of the FileSystemObject and loop through all of the files in the folder. The object pointer in the For loop allows me to retrieve the name of the next folder I need to download and open it using a TextStream object:
Set objFolder = objFSO.GetFolder(strPath) For Each objFile In objFolder.Files Set objTextStream = _ objFSO.OpenTextFile(strPath & "\" & _ objFile.Name, ForReading, True)
I then loop through each line in the file. I’ve assumed that at least one record will exist in each file, always having four lines of data followed by a blank line. I parse each line into one or more string variables, which I then append to corresponding fields in zstblAgenciesTemp:
strTemp = Trim(objTextStream.ReadLine) Do strAgency = strTemp strAgencyStreetAddr = Trim(objTextStream.ReadLine) strTemp = Trim(objTextStream.ReadLine) strAgencyCity = Left(strTemp, _ InStr(strTemp, ",") - 1) strAcencyState = Mid(strTemp, _ InStr(strTemp, ",") + 2, 2) strAgencyZip = Mid(strTemp, _ InStr(strTemp, ",") + 5, 5) strAgencyPhone = Trim(objTextStream.ReadLine) With rst .AddNew !Agency = strAgency !AgencyStreetAddr = strAgencyStreetAddr !AgencyCity = strAgencyCity !AgencyState = strAcencyState !AgencyZip = strAgencyZip !AgencyPhone = strAgencyPhone .Update End With strTemp = vbNullString
To determine that I’m not reading in blank lines and that I haven’t gone past the last record, I test the length of my input string, strTemp, and check the AtEndOfStream property of my TextStream object. If the end of the stream is reached and there’s nothing in my variable, the loop exits and I move on to the next file (if any):
Do While Not objTextStream.AtEndOfStream _ And Not CBool(Len(strTemp)) strTemp = Trim(objTextStream.ReadLine) Loop Loop While Len(strTemp) Next objFile
Finally, I select the DISTINCT entries in zstblAgenciesTemp using a saved query, zsqryDISTINCT_DownLoads. I use zsqryDISTINCT_DownLoads in a join to tblAgencies inside of qappDISTINCT_DownloadsTo_tblAgencies (see Figure 4). It appends all of the records from the DISTINCT set to tblAgencies where not found in tblAgencies:
CurrentDb.Execute _ CurrentDb.QueryDefs( _ "qappDISTINCT_DownloadsTo_tblAgencies").SQL, _ dbFailOnError
Cleanup is simple. I rename the folder so that the download process isn’t repeated by subsequent clicks on cmdDownload. First, however, I need to let go of any files that my Scripting objects may have a handle on. As always, I set the object variables to Nothing in the exit block:
objTextStream.Close objFolder.Name = "Done " & objFolder.Name Else MsgBox "Today's folder not found" End If Exit_Here: Set objTextStream = Nothing Set objFolder = Nothing Set objFSO = Nothing Exit Sub ErrorHandler: MsgBox "Error #" & Err.Number & ": " & _ Err.Description Resume Exit_Here End Sub
Microsoft Support advises that there are limitations to using the FileSystemObject:
- It reads only ASCII data. While the FileSystemObject can create an ASCII or Unicode text file, the FileSystemObject can only read ASCII text files.
- It supports only forward reading, line-by-line. The FileSystemObject reads a text file in one direction: from the beginning to the end of the text file. Further, the FileSystemObject only reads line-by-line. If you need to go back to a previous line, you must return to the beginning of the file and read forward to the required line.
- You can’t open a file for simultaneous reading and writing. You can open a file for reading by using the ForReading IOMode parameter of the OpenTextFile method. However, if you want to modify the file, you must reopen the file for appending or writing and then open a TextStream object to make your file modifications.
- You can’t search for file names using wildcards. The FileSystemObject can only retrieve a specified file using the GetFile method. You can’t use wildcard characters in the filespec parameter of the GetFile method.
- You can’t return the file version number. While the File object in the Files collection of the FileSystemObject can return several file properties, there’s no version property. Use the GetFileVersionInfo Windows API function to get a file’s version information.
- You can’t sort file names from the Files collection. You can iterate through the File objects in the Files collection to obtain a list of the files in a folder. However, the File objects aren’t sorted. You need to use a sort routine to sort the File objects in the Files collection.
So you must still use the original file I/O statements in Visual Basic for binary file access, but when was the last time you needed to do that?
If you’d like to try the demo, open the sample database in the accompanying Download file and re-link tblAgencies to FileSystemObject_be.mdb. Then, put the included folder of download files “02-26-04” in the same directory as the back end. Be sure to rename the folder to today’s date in the format mm-dd-yy. Now open frmFileSystemObjectDemo and click the Download button, and you’re off and running.