rcavalcante
10-20-2014, 05:32 AM
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.
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.