Tranfering data from the column to text file .

[Sharbat]

Hi,

How should we tranfer the data from one column of an excel sheet into
a text file?
There are several sheets, but I wanted to transfer the data from one
of the column of a sheet into a text file.
Is the export option available on Excel via which we can do this or
any other option.

Your ideas will be great.

Thanks

Sharbat

 

[Kevin Stecyk]

Sharbat wrote

Hi,

How should we tranfer the data from one column of an excel sheet into
a text file?
There are several sheets, but I wanted to transfer the data from one
of the column of a sheet into a text file.
Is the export option available on Excel via which we can do this or
any other option.

Sharbat,

I would copy that one column of data to a new workbook and then save it
using the SAVE AS command using a text file (txt). You can also use CSV.
There are lots of options to choose from when using SAVE AS.

Hope that helps.

Best regards,
Kevin

 

[Dave Peterson]

I'd copy that column and paste into a notepad file and save from there.

But if you want to automate it, you could look at some sample code:

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

Earl Kiosterud's:
http://www.tushar-mehta.com/
Look for Text Write in the left hand frame.

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

 

[Sharbat]

Thanks,

Your suggestion is really a good one, but I wanted to copy this text
data directly form the column to text file.
Is there another approach via which we can do it directly or
implicitly.

Regards

Sharbat

 

[BSENA]

Hello DAVE AND EVERYONE,

Thanks for your suggestion.
As you inform me about the web site, I am using the followin
procedure from this web site, in order to copy the text from a colum
of an Excel sheet into a text file.

But I like to say that this procedure can’t copy the entire text into
text file.
Currently it copies the text before the following line that is th
special character (# ) which is the part of TEXT Data:

###################################################

The text data of the column of an excel sheet contains specia
character like #.

This is the nature of this text data.

I think the above special character line halt the further process. Ho
should we solve this issue?

Below u find some of the text data which is available in the column o
an excel sheet.

Your suggestion will be appreciated.


Thanks

Sharbat



Some part of Text from the column:



PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=961103
PARSING IN CURSOR #3 len=809 dep=0 uid=5 oct=47 lid=5 tim=96110
hv=2641313648 ad='3be0abc'
###################################################
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=961103
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=961103


VBA CODE Which I got from the NET:


Exporting To Text Files



Public Sub ExportToTextFile(FName As String, _
Sep As String,
SelectionOnly As Boolean)
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


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


You can call this macro from another VBA procedure as follows:
ExportToTextFile "c:\temp\test.txt", ";" , FALS

 

[Dave Peterson]

I copied your test data (6 rows?) into a worksheet.

I selected the range to extract.

I ran this macro:

Sub testme01()
Call ExportToTextFile("c:\temp\test.txt", ";", True)
End Sub

Notice the "True" at the end. This means that only the selection will be saved
to the text file.

It ran ok for me.

I looked in c:\temp\text.txt and saw this:

PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=961103
PARSING IN CURSOR #3 len=809 dep=0 uid=5 oct=47 lid=5 tim=961103
hv=2641313648 ad='3be0abc'
###################################################
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=961103
PARSE #4:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=961103


If that "#######" line was really just text, then it worked ok.

But excel can show #### in a cell for a few reasons--negative dates/times or
cell text being too long (with the cell format of Text).

I put a negative number in that ###### cell and formatted it as Date. The code
blew up real good.

But if I changed this line:

From:
CellValue = _
Application.WorksheetFunction.Text _
(Cells(RowNdx, ColNdx).Value, _
Cells(RowNdx, ColNdx).NumberFormat)

to:
CellValue = Cells(RowNdx, ColNdx).Text


(it's a single logical line on multiple physical lines with continuation
characters--so be a little careful.)

And make sure you select you range (not the whole column!) and run that
subroutine:

Sub testme01()
Call ExportToTextFile("c:\temp\test.txt", ";", True)
End Sub

With True set, then Chip Pearson's code worked ok for me.

 

[Sharbat]

Hi Dave,

Thanks for giving me the solution.
I will check it.


Best Regards

Sharbat