阅读文件夹中的所有文件，并在Excel中显示内容 [英] Reading all files in Folder and showing content in Excel

问题描述

我想显示一个文件夹和excel中的7000个文件内容？

我找到了一块帮助我但只读一个的代码一个但是，我想一次性阅读7000。请帮忙。

  Option Explicit 
 Sub Import_TXT_File（）
 Dim strg As Variant 
 Dim EntireLine As String 
 Dim FName As String 
 Dim i As String 
 
 Application.ScreenUpdating = False 
 FName = Application.GetOpenFilename（Text Files（* .txt），*。 txt，选择要导入的文件）
打开输入访问的FName读为＃1 
i = 1 
虽然不是EOF（1）
行输入＃1，完整列
 strg = EntireLine 
'将Sheet1更改为相关工作表名称
'将A更改为相关列名
表（Sheet1）。范围（A & i）.Value = strg 
i = i + 1 
 Wend 
 EndMacro：
错误GoTo 0 
 Application.ScreenUpdating = True 
关闭＃ 1 
 End Sub 
  

解决方案

user1185158

当您阅读7000个文件时，您使用的代码将非常慢。也没有一个可以读取7000个文件的代码。你将不得不循环通过7000个文件。但是有一个好消息:)而不是循环遍历文本文件中的每一行，您可以将整个文件读入数组，然后将其写入excel。例如，看到这段代码与上面的代码相比非常快。

TRIED AND TESTED

  Sub Sample（）
 Dim MyData As String，strData（）As String 
 
打开C：\ MyFile.TxtFor Binary As＃1 
 MyData = Space $（LOF（1））
获取＃1，MyData 
关闭＃1 
 strData（）= Split MyData，vbCrLf）
 End Sub 
  

现在在循环中使用相同的代码可以写它成为Excel文件

 '~~>将其更改为相关路径
 Const strPath As String =C：\Temp\
 
子样本（）
 Dim ws As Worksheet 
 Dim MyData As String，strData（）As String 
 Dim WriteToRow As Long，i As Long 
 Dim strCurrentTxtFile As String 
 
设置ws =表格（Sheet1）
 
'~~>从行1开始
 WriteToRow = 1 
 
 strCurrentTxtFile = Dir（strPath&* .Txt）
 
'~~>循环通过文件夹中的所有文本文件
 Do While strCurrentTxtFile<> 
 
'~~>打开文件1，将其读入数组
打开strPath& strCurrentTxtFile For Binary As＃1 
 MyData = Space $（LOF（1））
获取＃1，MyData 
关闭＃1 
 
 strData（）=拆分（MyData，vbCrLf）
 
'~~>从数组读取并写入Excel 
对于i = LBound（strData）到UBound（strData）
 ws.Range（A& WriteToRow）.Value = strData（i）
 WriteToRow = WriteToRow + 1 
 Next i 
 
 strCurrentTxtFile = Dir 
循环
 
 MsgBoxDone
 End Sub 
  

上面的代码是读取表1中的7000个文本文件的内容（一个在另一个之外）。另外我还没有包括错误处理。

注意：如果您正在读取较大的文本文件，比方说每个文件有10000行，那么您将不得不调整在上述情况下的代码，您将收到错误。例如

7000文件* 10000行= 70000000行

Excel 2003有65536行和Excel 2007/2010有1048576行。

所以一旦 WriteRow 达到最大行，你可能想要将文本文件内容读入Sheet 2等...

HTH

Sid

I want to show 7000 files content that are in a folder and in excel?

I have a found a piece of code that helped me but its only reading one by one. However, I want to read 7000 all in one go. Please help.

 Option Explicit
 Sub Import_TXT_File()
 Dim strg As Variant
 Dim EntireLine As String
 Dim FName As String
 Dim i As String

 Application.ScreenUpdating = False
 FName = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Choose File to Import")
 Open FName For Input Access Read As #1
 i = 1
 While Not EOF(1)
 Line Input #1, EntireLine
 strg = EntireLine
 'Change "Sheet1" to relevant Sheet Name
 'Change "A" to the relevant Column Name
 Sheets("Sheet1").Range("A" & i).Value = strg
 i = i + 1
 Wend
 EndMacro:
 On Error GoTo 0
 Application.ScreenUpdating = True
 Close #1
 End Sub

解决方案

user1185158

The code which you are using will be very slow when you are reading 7000 files. Also there is no code which can read 7000 files in 1 go. You will have to loop through the 7000 files. However there is one good news :) Instead of looping through every line in the text file, you can read the entire file into an array and then write it to excel. For example see this code which is very fast as compared to the code that you have above.

TRIED AND TESTED

Sub Sample()
    Dim MyData As String, strData() As String

    Open "C:\MyFile.Txt" For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)
End Sub

Now using the same code in a loop we can write it into an Excel File

'~~> Change this to the relevant path
Const strPath As String = "C:\Temp\"

Sub Sample()
    Dim ws As Worksheet
    Dim MyData As String, strData() As String
    Dim WriteToRow As Long, i As Long
    Dim strCurrentTxtFile As String

    Set ws = Sheets("Sheet1")

    '~~> Start from Row 1
    WriteToRow = 1

    strCurrentTxtFile = Dir(strPath & "*.Txt")

    '~~> Looping through all text files in a folder
    Do While strCurrentTxtFile <> ""

        '~~> Open the file in 1 go to read it into an array
        Open strPath & strCurrentTxtFile For Binary As #1
        MyData = Space$(LOF(1))
        Get #1, , MyData
        Close #1

        strData() = Split(MyData, vbCrLf)

        '~~> Read from the array and write to Excel            
        For i = LBound(strData) To UBound(strData)
            ws.Range("A" & WriteToRow).Value = strData(i)
            WriteToRow = WriteToRow + 1
        Next i

        strCurrentTxtFile = Dir
    Loop

    MsgBox "Done"
End Sub

What the above code does is that it reads the contents of the 7000 text files in sheet 1 (one below the other). Also I have not included error handling. Please do that.

CAUTION: If you are reading heavy text files, say, each file has 10000 lines then you will have to tweak the code in the above scenario as you will get errors. for example

7000 Files * 10000 lines = 70000000 lines

Excel 2003 has 65536 rows and Excel 2007/2010 has 1048576 rows.

So once the WriteRow reaches the maximum row, you might want to read the text file contents into Sheet 2 and so on...

HTH

Sid

这篇关于阅读文件夹中的所有文件，并在Excel中显示内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！