VBA Standard Text File I/O Statements

[zmbd]

It seems that in VBA some the most fundamentally basic commands for file control are the hardest to find any information for, for example, the basic File operation commands, such as DIR(), CHDRIVE(), CHDIR(), FILECOPY(), NAME(), KILL(), and commands to handle the basic text input/output.

So in this article I want to cover the basics behind the file I/O commands.
FreeFile, Open, Close, LOF, SEEK, Input, Input#, Line Input, Print, Write, Reset, and EOF.

Using these commands along with the string functions can provide a very valuable set of tools without the need to add any library references or late binding object to use the file scripting object. You can archive materials, save settings, offline error logs, and I’m sure that many of us have had the need to import text from a TXT/CSV file. Each of the statements are hyperlinked to the Microsoft Programer's Reference site for more detail... well at the time I wrote this there was useful information there :) ...

FreeFile
intPointer = FreeFile
When a file is opened it needs a pointer. FreeFile allows you to find the very next, lowest, available pointer. It doesn’t increment with use/call if the FreeFile; thus, until you open or close a file the result returned will be the same, so if you open a files with #1, #2, and #3 and call FreeFile it will return #4, if you call it 50 times without opening or closing a file, it will return #4 every time. However, if you close #1, then call FreeFile, it will return #1 and files referenced with #2 and #3 will remain open!

Open
Open pathname For mode [Access access] [lock] As [#]filenumber [Len=reclength]
The basics… you have to open the file before you can use it.
Keep in mind… you open a file for INPUT to the program from the file and OUTPUT from the program to the file.
The FOR modes are: Append, Binary, Input, Output, or Random – Keep in mind, that unless you open as Random, every time you INPUT from the file or OUTPUT/APPEND to the file, the pointer moves forward this is almost the same as a dbOpenForwardOnly recordset. For more information about using the Random mode How to Work with Random Access Files
The lock modes are: Shared, Lock Read, Lock Write, and Lock Read Write
Reclength, I rarely use and has to do with either the bytes or characters buffered depending on how the mode used to open the file; however, say the data is in the first 100 characters of a 10000 character file, why open the entire thing.
So the basic syntax to open the file would be
Open "C:\in.txt" For Input As intPointer to create/overwrite the file
Open "C:\in.txt" For Append As intPointer to append to an existing file.

Close
CLOSE filenumberlist
The basics, if you open it, close it. You also need to close a file between modes, so to go from input to output or random and lock types you need to first close the file then re-open the file with the correct settings.

LOF()
intChars = LOF [#]filenumber
Returns the number of Characters in the open file.
intX=LOF( 1)

SEEK
SEEK [#]filenumber, position
Sets the position within the opened file for the next read/write operation. Be careful here or you will overrun the file length and error out - and yes, you can move the pointer back to the start of the file using this method (unlike a dbOpenReadForwardOnly recordset)
Seek #1, NextChar

Input
strImport = INPUT(#Toimport, [#]filenumber )
The Input function will import the number of specified characters at a time into Access. This is probably of most use when you have a record of fixed width or length (i.e. you have set the Len argument of the Open statement). Once again, be careful of the file length or you will overrun the file and error out
For example this would import the entire file that you could then parse, if you have the memory and you’re reading across a slow network this could be more useful.
strImportFile = input(len([#]filenumber), [#]filenumber )

Line Input #FileIndex
Line Input #filenumber, varname
Does what it says, reads the entire line into the variable and you split as needed.
Line Input #1, strReadLineIn
(Note: The syntax here is important, the "#" sign must be part of the code so Line Input #intIndex, strReadLineIn will work however, Line Input intIndex, strReadLineIn will error)

Print #
Print #filenumber, [outputlist]
Well we need a method to put data into the file. This method sends the information to the file pretty much as is, appending a <cr><lf> at the end of the output. It doesn’t wrap extra characters around things so it’s a better choice for exporting to other programs. Therefore, if you need delimitation in the file, you need to provide it in the string format. The other quirk is that Data classed as date is output as the system short date and it will insert spaces between the list “fields.”
There’s a series of arguments that can be included to help with output formatting (see the link).
Sending a print# to the file without anything inserts an empty, zero length, string (blank line); however, If there is a Null valued variable, it will insert a Null character in the line and then return.
There’s an example in the link, the most basic is:

’Simple single line:
Print #1, “This is a single line to the file” ‘you get exactly as shown
‘print a line of information
Print #1,  “a”;”b”;”c”;”d” ‘you get “abcd” on the line
‘two zones
Print #1, “zone1”, “zone 2” ‘ you get “zone 1             zone2” however, if zone1 text is larger then things push
‘two zones – better formatting
Print #1, “zone1”; tab ; “zone 2” ‘ same as above, but with the tab character that you can then use to set the starting column such as tab(10)
‘A trailing “;” semicolon will hold the line for the next print# operation.
Print #1,  “a”;”b”;”c”;”d”;  ‘note the trailing ;
Print #1,  “e”;”f”;”g”;”h” ‘you get “abcdefgh” on the line and a <cr><lf>
 

> - <
The two following work in conjunction with each other and wrap things in double quotes. Be aware, that anything with embedded double quotes will have issues with these two methods/statements as these will create a new “element” within the “record”/Line.

Write #
Write #filenumber, [outputlist]
Unlike the Print # statement: This statement re-formats and translates a lot things, ignores the local settings, inserts commas between items and quotation marks around strings as they are written to the file and adds the <cr><lf>. The link has a good example of how and what you get. I use this rarely and only for a few “deep” archives as there has to be some care taken as to how the data is formatted and the embedded quotes can make a mess of things.

Input#
Input #filenumber, varlist
Note the variable list here… this works with the Write# statement… and from the manual: “it is a Required. Comma-delimited list of variables that are assigned values read from the file — can't be an array or object variable. However, variables that describe an element of an array or user-defined type may be used.” These variables are also type castable so that a date can be assigned to a date, true/false to a Boolean, etc…
This isn’t really the most useful of the input methods; however, I’ve used it upon occasions when I’ve written files using the WRITE# method… usually for deep archives where if I ever really need the data it’s a truly bad day (week, month).

This is a handy command:
Reset.
And that is the code, place Reset on a line and execute… and EVERY file you currently have open is flushed, saved, and closed – period, no prompt, just done. It’s a nice thing to have in an error event handler or in the cleanup section of the code.

Ahhh Yes, one more,

EOF
You need a means of testing for the end of file. So to borrow the code from the above link as it really cannot get any more simple and generic than this for an example:

 Dim InputData
Open "MYFILE" For Input As #1    ' Open file for input.
Do While Not EOF(1)    ' Check for end of file.
Line Input #1, InputData    ' Read line of data.
Debug.Print InputData    ' Print to the Immediate window.
Loop
Close #1    ' Close file. 

There are other text file IO commands such as GET and PUT for simple record handling. Ah, Yes, I did very old school “database” handling with this stuff back in the day… then SQL and real database engines became available to us little people (and much rejoicing filled the land).

Once again, unless opened for random, every INPUT/OUTPUT action on the file will generally move the index forward in the open file unless a SEEK() is performed.

There are other basic VBA statements that can also save you some effort, most of these we use frequently; however, a few are less so:
Office Visual Basic Language Reference>Statements
some of these have been retained only for backwards compatibility (“Error”) and others such as “Gosub” have fallen out of favor and considered poor programing technique. Another reference to keep handy are the basic functions for VBA: Office Visual Basic Language Reference>Functions

Most of this information is common knowledge to us old school BASIC/FORTRAN/COBOL programmers. I’ve borrowed an example or two from various places altering the variables, however, this is like a C-scale in music, it’s a C-scale and it’s in the public domain so to speak.

[jimatqsi]

Wow, very nice to have all this information in one place. Thank you!

[Databoe]

Excellent post, thanks so much.
Do you know what vba uses to define the end of the file? As it uses CR or CRLF to work out the end of line, is there a similar character it uses to define end of file?

[zmbd]

Databoe:
Each of the statements (in blue) are hyper-linked to MS programmer's reference for more information.

In the case of Line Input #Fileindex it is a case of both-and not either-or; however, with the CRLF combination the CRLF is usually dropped from the string where with just the CR the return is kept. I have had occasions where this hasn't held true so one must usually inspect the string in code before using.

As for the simple Input you have to parse the string yourself once fetched to the variable.

Hope that helps.

For deeper discussion we do ask that you start a new thread in the main Q&A section ... more people will see the question and it helps to keep the articles on topic.


-z