Exporting to Notepad
 

I am trying to get excel to export a column to a .txt file is there any way that I can do this so that later on I can refrence this file later on. I know I can copy and paste the colmun but I was hoping that there was a way that I could export it..

Thank

Adam Kozul

Exporting to Notepad
 

I got the following code from this NG. This code assumes the data begins at
A1. I don't recall who to credit, but here's the code:

Public Sub ExportToTextFile()
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim FName As String
Dim Sep As String
Dim SelectionOnly As Boolean
FName = "testing.txt"
Sep = ","
SelectionOnly = True

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
Else
With ActiveSheet.UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
End If

Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub


--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winne the Pooh

"Adam Kozul" wrote in message
...
I am trying to get excel to export a column to a .txt file is there any
way that I can do this so that later on I can refrence this file later on.
I know I can copy and paste the colmun but I was hoping that there was a way
that I could export it...

Thanks

Adam Kozul

Exporting to Notepad
 

Here is where you got most of it:

http://www.cpearson.com/excel/imptext.htm import/export text files
Chip Pearson's site.

--
Regards,
Tom Ogilvy

"Michael Malinsky" wrote in message
...
I got the following code from this NG. This code assumes the data begins
at
A1. I don't recall who to credit, but here's the code:

Public Sub ExportToTextFile()
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim FName As String
Dim Sep As String
Dim SelectionOnly As Boolean
FName = "testing.txt"
Sep = ","
SelectionOnly = True

Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
Else
With ActiveSheet.UsedRange
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
End If

Open FName For Output Access Write As #FNum

For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub


--
Michael J. Malinsky
Pittsburgh, PA

"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winne the Pooh

"Adam Kozul" wrote in message
...
I am trying to get excel to export a column to a .txt file is there any
way that I can do this so that later on I can refrence this file later on.
I know I can copy and paste the colmun but I was hoping that there was a
way
that I could export it...

Thanks

Adam Kozul

Exporting to Notepad
 

Adam

Here's a little easier macro to follow - not as powerful, but for your
specific request, it gets the job done.

Sub Col1ToText()

'copy sheet1 into a new workbook
ThisWorkbook.Sheets(1).Copy

'The new workbook will be active
With ActiveWorkbook

'Delete all the columns but one
.Sheets(1).Columns("B:IV").Delete

'Save the new workbook as text
.SaveAs "C:\Dick\MyCol1.txt", xlTextWindows

'Close the new workbook with no prompt
.Close False
End With

End Sub
--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"Adam Kozul" wrote in message
...
I am trying to get excel to export a column to a .txt file is there any
way that I can do this so that later on I can refrence this file later on.
I know I can copy and paste the colmun but I was hoping that there was a way
that I could export it...

Thanks

Adam Kozul