VBA read and write files

Process text files

1. Open statement

Syntax: Open pathname for mode [Access access] [lock] as [#] filenumber
[Len = reclength]

Here, access, lock, and reclength are optional parameters, which are generally not required.
Mode specifies how to open the file. There are 5 types:
Input: to enter
Read mode.
Output: open in output mode, that is, write mode.
Append: open in append mode, that is, add content to the end of the file.
Binary:
Open in binary mode.
Random: open the file in random mode. If the mode is not specified, open the file in random mode.

Filenumber is a valid file number ranging from 1 to 511. You can specify or use freefile
Function to get the next available file number.

Note: If the file specified by pathname does not exist, append, binary, output, or random is used.
You can create this file.

Example:
Open "F:/test.txt" for input as # 1' open as input
Open
"F:/test.xls" for binary as # 1' open in binary mode

2. Close statement

Syntax: Close [filenumberlist]
The filenumberlist parameter is one or more file numbers. If this parameter is omitted
Filenumberlist, all active files opened by the open statement are closed.

Note: After opening a file, you must close the file after use.

Example:
Dim I, filename
For I = 1 to 3
Filename = "test"
& I 'create a file name.
Open filename for output as # I 'open the file.

Print # I, "this is a test." 'writes the string to the file.
Next I
Close'
Close all three opened files.

3. Reset statement

Syntax: reset

Function: Disable all disk files opened with open statements.

Note: The reset statement closes all active files opened by the open statement and writes all content in the file buffer to the disk.

Example:
Dim filenumber
For filenumber = 1 to 5
Open
"Test" & filenumber for output as # filenumber
Write
# Filenumber, "Hello World" 'writes data to a file.
Next filenumber
Reset'
Close the file and write the data in the buffer to the disk.

4. freefile Function

Syntax: freefile [(rangenumber)]
Parameters
Rangenumber specifies a range to return the next available file number within the range. If 0 is specified (the default value), a file number between 1 and 255 is returned. Specify 1
Returns a file number between 256 and 511.

Function: provides an unused file number.

Example:
Dim fnum as integer

Fnum = freefile

Open "F:/test.txt" for input as # fnum

Close # fnum

5. EOF Functions

Syntax: EOF (filenumber)

Function: returns an integer that contains the Boolean value true, indicating that the object has reached the end of the file opened for random or sequential input.

6. lof Functions

Syntax: lof (filenumber)

Function: returns a long value indicating the size of the file opened with an open statement, in bytes.

7. Loc Functions

Syntax: LOC (filenumber)

Function: returns a long string that specifies the current read/write position in the opened file.

8. Input # statement

Syntax: Input # filenumber, varlist

Function: read data from an opened Sequence File and specify the data to the variable.

Note: write # is usually used to write the data read from the input # statement to a file. To enable the use of input #
When writing data to a file, you must use the write # statement instead of the print # statement. Use Write #
Statement to ensure that each data field is correctly separated.

Example:
In this example, the input # statement is used to read data in the file into two variables. In this example, assume that the testfile contains several lines in write #
The data written by the statement. That is to say, the string in each row is enclosed in double quotation marks and separated from the number by a comma, for example, ("hello", 234 ).

Dim mystring, mynumber
Open "testfile" for input as #1'
Open the input file.
Do while not EOF (1) 'loops to the end of the file.
Input #1,
Mystring and mynumber read data into two variables.
Debug. Print mystring,
Mynumber displays data in the immediate window.
Loop
Close #1 'close the file.

9. Write # statement

Syntax: write # filenumber, [outputlist]

Function: write data to an ordered file.

Note: Data written by write # is read from the file using input.
If outputlist is omitted
Add a comma to print a blank line to the file. Multiple Expressions can be separated by white spaces, semicolons, or commas. The blank is equivalent to the semicolon.

When writing data into a file with write #, several general conventions will be followed, so that no matter what region is available, input # can read and interpret the data correctly:

· Use periods as decimal delimiters when writing numeric data.

· For boolean data, print # True # or print # False #. No value is set to true or false in any region.
These two keywords are translated.

· Use a common date format to write data of the date type to a file. When the part of the date or time is lost or zero, only the existing part is written to the file.

· If the outputlist data is empty, no data is written to the file. For NULL data, # null # is required #.

· If the outputlist data is null, # null # is written to the file.

· For error data, the output looks the same as # error errorcode. No keyword error is entered in any region.
Translate.
Unlike the print # statement, when writing data to a file, write #
The statement inserts a comma between the project and the quotation mark used to mark the string. There is no need to type a clear delimiter in the list. Write #
After the last character is written into the file, a new line character is inserted, that is, the carriage return line break (CHR (13) + CHR (10 )).

Example:
Open "F:/test.txt" for output as #1 'Open the output file.
Write
#1, "Hello World", 1234 'Write Data separated by commas.
Write #1, 'Write blank rows.

Dim mybool, mydate, mynull, myerror
'Assign values to boolean, date, null, and error.
.
Mybool = false: mydate = # February 12,196 9 #: mynull = NULL
Myerror
= Cverr (0, 32767)
'Boolean data is written in the format of # True # Or # False.
'
The date is written in the common date format, for example: #
'August 1, January 13, 1994. NULL data to # null #
Format.
'Error data is written in the format of # error code.
Write #1, mybool; "is
Boolean value"
Write #1, mydate; "is a date"
Write #1, mynull ;"
Is a null value"
Write #1, myerror; "is an error value"
Close
# 1' close the file.

We can see that the written content is:
"Hello World", 1234

# False #, "is a Boolean value"
#1969-02-12 #, "is a date"
# Null #,"
Is a null value"
# Error 32767 #, "is an error value"

10. Line input # statements

Syntax: line Input # filenumber, varname

Function: Read a row from an opened Sequence File and assign it to the string variable.

Note: Print # And line Input # are usually used in combination.
Line input #
The statement reads only one character from the file at a time until the carriage return (CHR (13) or carriage return-linefeed (CHR (13) + CHR (10 ))
So far. Enter-The linefeed will be skipped and will not be appended to the string.

Example:
Dim textline
Open "testfile" for input as #1 'open the file.
Do
While not EOF (1) 'loops to the end of the file.
Line input #1, textline'
Read a row of data and assign it to a variable.
Debug. Print textline' displays data in the current window.
Loop
Close
# 1' close the file.

11. Input Functions

Syntax: input (number, [#] filenumber)
Number indicates the number of characters to return.

Function: returns a string that contains characters in files opened in the input or binary format.

Note: Print # Or put is usually used to write data read by the input function to a file. The input function is only used to input or binary data.
Open files.
Unlike the input # statement
The function returns all the characters it reads, including commas (,), carriage returns, blank columns, linefeeds, quotation marks, and leading spaces.

Example:
Dim mychar
Open "F:/test.txt" for input as #1
Do while
Not EOF (1) 'loops to the end of the file.
Mychar = input (1, #1) 'is a single character.

Debug. Print mychar is displayed in the current window.
Loop
Close #1

The following function reads data from a text file to a string at a time (however, an error occurs if it contains Chinese characters because a text occupies 2 bytes ).

Public Function readtext (filename as string)

Dim fnum %, isopen as Boolean
On Error goto erro
Fnum =
Freefile ()
Open filename for input as # fnum
Isopen = true
Readtext
= Input (lof (fnum), fnum)

Erro:
If isopen then close # fnum
If err then
Debug. Print err. Number, Err. Description

End Function

12. Print # statement

Syntax: Print # filenumber, [outputlist]

The outputlist parameter is set as follows:
[{SPC (n) | tab [(n)]}] [expression] [charpos]

SPC (n) is used to insert white spaces in the output data, and N refers to the number of white spaces to be inserted.
Tab (N)
It is used to locate the insert point on an absolute column number. Here, n is the column number. Use a tab without parameters to locate the start position of the insertion point in the next printing area.
Expression
The numeric or string expression to print.
Charpos specifies the insertion point of the next character. Use a semicolon to position the insertion point after the previous display character. Use tab (N)
Locate the insertion point on an absolute column number, and use a non-parameter tab to locate the insertion point at the beginning of the next printing area. If charpos is omitted, the next character is printed in the next line.

Function: writes formatted and displayed data to an ordered file.

Note: line Input # Or input is usually used to read print # data written into the file.

Example:
Open "F:/test.txt" for output as #1 'Open the output file.
Print
#1. "This is a test" 'writes text data to a file.
Print
#1, 'Write blank rows into a file.
Print #1, "Zone 1 ";
Tab; "Zone 2" 'data is written to two zones (print zones ).
Print #1, "hello ";"
";" World "'separates two strings with spaces.
Print #1, SPC (5); "5 leading
Spaces "'writes five spaces before the string.
Print #1, tab (10 );
"Hello" 'write the data in column 10.

'Assign values to boolean, date, null, and error values.
Dim mybool, mydate, mynull,
Myerror
Mybool = false: mydate = #2/12/1969 #: mynull = NULL
Myerror
= Cverr (0, 32767)
'True, false, null, and error will automatically convert the format according to the system region.
'
The date is displayed in the standard short date format.
Print #1, mybool; "is a Boolean value"
Print
#1, mydate; "is a date"
Print #1, mynull; "is a null value"
Print
#1, myerror; "is an error value"
Close #1

The content written in the above Code is as follows:
This is a test

Zone 1 Zone 2
Hello World
5 leading Spaces

Hello
False is a Boolean Value
1969-2-12 is a date
Null is
Null Value
Error 32767 is an error value

13. Width # statement

Syntax: width # filenumber, width
Width is required. The value range is 0-255.
Before the start of a new line, it indicates the number of characters that can appear in the row. If width is equal to 0, the length of the row is not limited. The default value of width is 0.

Function: Specify the width of an output line to a file opened with an open statement.

Example:
Dim I
Open "F:/testfile.txt" for output as #1
Width #1,
5' set the output row width to 5.
For I = 0
9' loop 10 times.
Print #1, CHR (48 +
I); 'each row outputs five characters.
Next I
Close #1

The content written in the above Code is as follows:
01234
56789

 

(4) Processing binary files
To open a binary file, you can use the random and binary methods of the open statement. Get and put statements are used to read and write binary files.

1. Put statement

Syntax: Put [#] filenumber, [recnumber], varname

Recnumber is optional. Variant (long ). Record Number (file in random mode) or number of bytes (Binary
File), indicating that the write operation starts here.

Note: Get is usually used to read the file data written by put.

Example:
Dim num as long, text as string
Num = 12345
TEXT ="
String"
Open "F:/data. bin" for binary as # 1' open or create a binary file
Put
#1, num' write 4 bytes
Put #1 ,,
Text 'write 8 bytes (String Length: 8)
Close #1

2. Get statement

Syntax: Get [#] filenumber, [recnumber], varname

Recnumber is optional. Variant (long ). Record Number (file in random mode) or number of bytes (Binary
To start reading data here.

Function: read an opened disk file into a variable.

Note: Put is usually used to write the data read by get to a file.

Example: Read the content written in the above Code
Dim num as long, text as string
Open
"F:/data. bin" for binary as #1
Get #1, num
TEXT =
Space $ (8) 'prepare an 8-byte string
Get #1 ,,
Text' read
Debug. Print num, text
Close #1

In the displayed window, you can see the following:
12345 a string

3. Seek statement

Syntax: Seek [#] filenumber, position
The position is between 1 and ~
A number between 2,147,483,647 (equivalent to 2 ^ 31-1), indicating the location of the next read/write operation.

Function: In an open statement file, set the location of the next read/write operation.

Note: You can use the seek statement to specify the location where the get statement is read. However, the record number specified in the get and put statements overwrites the file location specified by the seek statement.

Example:
Dim maxsize, nextchar, mychar
Open "testfile" for input
#1
Maxsize = lof (1) 'gets the total number of characters in a file.
'
Read all records cyclically, but read from the last record.
For nextchar = maxsize to 1 step-1

Seek #1, nextchar 'sets the read/write location.
Mychar = input (1, #1 )'
Read a single character.
Next nextchar
Close #1

4. Seek Function

Syntax: Seek (filenumber)

Function: returns a long string that specifies the current read/write position in the file opened by the open statement.

Note: when using the get statement to read a file, you must use the lof function to determine whether the object has reached the end, rather than using the EOF function. You can use the seek function to determine the current position and then
Lof value comparison.

Example:
Do While seek (1) <lof (1)
'Continue reading
......
Loop

 

 

Example:

Dim filepath as string
Dim filenumber as integer
Dim Contents
As string

Dim comstr as string
Dim count as integer
Dim
Start as integer
Dim I as integer
Dim name as string

Filenumber = freefile ()
Filepath = "C:/scripts/insert.txt"

Count
= Inputbox ("Enter the number of records ")
Start = inputbox ("Enter the number of rows in the Start Cell of the record ")
Contents
= ""

Comstr = "insert into TMl (ID, name) values ("
For I = 1 to count
Step 1
Name = worksheets (1). cells (START, 1). Value

Contents = contents & I & ", '" & name &
"'" & CHR (10)
Start = start + 1
Next I

Open filepath for output as # filenumber
Print # filenumber,
Contents
Close # filenumber