Copy the text data from the column of an excel sheet into a text file .

[Sharbat]

Hello


I am using the following procedure, in order to copy the text from one
of the column of an Excel sheet into a text file.

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

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

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

This is the nature of this text data.

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

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

Suggestions will be appreciated.


Thanks

Sharbat





Some part of Text data from the column of an excel sheet:



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



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


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

 

[Dave Peterson]

See one more reply at the original thread.

Hello

I am using the following procedure, in order to copy the text from one
of the column of an Excel sheet into a text file.

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

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

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

This is the nature of this text data.

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

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

Suggestions will be appreciated.

Thanks

Sharbat

Some part of Text data from the column of an excel sheet:

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

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

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

 

[Dave Peterson]

I meant the first time you posted the question.

http://groups.google.com/[email protected]

From your headers, you're posting through exceltip.com. I don't use this, but
if you're having trouble finding your older posts, you can use google to search
(google could take 4-8 hours to scoop up your post (and answers), though).

That's one reason I connect to the MS Newsgroups directly through Microsoft's
newsservers.

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tutorials/oe_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm