VBA文本数据文件读写
来源:互联网 发布:淘宝男士服装 编辑:程序博客网 时间:2024/06/09 19:46
来源
Reading/Writing Text Files
Being able to read from and write to text files in VBA is useful for the import/export of coordinate data as well as other file and configuration parameters.
Opening Files
To work with a text file you will typically need to dimension three variables:
- A String variable for the file name
Dim txtFileName As String
- A second String variable for the line of text to read/write
Dim txtFileLine As String
- A Long variable for a file number required by VBA
Dim txtFileNumber As Long
After the variables are dimensioned the next step is to find an available file number to use for opening the file and the required file I/O. Use the VBA FreeFile function to assign the next available number.
txtFileNumber = FreeFile
Once you have an available file number you can open the file.
Open For Read
Use the For Input option of the open command to open a text file for reading. The file must exist prior to issuing the open statement or VBA will raise an error. Checking for the existence of a file, and error handling will be discussed in future tips.
Open txtFileName For Input As #txtFileNumber
Open For Write
When opening a text file for writing you have two options. TheFor Output option will create a new file if it does not exist, or clear out the contents of the file if it does exist. TheFor Append option will also create a new file, but will add new lines on to the end of the file if it already exists.
Open txtFileName For Output As #txtFileNumber
Open txtFileName For Append As #txtFileNumber
Reading from a text file
Use the following loop structure as a template for processing the entire file:
Do While Not EOF(txtFileNumber)
Line Input #txtFileNumber, txtFileLine
‘
‘ Do something with the text here
‘
Loop
Text files are accessed sequentially which means if you are only interested a particular line you will need to skip the lines you are not interested in to get to the line you a want. The following loop structure will help you accomplish this:
For i = 1 to linesToSkip
Line Input #txtFileNumber, txtFileLine
Next
Line Input #txtFileNumber, txtFileLine
‘
‘ Do something with the text here
‘
Writing to a text file
Once you get your line of text formatted a simple print statement is all that’s needed to write to the file.
Print #txtFileNumber, txtFileLine
Closing files
Once you are done interacting with the text file you will need to close it.
Close txtFileNumber
Putting it all together
The following subroutine illustrates one way to read text from an input file, modify the line, and then write it to an output file.
Sub TextFileIO_Example()
Dim txtFileName_IN As String
Dim txtFileNumber_IN As Long
Dim txtFileLine_IN As String
Dim txtFileName_OUT As String
Dim txtFileNumber_OUT As Long
Dim txtFileLine_OUT As String
txtFileName_IN = "C:\Temp\TextFile_IN.txt"
txtFileName_OUT = "C:\Temp\TextFile_OUT.txt"
txtFileNumber_IN = FreeFile
Open txtFileName_IN For Input As #txtFileNumber_IN
txtFileNumber_OUT = FreeFile
Open txtFileName_OUT For Output As #txtFileNumber_OUT
Do While Not EOF(txtFileNumber_IN)
Line Input #txtFileNumber_IN, txtFileLine_IN
txtFileLine_OUT = "# " & txtFileLine_IN
Print #txtFileNumber_OUT, txtFileLine_OUT
Loop
Close txtFileNumber_OUT
Close txtFileNumber_IN
End Sub
Tags: VBA
Reading/Writing Text Files
Being able to read from and write to text files in VBA is useful for the import/export of coordinate data as well as other file and configuration parameters.
Opening Files
To work with a text file you will typically need to dimension three variables:
- A String variable for the file name
Dim txtFileName As String
- A second String variable for the line of text to read/write
Dim txtFileLine As String
- A Long variable for a file number required by VBA
Dim txtFileNumber As Long
After the variables are dimensioned the next step is to find an available file number to use for opening the file and the required file I/O. Use the VBA FreeFile function to assign the next available number.
txtFileNumber = FreeFile
Once you have an available file number you can open the file.
Open For Read
Use the For Input option of the open command to open a text file for reading. The file must exist prior to issuing the open statement or VBA will raise an error. Checking for the existence of a file, and error handling will be discussed in future tips.
Open txtFileName For Input As #txtFileNumber
Open For Write
When opening a text file for writing you have two options. TheFor Output option will create a new file if it does not exist, or clear out the contents of the file if it does exist. TheFor Append option will also create a new file, but will add new lines on to the end of the file if it already exists.
Open txtFileName For Output As #txtFileNumber
Open txtFileName For Append As #txtFileNumber
Reading from a text file
Use the following loop structure as a template for processing the entire file:
Do While Not EOF(txtFileNumber)
Line Input #txtFileNumber, txtFileLine
‘
‘ Do something with the text here
‘
Loop
Text files are accessed sequentially which means if you are only interested a particular line you will need to skip the lines you are not interested in to get to the line you a want. The following loop structure will help you accomplish this:
For i = 1 to linesToSkip
Line Input #txtFileNumber, txtFileLine
Next
Line Input #txtFileNumber, txtFileLine
‘
‘ Do something with the text here
‘
Writing to a text file
Once you get your line of text formatted a simple print statement is all that’s needed to write to the file.
Print #txtFileNumber, txtFileLine
Closing files
Once you are done interacting with the text file you will need to close it.
Close txtFileNumber
Putting it all together
The following subroutine illustrates one way to read text from an input file, modify the line, and then write it to an output file.
Sub TextFileIO_Example()
Dim txtFileName_IN As String
Dim txtFileNumber_IN As Long
Dim txtFileLine_IN As String
Dim txtFileName_OUT As String
Dim txtFileNumber_OUT As Long
Dim txtFileLine_OUT As String
txtFileName_IN = "C:\Temp\TextFile_IN.txt"
txtFileName_OUT = "C:\Temp\TextFile_OUT.txt"
txtFileNumber_IN = FreeFile
Open txtFileName_IN For Input As #txtFileNumber_IN
txtFileNumber_OUT = FreeFile
Open txtFileName_OUT For Output As #txtFileNumber_OUT
Do While Not EOF(txtFileNumber_IN)
Line Input #txtFileNumber_IN, txtFileLine_IN
txtFileLine_OUT = "# " & txtFileLine_IN
Print #txtFileNumber_OUT, txtFileLine_OUT
Loop
Close txtFileNumber_OUT
Close txtFileNumber_IN
End Sub
Tags: VBA
One Response to “Reading/Writing Text Files”
- Rick Says:
June 12th, 2015 at 8:21 am
Just a suggestion…
Consider changing the variable name from txtFileNumber to something like lngFileNumber since it is DIM’d as Long & not String.
- VBA文本数据文件读写
- R语言 | 数据文件读写
- R语言数据文件读写
- R语言数据文件读写
- R中读写数据文件
- 3.数据文件的读写
- ADO访问文本数据文件
- VBA 读写文件
- VBA 读写ini文件
- VBA 读写文件
- VBA读写文件
- 读写文本
- 读写文本
- 文本读写
- MATLAB如何读写Excel数据文件
- R语言数据文件的读写
- VBA创建文本文件、读写文本文件
- VBA创建文本文件、读写文本文件
- Linux系统时间与RTC时间
- 算法学习-棋盘格
- Android Studio对于外部引用的设置
- Android Intent传值和Bundle传值的异同
- Atitit.java c#这类编程语言的设计失败点attilax总结
- VBA文本数据文件读写
- C# MYbank ----------------------- 终级项目
- ButterKnife的使用、简化FindViewById
- usaco ★Zero Sum 和为零
- JS+CSS简易Tab效果
- 一档First Gear
- struts2 值栈浅析
- 邻接矩阵的深度优先遍历(递归以及非递归),广度优先遍历
- Atitit 多继承实现解决方案 java c#