The following code was originally in an excel 2003 .xls vba project. It works fine if there is less than 63k rows.
When the data grew to >63k rows, it hung, so of course I immediately saved the project as .xlsm to see if that would overcome the problem in Excel 2007. It seemed to get further but crashes ultimately crashes on the export function below.
When I kill excel, I see an error: " Method Text of Object Range failed. Run time error: -2147417848 (80010108) " I presume that's really the error and now what I caused when I end-tasked Excel.
I tried installing the 64 bit version of excel 2013 however the project wouldn't run at all (library errors or something like that). So then, I tried the 32 but version of Excel 2013 and have the same not-responding problem.
The sheet of data is only about 80,000 rows so it's well within the integer and long data type constraints.
I even tried increasing the memory allocation of my VM from 4 gigs ram to 12 gigs. It made no difference.
Public Sub MF_Export_File()
Const DELIMITER As String = "" 'Normally none
Const PAD As String = " " 'or other character
Dim vFieldArray As Variant
Dim myRecord As Range
Dim nFileNum As Long
Dim i As Long
Dim sOut As String
vFieldArray = Array(9, 4, 3, 5, 2, 8, 8, 30, 6, 10, 8, 2, 3, 9, 25)
nFileNum = FreeFile
Open Range("MF_XFER_Path").Value & "\" & Range("To_MF_File").Value For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
For i = 0 To UBound(vFieldArray)
sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
String(vFieldArray(i), PAD), vFieldArray(i))
Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
sOut = Empty