rookie777
07-26-2015, 06:01 PM
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
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