Pasting large arrays onto worksheet

[newuser123]

Does anybody know if there is a limitation to the size of anarray in Excel 2010?

I am trying to get all files from a folder, read them into an array and paste teh contents back onto a worksheet.

I know for a fact the sum of all rows of data in the folder does NOT exceed 1 million rows (the limit in Excel 2010) but somehow the code hangs when the array is large, ie 70,000.

It hangs in this line:

Cells(1, 1).Resize(UBound(sArray, 1), 1) = Application.WorksheetFunction.Transpose(sArray)

with a run-time 13 type mismatch error message.

Thanks


Option Explicit

Sub Get_Files()

Dim sArray() As String
Dim Directory As String
Directory = "C:\Test\"
Dim fso As Scripting.FileSystemObject
Dim folder As Scripting.folder, file As Scripting.file

Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder(Directory)

For Each file In folder.Files

Call FileToArray(FileName:=file, TheArray:=sArray)

Cells(lrow + 1, 1).Resize(UBound(sArray, 1), 1) = Application.WorksheetFunction.Transpose(sArray)

Erase sArray

Next

End Sub

Public Sub FileToArray(ByVal FileName As String, ByRef TheArray As Variant)

Dim oFSO As New FileSystemObject
Dim oFSTR As Scripting.TextStream
Dim ret As Long
Dim lCtr As Long

If Dir(FileName) = "" Then Exit Sub

If VarType(TheArray) <> vbArray + vbString Then Exit Sub

On Error GoTo ErrorHandler

Set oFSTR = oFSO.OpenTextFile(FileName)

Do While Not oFSTR.AtEndOfStream

ReDim Preserve TheArray(lCtr) As String

TheArray(lCtr) = oFSTR.ReadLine
lCtr = lCtr + 1
DoEvents

Loop

oFSTR.Close

ErrorHandler:

Set oFSTR = Nothing

End Sub