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.