Solved

Excel VBA - Write from worksheet to text file

Posted on 2013-01-12
4
468 Views
Last Modified: 2013-01-12
Hi,

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
Alison
OutputToFile-test.xlsm
DataOutput.txt
0
Comment
Question by:alisonthom
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
als315 earned 177 total points
ID: 38770338
Look at sample
OutputToFile-test.xlsm
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 178 total points
ID: 38770352
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
Next
Close

End Sub

Open in new window

0
 

Author Closing Comment

by:alisonthom
ID: 38770552
Thank you both for providing excellent solutions!

It is most appeciated
Alison
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 38770573
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now