什么是在VBA中逐行读取大文件的超快方法？

问题描述

我相信我已经想出了一种非常有效的方法来逐行读取非常大的文件。请告诉我，如果你知道更好/更快的方式或看到改善的余地。我想在编码方面做得更好，所以任何建议你都会很好。希望这是其他人可能会觉得有用的东西。

它似乎比使用我的测试中的Line Input快8倍。 b
$ b

 '该函数将文件读入字符串。 '
'我在使用VBA和.NET编程Excel一书中发现了这个问题。 '
 Public Function QuickRead（FName As String）As String 
 Dim I As Integer 
 Dim res As String 
 Dim l As Long 
 
 I = FreeFile 
l = FileLen（FName）
 res =空格（l）
打开二进制访问的FName读为#I 
获取#I，，res 
关闭I 
 QuickRead = res 
 End Function 
 
这个函数和Line Input语句一样工作
 Public Sub QRLineInput（_ 
 ByRef strFileData As String，_ 
 ByRef lngFilePosition As Long，_ 
 ByRef strOutputString，_ 
 ByRef blnEOF As Boolean _ 
）
 On Error GoTo LastLine 
 strOutputString = Mid $（strFileData，lngFilePosition ，_ 
 InStr（lngFilePosition，strFileData，vbNewLine） -  lngFilePosition）
 lngFilePosition = InStr（lFilePosition，strFileData，vbNewLine）+ 2 
退出子
 LastLine：
 blnEOF = True 
 End Sub 
 
 Sub Test（）
 Dim strFilePathName As String：strFil ePathName =C：\Fld\File.txt
 Dim strFile As String 
 Dim lngPos As Long 
 Dim blnEOF As Boolean 
 Dim strFileLine As String 
 
 strFile = QuickRead（strFilePathName）& vbNewLine 
 lngPos = 1 
 
直到blnEOF 
调用QRLineInput（strFile，lngPos，strFileLine，blnEOF）
 Loop 
 End Sub 
  

感谢您的建议！

解决方案

您可以使用Scripting.FileSystemObject来做这件事。
从：

例如：

$ p $ Set objFSO = CreateObject（Scripting.FileSystemObject）
Set objFile = objFSO.OpenTextFile（C ：\FSO\ServerList.txt，1）
Do直到objFile.AtEndOfStream
strLine = objFile.ReadLine
MsgBox strLine
Loop
objFile.Close


I believe I have come up with a very efficient way to read very, very large files line-by-line. Please tell me if you know of a better/faster way or see room for improvement. I am trying to get better at coding, so any sort of advice you have would be nice. Hopefully this is something that other people might find useful, too.

It appears to be something like 8 times faster than using Line Input from my tests.

'This function reads a file into a string.                        '
'I found this in the book Programming Excel with VBA and .NET.    '
Public Function QuickRead(FName As String) As String
    Dim I As Integer
    Dim res As String
    Dim l As Long

    I = FreeFile
    l = FileLen(FName)
    res = Space(l)
    Open FName For Binary Access Read As #I
    Get #I, , res
    Close I
    QuickRead = res
End Function

'This function works like the Line Input statement'
Public Sub QRLineInput( _
    ByRef strFileData As String, _
    ByRef lngFilePosition As Long, _
    ByRef strOutputString, _
    ByRef blnEOF As Boolean _
    )
    On Error GoTo LastLine
    strOutputString = Mid$(strFileData, lngFilePosition, _
        InStr(lngFilePosition, strFileData, vbNewLine) - lngFilePosition)
    lngFilePosition = InStr(lngFilePosition, strFileData, vbNewLine) + 2
    Exit Sub
LastLine:
    blnEOF = True
End Sub

Sub Test()
    Dim strFilePathName As String: strFilePathName = "C:\Fld\File.txt"
    Dim strFile As String
    Dim lngPos As Long
    Dim blnEOF As Boolean
    Dim strFileLine As String

    strFile = QuickRead(strFilePathName) & vbNewLine
    lngPos = 1

    Do Until blnEOF
        Call QRLineInput(strFile, lngPos, strFileLine, blnEOF)
    Loop
End Sub

Thanks for the advice!

解决方案

You can use Scripting.FileSystemObject to do that thing.From the Reference:

And a quick example:

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\FSO\ServerList.txt", 1)
Do Until objFile.AtEndOfStream
 strLine = objFile.ReadLine
 MsgBox strLine
Loop
objFile.Close

这篇关于什么是在VBA中逐行读取大文件的超快方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！