Reading from Text Files

by CameronM 1. November 2006 01:19

Reading from an existing file is a very simple extension of the code example we used in Working with Text Files, except this time we will utilise the ReadLine function of the TextStream object from FileSystemObject to return a string containing the first line from our file.

Reading the First Line of Text

'open and read the contents of an existing file
Dim strFileName As String
strFileName = "C:\Temp\MyWorld.txt"

'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim fsoStream As Scripting.TextStream

'open an existing text file
Set fsoStream = fso.OpenTextFile(strFileName, ForReading)

'read a line to the file and display in a message box
With fsoStream

MsgBox .ReadLine

End With

If everything worked you will get the following message. If not, make sure that your text file exists and that it has at least one line of text.

Results of reading a simple text file

Of course in most cases we want to read more than just the first line of the file. For this we add a simple Do While…Loop and reference the property AtEndOfStream which tells us that the end of the file has been reached. Normally we would read each line and run some kind of actions on it, such as adding it to the database or checking the value against some previously stored values – feel free to add these at the TODO line.

Reading Every Line of Text

'open and read the contents of an existing file
Dim strFileName As String
strFileName = "C:\Temp\MyWorld.txt"

'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim fsoStream As Scripting.TextStream

'open an existing text file
Set fsoStream = fso.OpenTextFile(strFileName, ForReading)

'read a line to the file and display in a message box
With fsoStream

Do While Not .AtEndOfStream

'TODO: add some code here to process each line
MsgBox .ReadLine

Loop

End With

 

Tags: ,

Access | VBA

Working with Text Files Tutorials

by CameronM 28. October 2006 01:06

Reading and writing from text files may seem antiquated to the modern developer, after all didn’t we invent database management systems to avoid using flat files in the first place? There are however many times when it is useful and even absolutely necessary to read and write to text files especially when transferring data between legacy systems or proprietary systems that don’t offer direct access to their underlying databases.

Thankfully the FileSystemObject contains a number of excellent methods for opending, reading and writing to text files.

Writing to Text Files

You will find quite a number of examples on the web where the FileSystemObject is not used to write to a text file, which of course works, but to be thorough in our discussion about the FileSystemObject class we’ll look at the slightly more complex using a TextStream.

When you were in the Object Browser you may have noticed that the both the CreateTextFile and OpenTextFile functions of the FileSystemObject class returned a TextStream. We can use this TextStream to add additional lines of data to a new or existing text file.

Creating and Writing to a Text File

'create a new text file and write a line of text
Dim strFileName As String
strFileName = "C:\Temp\MyWorld.txt"

'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim fsoStream As Scripting.TextStream

'create a text file - overwriting any previous file
Set fsoStream = fso.CreateTextFile(strFileName, True)

'write a line to the file
With fsoStream

.WriteLine "Hello World!"

End With

When you were in the Object Browser you may have noticed that the both the CreateTextFile and OpenTextFile functions of the FileSystemObject class returned a TextStream. We can use this TextStream to add additional lines of data to a new or existing text file.

Opening and Writing to a Text File

'open an existing text file and write a line of text
Dim strFileName As String
strFileName = "C:\Temp\MyWorld.txt"

'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim fsoStream As Scripting.TextStream

'create a text file - overwriting any previous file
Set fsoStream = fso.OpenTextFile(strFileName, ForWriting, True)

'write a line to the file
With fsoStream

.WriteLine "Hello World!"

End With

'close the text file
fsoStream.Close

If you run the example above you will notice that no matter how many times you run it, you only end up with one Hello World! in the text file. This is because the ForWriting option, despite the sensible sounding name actually clears the test file when you open it. Not a particularly useful function if you were hoping to keep your previous information. In this case you will need to use the ForAppending option.

Tags: , ,

VBA | Access | VBA

Getting Information about your Files

by CameronM 21. October 2006 00:29

Having come this far, with a complete list of Drives and Folders, the next logical step is to see if we can find out anything useful about what Files our Folders contain. Thankfully once again the FileSystemObject contains a very useful method called Files that returns a collection of files within the current folder.

Building on the example we used when dealing with Folders using the FileSystemObject, we can now gather a raft of information about the files located in a particular folder using the Files collection of the FileSystemObject.

'declare the starting or root folder – you could get this from the Drives
Dim strRoot As String
strRoot = "C:\"

'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim parent As Scripting.Folder
Dim children As Scripting.Folders
Dim child As Scripting.Folder
Dim fl As File

'get the root folder from fso
Set parent = fso.GetFolder(strRoot)

'get the subfolders contained within the root folder
Set children = parent.SubFolders

'iterate through the subfolder under the root and display some data
For Each child In children

'iterate through the files
For Each fl In child.Files
Debug.Print fl.Name & " " & fl.Type
Next

Next

Of course, sometimes you already know the location of the file you want information about, so the FileSystemObject includes another function called GetFile that takes as its parameter the full path and filename of the file.

'declare the starting or root folder – you could get this from the Drives
Dim strFilePath As String strFilePath = "C:\Temp\MyWorld.txt"

'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim fl As File

'get the file from fso
Set fl = fso.GetFile(strFilePath)

'display some information about the file
Debug.Print fl.Name & " " & fl.Type & " " & fl.Size

'TODO: add some code to move, copy or delete the file
'you can even open it as a TextStream to read the contents

Tags: ,

Access | VBA

Getting Information about your Folders

by CameronM 14. October 2006 00:27

Just knowing the drive letters on your computer isn’t very exciting, but using this information to display a list of folders starts getting a little more useful. The GetFolder method of the FileSystemObject returns a Folder object, which contains a whole lot of useful information about the folder, including its name, size and a collection of subfolders.

You may on occasion need to display or work with a list of sub-folders located within a parent folder. Thankfully, as with finding out what Drives your computer has access to, working with fodlers is easy using the Folders property of the FileSystemObject.

'declare the starting or root folder – you could get this from the Drives
Dim strRoot As String
strRoot = "C:\"

'declare the variables for use with the Scripting library
Dim fso As New Scripting.FileSystemObject
Dim parent As Scripting.Folder
Dim children As Scripting.Folders
Dim child As Scripting.Folder

'get the root folder from fso
Set parent = fso.GetFolder(strRoot)

'get the subfolders contained within the root folder
Set children = parent.SubFolders

'iterate through the subfolder under the root and display some data
For Each child In children
Debug.Print child.name
Next

Tags: ,

Access | VBA

Getting Information about your Drives

by CameronM 7. October 2006 10:25

As you have no doubt discovered as you carefully analysed the Scripting library in the Object Browser, there are far more items listed than just the TextStream class and the OpenTextFile method. Using the FileSystemObject you can retrieve a wealth of information about the current computer, including information about available drives, folders and files.

You may on occasion need to display a list of drives the current computer user has access to, so that you can store information about the location of various files used within your Access database. This is very easy using the Drives property of the FileSystemObject.

'declare the variables for use with the Scripting library

Dim fso As New Scripting.FileSystemObject
Dim drvs As Scripting.Drives
Dim drv As Scripting.Drive

'get the drives from fso
Set drvs = fso.Drives

'iterate through the drives and display some data
For Each drv In drvs

Debug.Print drv.DriveLetter & " " & drv.ShareName

Next

You will notice that there is a myriad of properties that you can discover about each Drive, including AvailableSpace and FileSystem, such as FAT and NTSC. Try a few out in the Debug line for the example above and then impress you friends!

Tags: ,

Access | VBA

Introducing the FileSystemObject

by CameronM 30. September 2006 09:12

The FileSystemObject class contains the methods and properties required when interacting with your computers file system and is also the primary tool we will use to read and write text files. The FileSystemObject is a class within the Microsoft Scripting Runtime library, contained in the scrrun.dll.

The FileSystemObject class

Adding a Reference to the FileSystemObject

To help you get starting using the FileSystemObject it is a good idea to add a reference to the Microsoft Scripting Runtime library in your Access project (using Tools\References on the menu bar). That way you can browse the various methods and properties available in the Object Browser as well as taking advantage of IntelliSense, which helps avoid typos. We will use this method in our examples, however if you are deploying your finished database it is always best to avoid superfluous references, so we recommend you use late binding, which we will demonstrate later in the article.

Late Binding the FileSystemObject Class

As mentioned previously, if you are deploying your database to others, it is best to limit the number of references your database uses. That is unless you have a particularly sadistic attraction to pain and suffering. To use late binding, follow the example below and remove any declarations using the System library, creating instead variable of type Object.

'replace an variables creating an instance of the Scripting library
'Dim fso As New Scripting.FileSystemObject
'Dim fsoStream As Scripting.TextStream

Dim fso As Object
Dim fsoStream As Object

'initialise the FileSystemObject by using the CreateObject method

Set fso = CreateObject("Scripting.FileSystemObject")

You will also need to replace the IOMode constants used in the OpenTextFile function with their integer values.
ForWriting = 2
ForAppending = 8
ForReading = 1

After you have done this, remove the reference to the Microsoft Scripting Library and make sure your project compiles. Now you are free to explore the many facets of this powerful object.

 

Tags: ,

Access | VBA