好吧,我假设所有 6000 个文件都具有相同的格式。
我的测试条件
- 我有一个名为 C:\Temp\ 的文件夹,其中包含 6000 个 CSV 文件
- 所有 csv 文件都有 40 行和 16 列
- 在 Excel 2010 中进行了测试。无法访问 2011。将在 2011 年进行测试,大约需要 30 分钟。
我运行了下面的代码,代码只花了 4 秒。
Option Explicit
Sub Sample()
Dim strFolder As String, strFile As String
Dim MyData As String, strData() As String
Dim FinalArray() As String
Dim StartTime As String, endTime As String
Dim n As Long, j As Long, i As Long
strFolder = "C:\Temp\"
strFile = Dir(strFolder & "*.csv")
n = 0
StartTime = Now
Do While strFile <> ""
Open strFolder & strFile For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
strData() = Split(MyData, vbCrLf)
ReDim Preserve FinalArray(j + UBound(strData) + 1)
j = UBound(FinalArray)
For i = LBound(strData) To UBound(strData)
FinalArray(n) = strData(i)
n = n + 1
Next i
strFile = Dir
Loop
endTime = Now
Debug.Print "Process started at : " & StartTime
Debug.Print "Process ended at : " & endTime
Debug.Print UBound(FinalArray)
End Sub
文件夹截图
代码输出的屏幕截图
UPDATE
好的,我在MAC上测试过
我的测试条件
- 我的桌面上有一个名为 Sample 的文件夹,其中包含 1024 个 CSV 文件
- 所有 csv 文件都有 40 行和 16 列
- 在Excel 2011中测试过。
我运行了下面的代码,代码花费了不到 1 秒的时间(因为只有 1024 个文件)。所以我预计它会再次运行 4 秒,以防有 6k 个文件
Sub Sample()
Dim strFile As String
Dim MyData As String, strData() As String
Dim FinalArray() As String
Dim StartTime As String, endTime As String
Dim n As Long, j As Long, i As Long
StartTime = Now
MyDir = ActiveWorkbook.Path
strPath = MyDir & ":"
strFile = Dir(strPath, MacID("TEXT"))
'Loop through each file in the folder
Do While Len(strFile) > 0
If Right(strFile, 3) = "csv" Then
Open strFile For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
strData() = Split(MyData, vbCrLf)
ReDim Preserve FinalArray(j + UBound(strData) + 1)
j = UBound(FinalArray)
For i = LBound(strData) To UBound(strData)
FinalArray(n) = strData(i)
n = n + 1
Next i
strFile = Dir
End If
strFile = Dir
Loop
endTime = Now
Debug.Print "Process started at : " & StartTime
Debug.Print "Process ended at : " & endTime
Debug.Print UBound(FinalArray)
End Sub
文件夹截图
代码输出的屏幕截图