How to fast read/write Excel VBA array contents to and from text file!

[rookie777]

Hello,
I'm looking for a way how to fast export/import large file(over 600 000 rows and 20 columns) to and from TXT file. There is some error ("Operation is not allowed when the object is closed") in this code and I can't figure it out where the problem is.File should be comma delimited.
Thank you for your help.

John

Sub FetchSourceData_CreateTextFileFromRecordset()

Dim outputTextFile As Object 'Could be declared as a TextStream
Dim fso As New Scripting.FileSystemObject
Dim i As Long, j As Long, file2Write As Long
Dim ubound1 As Long, ubound2 As Long
Dim recordsetDataArray As Variant
Dim strText As String
Set myRecordSet = New ADODB.Recordset
Set outputTextFile = fso.CreateTextFile("E:\MyTextFile.txt", True)

recordsetDataArray = myRecordSet.GetRows
ubound1 = UBound(recordsetDataArray, 2)
ubound2 = UBound(recordsetDataArray, 1)
For i = 0 To ubound1
strText = ""
For j = 0 To ubound2
strText = strText & recordsetDataArray(j, i) & ","
Next j
strText = Left(strText, (Len(strText) - 1))
outputTextFile.WriteLine strText
Next i
outputTextFile.Close
Set outputTextFile = Nothing
Set fso = Nothing
End Sub

[mancubus]

hi.
please use code tags when pating your code here.
# button will do it for you.
what is myRecordSet?

this may help:
http://www.cpearson.com/excel/ImpText.aspx

you can save a worksheet as txt as well.

[rookie777]

This is the link I have my code from

w w w.vbaexpress.com/forum/showthread.php?46617-Solved-How-to-fast-write-Excel-VBA-array-contents-to-a-text-file!

I don't understand it very well. All I need is to find solution how to read from and write to large txt file (more then 600 000 rows and 20 columns) in Excel. I've tried already the code from the link you posted, but it takes forever to import text.

[mancubus]

it was me who replied first to that thread as well.
and posted the the same link to pearsons's site.

from OP's last post: "myRecordSet has been declared as a ADODB.Recordset elsewhere"

check this out to import a text file using ADO:
http://www.erlandsendata.no/english/...acimportadotxt