Pasting large arrays onto worksheet

Jul 9, 2012
Reaction score
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.


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


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




Set oFSTR = Nothing

End Sub


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question