Excel VBA - Write from worksheet to text file


I woud be very grateful if someone could help me with a task that I have not done before, and hence not aware of a good approach I should adopt.

I have an Excel workbook that is populated with a list of files from a folder that I would like to output to a text file.  I attach a sample workbook:

1.  There are 3 columns and n number of rows.

2.  The first row contains the text "Filename" in the 1st column, a date in the 2nd column and a date in the 3rd column.  This row needs to appear in the first line of the text file with a tab separating the 3 data values.

3.   The data rows beginning at row 2 in the worksheet should be output to separate lines in the text file.

4.   A blank row should appear in the text file after the last data row.

5.   The text file should be saved to the same folder in which the worbook has been saved and should be called "DataOutput.txt".

If it helps I also attach a "DataOutput.txt" that I have prepared manually.

Many thanks in advance
Who is Participating?
als315Connect With a Mentor Commented:
Look at sample
Martin LissConnect With a Mentor Older than dirtCommented:
Here's a macro
Sub CreateFile()
Dim lngLastRow As Long
Dim lngIndex As Long
Dim strLine As String

lngLastRow = Range("A65536").End(xlUp).Row
Open Application.Path & "\DataOutput.txt" For Output As 1
Print #1, Cells(1, 1).Value & vbTab & Cells(1, 2).Value & vbTab & Cells(1, 3).Value
For lngIndex = 2 To lngLastRow
    Print #1, Cells(lngIndex, 1).Value

End Sub

Open in new window

alisonthomAuthor Commented:
Thank you both for providing excellent solutions!

It is most appeciated
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.