Most efficient way to copy array data (big amount of data) to a txt file

[rcavalcante]

Hi all,

I'm using VBA to get some information from an array to a txt file. The main issue is that I don't know previously the size of the array, but I do know that it is a big one and that's taking too long time to complete the action. I have to reduce the amount of time because i'll have to run this action in a loop.

I implemented the sub below to try to complete my task:


Public Sub CreateFile(ByRef linhas() As String, sht As String)
Dim myFile As String, size As Long
Dim i As Long
size = WorksheetFunction.CountA(Worksheets(sht).Columns(1)) ' getting the size of array

myFile = Worksheets(1).Cells(1, 10).Value & "\" & Worksheets(1).Cells(9, 2).Value 'creating nem folder to save the txt file.


If Len(Dir(myFile, vbDirectory)) = 0 Then
MkDir myFile
End If

myFile = myFile + "\" & sht & ".txt" ' creating new txt file
Open myFile For Output As #1

For i = 1 To size
Print #1, linhas(i)
Next i

Close #1
End Sub
Then, I thought, maybe it is taking too long because I'm using a lot of print commands. So, I tried to adjust the final loop to this form, where "teste" is a string variable



For i = 1 To size
teste = teste & linhas(i) & vbCrLf
Next i
Print #1, teste
Close #1

It haven't made my code any faster also.
Therefore, does anyone have another idea of how to make it faster? I'll have to run this routine more than 30 times, and the arrays have more than 3000 lines each.

Regards,

Rafael.

[snb]

See: http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.15.2.3

[rcavalcante]

Thanks snb for the help. Unfortunately, I didn't understant it well.
So, in this code, the region in the worksheet where there's the data is copied. So then, I could use just an "Ctrl + V " function at the txt file.
Is it the logic of this code?

What I want to add to the txt file is not at the worksheet. I use the informations contained in there to creating a line code that'll be read by a circuit simulator. For example, if there is a row like this:





106
6689888820959_6689908820958
103
104
105
1
S477AN

DEF
2,5
H5




This row from my sheet will become something like:

new variable 106 with properties 103 and 104 and 105 is from type S477AN and it has 2,5 km



Therefore, I cannot just copy the content in the worksheet and then paste it in the text file

[Kenneth Hobs]

Try using Join() to join your string array into one string and Print once. e.g.


Public Sub CreateFile(ByRef linhas() As String, sht As String) Dim myFile As String

myFile = Worksheets(1).Cells(1, 10).Value & "\" & Worksheets(1).Cells(9, 2).Value 'creating nem folder to save the txt file.


If Len(Dir(myFile, vbDirectory)) = 0 Then MkDir myFile

myFile = myFile + "\" & sht & ".txt" ' creating new txt file
Open myFile For Output As #1

Print #1, Join(linhas(); vbCrLf)

Close #1

End Sub

[snb]

Please post a sample workbook and a sample resulting txt file.

[westconn1]

you could try assigning the array to a worksheet in a new workbook
then saving the workbook as a text file

test to see if it is faster

[SamT]

Dim TempBook As New Excel.Workbook
Dim TW As Workbook
Set TW = ThisWorkbook

LastRow = UsedRange,Rows.Count

With TempBook.Sheets(1)
.Range.Cells(1,1), CellsLastRow, 1)) = "new variable "
.Range(B:B) = TW.Range("C:C")
.Range.Cells(1,3), CellsLastRow, 3)) = ." with properties "
.Range("D:D") = TW.Range(""D:D")
.Range.Cells(1,5), CellsLastRow, 5)) = ." and "
.Range("E:E") = TW.Range(""E:E")
.Range.Cells(1,3), CellsLastRow, 3)) = ." and "
.Range("G:G") = TW.Range(""F:F")
'YOU GET THE IDEA.
End with

Tempbook. Save as Etc

From 3000x17 array to 17 pastes.