Efficient way of exporting a range of cells spanning two columns

Question

I am new to VBA and have made necessary adjustments to a script I have found online. What I want to do is to get the values of the range G2:H40000 from an Excel file, with each row cell separated by a Tab, then saved to a UTF-8 text file.

I have tested the script for range G2:H12, and it works. But when it involves 40,000+ row (like G2:H40000) with each cell in column H containing many text, the script takes so long to finish that I had to stop the process after more than an hour.

How can I make the script below run faster and be more efficient?

Sub ExportToTxt()

    Dim data As Range, row As Range, cell As Range
    Dim output As String

    Set data = Range("G2:H40000")

    For Each row In data.Rows
      For Each cell In row.Cells(1, 1) '1st param 1 excludes the table header
          output = output & cell.Value & vbTab & cell.Offset(0, 1).Value ' Offset 2nd param gets the adjacent cell to its right
      Next cell
      output = output & vbNewLine
    Next row

    ' Write to a UTF-8 encoded text file:
    Filename = "C:\Users\lenovo\Desktop\output.txt"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set out = fso.CreateTextFile(Filename, True, True) 'Syntax: object.CreateTextFile(filename[,overwrite[,unicode]])
    out.WriteLine (output)

    out.Close

End Sub

add a new sheet, copy cells G2:H40000 to that sheet, then save the workbook as tab separated with that sheet active, then delete the sheet and resave as a "proper" workbook. — Harassed Dad, Feb 17 '20 at 11:03
How long does it take for Range("G2:H400") and what is the size of the text file (in mega bytes approx) ? — CDP1802, Feb 17 '20 at 11:17
Also, try adding `Application.ScreenUpdating=False` at start of your code and `Application.ScreenUpdating=True` at end of code. Check [How to speed VBA code](https://stackoverflow.com/questions/47089741/how-to-speed-up-vba-code) — Foxfire And Burns And Burns, Feb 17 '20 at 11:25

FaneDuru · Accepted Answer · 2020-02-17T12:11:47.753

3

Try this code, please. It must be very fast...

Sub ExportToTxt()
    Dim data As Range, fso As Object, out As Object, k As Long, i As Long
    Dim output As String, arrDat As Variant, fileName As String

    Set data = Range("G2:H40000")
    arrDat = data.Value 'an array is created from the above range

    Filename = "C:\Users\lenovo\Desktop\output.txt"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set out = fso.CreateTextFile(fileName, True, True) 'Syntax: object.CreateTextFile(filename[,overwrite[,unicode]])
        For k = 1 To UBound(arrDat, 1)
           For i = 1 To UBound(arrDat, 2)
              output = output & arrDat(k, i) & vbTab
           Next i
           'the line string is created by array row elements concatenation:
           output = left(output, Len(output) - 1)
           out.WriteLine (output): output = ""'make the output string null after writing it in the text file.
        Next k
    out.Close
End Sub

It can be a little simpler for only two columns, but it is designed to work for how many columns you need...

edited Feb 17 '20 at 12:11

answered Feb 17 '20 at 11:39

FaneDuru

38,298
4
19
27

Thank you. I tried the code but I had to stop it from running after it continuously was creating a text file that reached 4 GB. I estimate that the text file should only be around 74 MB (after doing a manual copy to a new sheet and save as Tab-delimited text file). – JAT86 Feb 17 '20 at 12:16
1

@JAT86: Please, refresh the page and test the code again. It was a mistake in the previous version (output string was not made nul after each line)... It must take seconds and produce a file of less then 1 MB, according to your records volume. I tested it for 40000 rows, but for only one digit per row and it have a size of less then 400 KB... – FaneDuru Feb 17 '20 at 12:19
@JAT86: Just for my curiosity: What is your text file size after producing it using the code? – FaneDuru Feb 17 '20 at 13:14
Thank you very much. It took only about 10 to 15 seconds to produce a 172 MB text file for range G2:H42000 (which I used). Each cell in column G contains 28 characters, while each cell in column H contains about 2,200 characters. – JAT86 Feb 17 '20 at 16:23

Efficient way of exporting a range of cells spanning two columns

1 Answers1