VBA逐行读取文本文件并将每一行保存到新的excel工作表中

我有一个 txt 文件,其中包含几个原始文件(> 1000)。我想逐行读取此文件并将它们每次复制到新的 Excel 工作表(单元格 A1)。 txt 文件的每个原始文件都包含 excel 公式,用于通过 Internet 从财务数据库生成数据请求。所以每次它都必须从数据库中提取数据。我尝试自己编写代码,我尝试了两种逐行读取数据的可能性,但我收到了该行的错误消息,我将行写入单元格:

Sheet_i.Cells(1, 1).Value = TextLine    

我不明白,我在哪里犯了错误。

这是我的第一个代码,它使用 fso:

Sub ReadtxtFileIntoSeveralSheets()

Dim fso As FileSystemObject: Set fso = New FileSystemObject
Dim txtStream As TextStream
Dim TextLine As String
Dim i As Long 
Dim Sheet_i As Worksheet

' Get a FileSystem object
Set fso = New FileSystemObject

' get the file you want
Set txtStream = fso.OpenTextFile("D:excelformula.txt", ForReading, False)

i = 0 'to offset column for each line

' Read the file one line at a time
   Do While Not txtStream.AtEndOfStream

    TextLine = txtStream.ReadLine 'read line
    ' create new active worksheet

    Set Sheet_i = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    'paste the line to A1

     Sheet_i.Cells(1, 1).Value = TextLine

    i = i + 1
Loop

' Close file
txtStream.Close

Set txtStream = Nothing
Set fso = Nothing

End Sub

这是我的第二个代码:

Sub Data_copy_several_sheets_2()

Dim FileNum As Integer
Dim DataLine As String
Dim Sheet_i As Worksheet

FileNum = FreeFile()
Open "D:excelformula.txt" For Input As #FileNum

 While Not EOF(FileNum)
    Line Input #FileNum, DataLine ' read in data 1 line at a time
    Set Sheet_i = Worksheets.Add(after:=Worksheets(Worksheets.Count))

    Sheet_i.Cells(1, 1).Value = DataLine 'fill cell

 Wend
 Close #FileNum
End Sub

有人可以帮我完成这项任务吗?

stack overflow VBA Read txt file line by line and save each line to the new excel worksheet
原文答案

答案:

作者头像
Sheet_i.Cells(1, 1).Value = DataLine 

应该

Sheet_i.Cells(1, 1).Formula = DataLine

为了安全起见,您可能还想修剪该行并删除任何多余的空格:

Sheet_i.Cells(1, 1).Formula = Trim(DataLine)

如果您仍然没有得到任何结果,请确保正确的应用程序设置到位:

Sub SwitchOnDefaults()
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub