Send excel row to text file where large amount of columns used

This example sends a large amount of data from a row into a text file in the same path as the excel file is saved.

With thousands of columns per row available, it would take a long time and be open to errors to assign each cell on a row, set the delimit character between each when all you need to do is have a column/row loop.

This goes from the 5th row to the last row, taking each cell on the row (up to the maximum columns) and delimiting it with a ~ and outputting it to a text file. It is very quick and very efficient.

This simple and short piece of code is invaluable for exporting large amounts of data to a text file.


Set ws = Worksheets("Data")

With ws
lastr = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

FileUser = ActiveWorkbook.Name
'On Error Resume Next

FileuserDocFILE = ThisWorkbook.Path & "\Text_Output.txt" 'name the .txt database to hold the info
'On Error GoTo 0

For r = 5 To lastr
Application.StatusBar = r

For c = 1 To 682 'Takes all the cells up to column 682
output = output & ws.Cells(r, c) & "~"
Next c

Open FileuserDocFILE For Append As #1 'Use Output instead of APPEND to overwrite data
Print #1, output
Close #1

output = Empty

Next r






Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology