Copying data to a textfile is very slow, possible to speed it up?-CodePudding

I have a worksheet that is stored in an array called EBS There's about 11000 rows and 18 columns. There are both integers, dates and strings in there.

It's takes over a minute to run the code below. Is there a faster way to do this in vba? This macro repeats over and over and I would like it to be faster. The data needs to be as current as possible.

This is the part that stores the data:

    Dim FilePath As String
    FilePath = "H:\Data\EafX09475a8.txt"
    
    Dim DataLine As String
    
    On Error GoTo NoPath
    
    Open FilePath For Output As #1
    
    
    For i = 1 To UBound(EBS, 1)
        For j = 1 To UBound(EBS, 2)
            If j = 1 Then
                DataLine = EBS(i, j)
            Else
                DataLine = DataLine & vbTab & EBS(i, j)
            End If
        Next j
        Print #1, DataLine
    Next i
    Print #1, Now
    Close #1
    
NoPath:
On Error GoTo 0

CodePudding user response：

Save Workbook As Tab-Delimited Text File

This adds a new workbook, writes the array to it, and the Now, and saves and closes the file.

With Workbooks.Add
    With .Worksheets(1).Range("A1")
        .Resize(UBound(EBS, 1), UBound(EBS, 2)).Value = EBS
        .Offset(UBound(EBS, 1)).Value = Now
    End With
    Application.DisplayAlerts = False  ' overwrite without confirmation
    .SaveAs Filename:=FilePath, FileFormat:=xlText
    Application.DisplayAlerts = True
    .Close SaveChanges:=False
End With