ExcelBanter - Export each column in a different txt file

Many thanks Chip, your macro working

"Chip Pearson" wrote:

Catherine,

Try code like the following. Change the lines marked with <<< to the
appropriate values. WS is the worksheet name. FirstCol is the column
number of the first column of data to be exported. LastCol is the
column number of the last column of data to be exported. You don't
specify how you want the text files to be named. The code creates a
file name called "File_N.txt" where N is the column number. The files
are created in the same directory as the workbook resides.

Sub AAA()
Dim R As Range
Dim FirstCol As Long
Dim LastCol As Long
Dim FirstRow As Long
Dim FName As String
Dim FNum As Integer
Dim WS As Worksheet

FirstCol = 1 '<<< CHANGE
LastCol = 3 '<<< CHANGE
FirstRow = 1 '<<< CHANGE
Set WS = Worksheets("Sheet1") '<<< CHANGE
Set R = WS.Cells(FirstRow, FirstCol)
Do Until R.Column LastCol
FNum = FreeFile
FName = ThisWorkbook.Path & "\" & _
"File_" & CStr(R.Column) & ".txt" '<<<< Filename??
Open FName For Output Access Write As #FNum
Do Until R.Text = vbNullString
Print #FNum, R.Text
Set R = R(2, 1)
Loop
Close #FNum
Set R = WS.Cells(FirstRow, R.Column + 1)
Loop

End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Tue, 17 Feb 2009 13:24:05 -0800, Catherine
wrote:

Hi,

I need to create a separate txt file for each column in my worksheet.

Do you know how to do it?

Thanks

Catherine