Too big for excel? VBA Stops responding and Crashes on . . .

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.

Any ideas?

Thanks,
Mike

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
        
        Sheets("Results").Select
        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)
            With myRecord
                For i = 0 To UBound(vFieldArray)
                    sOut = sOut & DELIMITER & Left(.Offset(0, i).Text & _
                            String(vFieldArray(i), PAD), vFieldArray(i))
                Next i
                Print #nFileNum, Mid(sOut, Len(DELIMITER) + 1)
                sOut = Empty
            End With
        Next myRecord
        Close #nFileNum
    End Sub

Open in new window

mike2401Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
Can you post a sample data so  we can see what is the range and how it interacts. ?
gowflow
0
gowflowCommented:
Try putting Doevents after End With.

gowflow
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
leonstrykerCommented:
It seems like you are trying to create a Text file from a range. Would it be simpler to copy the data to a different spreadsheet and save then save that as a text file?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

mike2401Author Commented:
@gowflow is GENIUS !!!

When I added the doevents, and kept refreshing windows explorer in the output folder, I saw the file size kept increasing and increasing.  So, it wasn't frozen, but rather actually writing the file.

Sadly, it takes 40 minutes for 68,363 rows, but at least it works!!!!

Thanks a million !!!

Mike
0
Rory ArchibaldCommented:
The Text property gets slower and slower the more rows you use it on. If you really need the formatted value, you might be better off using .Value2 and then formatting the result using the NumberFormat property.
0
mike2401Author Commented:
And yes @LeonStryker, I am trying to create a text file from the range. I will open a separate call to see if there's a better way so that.  I want @gowflow to get all the points for this particular question.
Thanks!
Mike
0
mike2401Author Commented:
Thank you!
0
gowflowCommented:
Appreciated. I tried it on a small sample and saw there was no problem but when you mentioned 80000 this is when it got to my mind that the hanging was inevitable if not with DoEvents.

Tks for the appreciation.
gowlfow
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.