Microsoft Access through VBA makes it easy for us to interact with the system files. In this article we will explore how to manipulate text files through Microsoft Access. To use files with Access we will have to use the file system object or FSO. The File System Object is basically a class that contains properties, methods and attributes that all relate to manipulating files. For example to create a file we write:
fso.createfile()
To use the file system object, we need to instantiate it first, in other words, we need to make a ‘copy’ of the class. This is very easy to do. Let’s start with how to create a file:
In Microsoft Access create a form with the following controls:
On the create_file button’s Onclick event add the following code:
On Error GoTo Err_create_file_Click Dim fns As String fn.SetFocus If fn.Text <> "" Then fns = "c:\" & fn.Text Set fs = CreateObject("Scripting.FileSystemObject") Set a = fs.CreateTextFile(fns, True) a.Close MsgBox fns & " has been created" Else MsgBox "Please enter a File Name to create" End If Exit_create_file_Click: Exit Sub Err_create_file_Click: MsgBox Err.Description Resume Exit_create_file_Click
The important parts to look at in this code are where the FSO is instantiated:
Set fs = CreateObject("Scripting.FileSystemObject")
The fs variable will now inherit all the methods, attributes and properties that are defined in the File System Object, meaning that we can now use it to manipulate files. In fact, in the code line after we create a new file by using one of its methods called createTextFile() to create a file:
Set a = fs.CreateTextFile(fns, True)
Finally we close the object:
a.Close
In the overall, the code does the following, it checks to see if the user has entered a file name in the text field called fn. If so it hard codes the file by adding a c:\ drive to it:
fns = "c:\" & fn.Text
So if the file name was save.txt then it would be hard coded as ‘c:\save.txt’ – without the quotes. Then it calls the create text file method and creates a text file with that name. If the text field is empty, then the code simply outputs a error message to that effect.
Writing to a file is not that much difficult. In fact it is so easy to do that I did not use the FSO here. I used plain VBA to get the result I wanted, take a look at the code:
On Error GoTo Err_write_file_Click Dim fn As String fn = "c:\afile.txt" Open fn For Output As #1 Write #1, "Hi there mate" Close #1 Exit_write_file_Click: Exit Sub Err_write_file_Click: MsgBox Err.Description Resume Exit_write_file_Click
First we define the file name:
fn = "c:\afile.txt"
Then we open the file for output as #1, meaning that from this point on the file will not be referred to as fn but #1:
Open fn For Output As #1
Then we write a text to the file and close it:
Write #1, "Hi there mate" Close #1
It is that simple. If you now look at your C drive you will find a file called ‘afile.txt’ with the text ‘Hi there mate’ inside.
On Error GoTo Err_read_file_Click Dim fso, MyFile Dim linetxt As String Set fso = CreateObject("Scripting.FileSystemObject") Set MyFile = fso.OpenTextFile("c:\afile.txt", 1) linetxt = MyFile.ReadLine MsgBox linetxt Exit_read_file_Click: Exit Sub Err_read_file_Click: MsgBox Err.Description Resume Exit_read_file_Click
To read from a file using the FSO methods is easy, here we simply use the opentextfile() method to open the file for reading:
Set MyFile = fso.OpenTextFile("c:\afile.txt", 1)
Then we read the file contents in a variable using the Readline method:
linetxt = MyFile.ReadLine
Finally we print out the file contents:
MsgBox linetxt