使用VBA高效读取txt文件(或其他数据源)

Question

我有一列（大）数据存储在一个txt文件中。我需要在Excel工作表中复制列矢量。这是我的代码：

Dim t As Single
t = Timer
Dim sFile As String
inputFile = "C:Tempvector.txt"
Dim rowNum As Long
rowNum = 1
Dim dest As Range
Set dest = Sheet1.Cells(rowNum, 1)
Open inputFile For Input As #1
Do Until EOF(1)
    Input #1, ReadData
    If Not IsEmpty(ReadData) Then
        dest.Cells = ReadData
        rowNum = rowNum + 1
        Set dest = Sheet1.Cells(rowNum, 1)
    End If
Loop
Close #1 'close the opened file
Sheet1.[C2].Value = Timer - t

我想知道是否有更有效/更快的方法来完成同样的任务。为此，将txt文件转换为另一种格式（例如.csv、.xlsx或任何其他文件类型）而不是从.txt文件中读取行是否有意义？非常感谢您的帮助。S

Answer 1

在这个链接之后，我尝试了不同的解决方案。与最初问题中提出的代码相比，以下代码提供了更快的问题解决方案（在Excel中导入一列500000个随机数）。

Dim t As Single
t = Timer
Dim inputFile As String
inputFile = "C:Tempvector.txt"
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet
Set wbI = ThisWorkbook
Set wsI = wbI.Sheets("Sheet1")
Set wbO = Workbooks.Open(inputFile)
wbO.Sheets(1).Columns(1).Copy wsI.Columns(1)
wbO.Close SaveChanges:=False
Sheet1.[C2].Value = Timer - t

特别是，在20次试验后，平均计算时间为1.50秒，而在第一个代码中为10.2秒。希望这能有所帮助！

Answer 2

如果您想使用第一种方法（我建议使用这种方法，因为它不涉及通过Excel打开文件），那么您可以通过批处理打印来减少运行时间。此外，您可能需要考虑使用scripting.filesystemobject，而不是旧的IO接口。

请参阅下面的示例（注意，此代码尚未经过测试）

const path as string = ""
const max_print_rows as integer = 10000
dim print_start_cell as range
dim print_arr () as string
dim i as integer,j as long
dim fso as scripting.filesystemobject
dim in_file as scripting.textstream
set print_start_cell=thisworkbook.names("Start_Cell").referstorange
set fso=new scripting.filesystemobject
set in_file=fso.opentextfile(path,forreading)
redim print_arr(1 to max_print_rows,1 to 1)
do until in_file.atendofstream
  i=i+1
  print_arr(i)=in_file.readline
  if I=max_print_rows then
    print_start_cell.offset(j).resize(max_print_rows).value=print_arr
    j=j+i
    erase print_arr
    redim print_arr(1 to max_print_rows)
    i=1
  end if
loop
print_start_cell.offset(j).resize(max_print_rows).value=print_arr
erase print_arr
in_file.close
set in_file=nothing
set print_start_cell=nothing
set fso=nothing