Exporting block of data to a Text File

[mluetkem]

I have a block of data (5 columns by 396 rows) that I would like to export to a text file, tab delimited using a macro. Any ideas?

Mike

[MrShorty]

When I've done it, I've used the SaveAs method, indicating that the worksheet should be saved as a tab delimited text file. But then I have to execute another SaveAs to save the workbook as an Excel file (so I don't lose all of the other worksheets and formulas and such). I, too, would be interested in knowing if there's a better way to export a text file.

[Leith Ross]

Hello Mike,

This macro will save the active worksheet as a tab delimited file. When it runs, you will prompted for the Directory Path and Filename in which to save the data.
If you are not saving the entire worksheet, I can modify the macro.

Macro to Save Worksheet as Tab Delimited File:

Public Sub SaveAsTabbedText()

Dim StartCol
Dim EndCol
Dim I As Integer
Dim StartRow As Long
Dim EndRow As Long

Dim EndString As String
Dim FileName As String
Dim Filenum As Integer

With ActiveSheet.UsedRange
StartRow = .Row
StartCol = .Column
EndRow = .Rows.Count + StartRow - 1
EndCol = .Columns.Count + StartCol - 1
End With

Filenum = FreeFile
FileName = InputBox("Please Enter the Directory Path and File Name Below.")
If FileName = "" Then Exit Sub
I = InStr(1, Right(FileName, 4), ".")
If I = 0 Then FileName = FileName & ".txt"


Open FileName For Output As #Filenum
For R = StartRow To EndRow
For C = StartCol to EndCol
If C = EndCol Then
Data = Data & ActiveSheet.Cells(R, C).Value
Else
Data = Data & ActiveSheet.Cells(R, C).Value & vbTab
End If
Next C
Print #Filenum, Data
Data = ""
Next R
Close #Filenum

End Sub

Let me know how this works for you. If you want , you can e-mail me at [email protected].

Sincerely,
Leith Ross