VBA and Sequential Text Files
Sometimes you may need to store and retrieve information
but do no need the power of a spreadsheet or database. (not to mention the
extra coding, configuration and support files that go along with it.) In
these cases a text file may be just the thing you need. The process of
storing and retrieving information is simple, and the file can be edited
by anyone - even if that person doesn't have a spreadsheet/database tool.
Let's have a look first at reading from a sequential text
file.
Let's say we have a text file containing a list of standard layers that we
would like to import into AutoCAD.
Here's what we would need to do :
1. Open the file for input.
2. Read a line from the file and store it in a variable.
3. Add the contents of the variable to a list box.
4. Repeat steps 2 and 3 for each line in the file.
5. Close the file.
O.K. first let's create our text file. Open Notepad and
enter these layer names :
Annotations
Architectural
Beers
Border
Civil
Dimensions
Electrical
Existing
Instrumentation
Mechanical
Piping
Steel
Text
Now save this file as Layer.txt, ensuring that it is
within your support path.
Now open a new Project and Userform. Place a Listbox on
the form keeping the default name.
In the Userform Initialize event procedure, place the following code :
'CODING STARTS HERE
Private Sub UserForm_Initialize()
Dim sTemp As String
'clear listbox
ListBox1.Clear
'open the text file
Open "Layer.txt" For Input As #1
'until
the end of file
While Not EOF(1)
'read
the line and store it in a variable
Line Input #1, sTemp
'display
in the list box
ListBox1.AddItem sTemp
'loop
Wend
'close the file
Close #1
End Sub
'CODING ENDS HERE
Run the Project. You Listbox should look like this :
In the preceding example, 1 is the file number. However,
if you open and close multiple files throughout your program, using this
number might not be a good idea. In that case, you should use the "FreeFile"
function. I've re-written the coding below to include this function as
well as some basic error trapping :
'CODING STARTS HERE
Private Sub UserForm_Initialize()
Dim sTemp As String
Dim nFile As Integer
On Error GoTo err_handler
'get the next free file number
nFile = FreeFile
'clear listbox
ListBox1.Clear
'open the text file
Open "Layer.txt" For Input As #nFile
'until the end of file
While Not EOF(nFile)
'read the
line and store it in a variable
Line Input #nFile, sTemp
'display
in the list box
ListBox1.AddItem sTemp
'loop
Wend
'close the file
Close #nFile
Exit Sub
err_handler:
MsgBox Err.Number & " " & Err.Description
Err.Clear
Exit Sub
End Sub
'CODING ENDS HERE
To write data, you open the file for sequential output or
append. Have a look at the following two lines of code, each of which
opens a file for output :
'output mode - always
creates a new file, erases any existing information
Open "DwgLog.txt" for Output as #nFile
'append mode - adds
to an existing file or creates one
Open "DwgLog.txt" for Append as #nFile
Append mode means data written to a file is
added to the end of existing data. Opening a file for Output means that
any existing data will be erased. In either case the Open statement
automatically creates the file if it does not exist.
Have a look at the following coding that
when you run it, will write a Log file containing the date, time and
Drawing Number :
'CODING ENDS HERE
Sub DrgLog ()
Dim nFile1 As Integer
Dim Dname As String
On Error GoTo err_handler
'retrieve the drawing name
Dname = ThisDrawing.GetVariable("DWGNAME")
'get the next free file number
nFile1 = FreeFile
'open the file to append to
Open "DrgLog.txt" For Append As #nFile1
'write to the file
Print #nFile1, Format$(Now, "mm-dd-yy hh:mm:ss") & " -
" & Dname
'close the file
Close #nFile1
Exit Sub
err_handler:
MsgBox Err.Number & " " & Err.Description
Err.Clear
Exit Sub
End Sub
'CODING ENDS HERE
DrgLog.txt should have been created in your working
directory. Open it and have a look. It should look something like this :
11-02-01 09:57:20 - X3481.dwg
11-02-01 19:57:21 - X3482.dwg
11-02-01 14:40:20 - K2341.dwg
11-02-01 15:21:40 - K3418.dwg
Every time the routine is run, the date,
time and Drawing Number is added to the file. (Hey, we could make a good
drawing log file out of this?)
On the next page we'll have a look at opening and
searching a text file for multiple instances of information required for a
program to run.
|