HELP! Writing Text file from Excel Spreadsheet.

[JEB]

Pardon my sence of urgency here, but I'm stuck and need help.
I have a worksheet that contains only one column of string data that I need
to write to an MS-DOS formated Text file on my PC (for later use). What is
the simplest way to do it. (The data resides in colmun "A" and contains
anywhere from 50 to 160 rows).
Thanking you in advance.
JEB

 

[Rick Rothstein \(MVP - VB\)]

You can use this macro to write out Column A's content...

Sub SaveColumnA()
Dim X As Long
Dim FF As Long
Dim ColumnText As String
For X = 1 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ColumnText = ColumnText & Range("A" & CStr(X)).Value & vbCrLf
Next
FF = FreeFile
Open "c:\temp\test.txt" For Output As #FF
Print #FF, ColumnText
Close #FF
End Sub

Rick

 

[JEB]

Rick;
Thank you very much. It worked. Is there, however, anyway to eliminte the
LFCR as the first character of the record? My data string is 1500 bytes and
I need to output that to the text file starting in position 1. It seems that
I have a 1 byte displacement.

Also, do you know of anyway that in creating a string, to maintain a given
length of a field which is being concatenated to the string? For example, if
I have a 25 byte 'name' field that only contains 18 characters, I want to
move the whole 25 bytes rather than the first 18.

Thank you again for your help.
JEB

 

[Rick Rothstein \(MVP - VB\)]

You can use this code to produce a file that won't start with a CrLf
combination...

Sub SaveColumnA()
Dim X As Long
Dim FF As Long
Dim ColumnText As String
For X = 1 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ColumnText = ColumnText & Range("A" & CStr(X)).Value & vbCrLf
Next
Do While Left$(ColumnText, 2) = vbCrLf
ColumnText = Mid$(ColumnText, 3)
Loop
FF = FreeFile
Open "c:\temp\test.txt" For Output As #FF
Print #FF, ColumnText
Close #FF
End Sub

As for your other question, it was not clear to me if you meant that for use
in the above code or for some other code you have. If you meant for use in
the above, change the statement in the For-Next block to this...

ColumnText = ColumnText & Format$(Range("A" & CStr(X)).Value, _
"!" & String(25, "@")) & vbCrLf

If you meant for use in other code, then assuming your text is in a variable
named MyText, you would use this....

Format$(MyText, "!" & String(25, "@"))

which is identical to this....

Format$(MyText, "!@@@@@@@@@@@@@@@@@@@@@@@@@")

that is, a pattern string composed of an exclamation mark followed by as
many @ symbols as you want characters in your output field (each character
in MyText fills in one of the @ symbols, any unfilled @ symbols become
spaces... the exclamation mark says to start filling in the@ symbols from
the left side).

Rick

 

[JEB]

Again, my sincere thanks.

You can use this code to produce a file that won't start with a CrLf
combination...

Sub SaveColumnA()
Dim X As Long
Dim FF As Long
Dim ColumnText As String
For X = 1 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ColumnText = ColumnText & Range("A" & CStr(X)).Value & vbCrLf
Next
Do While Left$(ColumnText, 2) = vbCrLf
ColumnText = Mid$(ColumnText, 3)
Loop
FF = FreeFile
Open "c:\temp\test.txt" For Output As #FF
Print #FF, ColumnText
Close #FF
End Sub

As for your other question, it was not clear to me if you meant that for use
in the above code or for some other code you have. If you meant for use in
the above, change the statement in the For-Next block to this...

ColumnText = ColumnText & Format$(Range("A" & CStr(X)).Value, _
"!" & String(25, "@")) & vbCrLf

If you meant for use in other code, then assuming your text is in a variable
named MyText, you would use this....

Format$(MyText, "!" & String(25, "@"))

which is identical to this....

Format$(MyText, "!@@@@@@@@@@@@@@@@@@@@@@@@@")

that is, a pattern string composed of an exclamation mark followed by as
many @ symbols as you want characters in your output field (each character
in MyText fills in one of the @ symbols, any unfilled @ symbols become
spaces... the exclamation mark says to start filling in the@ symbols from
the left side).

Rick

 

[Curt]

not smart enough. Have been trying to get your code to write more than 1
column. Also have been having trouble accessing this web site. Need to write
A!:F50 to text file so can speed up mail merge. Have been told this will
eliminate word searching full sheet. Had on responce to this and all I get
when trying to acess is blank page. I got here by going into word then
getting here. any Help greatly appreciated.
Thanks
(e-mail address removed)

 

[Rick Rothstein \(MVP - VB\)]

How do you want the text file to look? By that I mean, do you want every
cell on its own line in the file? Or do you want to keep the entire row
together on a single line in the file? If the latter, what delimiter do you
want between each cell's value? The more detail you can give as to what the
file should look like when the macro is finished, the better able we will be
to give you a solution.

Rick

 

[Curt]

What I want to accomplish is to speed up mail merge. Merge wants to search
entire excel sheet. Have deducted if I can create a text file to do merge
from then area for search will be limited. Think i need text file to apear
same as excel sheet. This is something I have never tried before. The entire
row I think should be on one line. It is 5 columns wide. Will copy & paste
first two rows of sheet in here so you can see what I am dealing with.
Name of Entry Contact Person Address City&State Zip Amount
Willamette Leadership Acedemy SGM Steven Arbuckel 1321 sd2 1 10
Amount belongs on line 1, 10 is under amount
Column headings will be used in merge for identfication. Will be useing xlup
if possible to eliminate blank rows. Have allowed 50 rows on sheet. All will
not be used. FYI This is for Vet's Parade Association. Thank you letters for
being a part.
Didn't know what I was getting into when I said I would do this. When
finished the program will be on the Vet's web site for all to use. Novice as
I am is there a better way ? I built tables in word and still merge takes to
long. This will be run from a userform option button all macro operation
because many my age are not computer savy. The Parade is the one time of year
they can stand proud in Public.
Sorry if I ramble
(e-mail address removed)
Semper Fi

 

[Curt]

Rick I found this on net as it says it will write and output nothing for a
blank. Thats my idea to eliminate all blank lines from excel worksheet.
Problem not knowing.
First Set fswrite variable not defined
put in dim statement
then
write path name varailable not defined
Here is code could you look and see what I am missing. This is a first for me.
Thanks in advance.

'The code below will write a CSV file and will not output anything for a
blank line
Sub WriteCSV()
Dim fswrite As Variable
Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"
Const Delimiter = ","
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Set fswrite = CreateObject("Scripting.FileSystemObject")-------------
'open files
' WritePathName = MyPath + WriteFileName------------
WritePathName = "c:\temp\text.cvs" ----(tried this)
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

lastrow = Cells(Rows.Count, "A").End(xlUp).row

For RowCount = 1 To lastrow
lastcol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To lastcol
If ColCount = 1 Then
OutputLine = Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
OutputLine = Trim(OutputLine)
If Len(OutputLine) <> 0 Then
tswrite.writeline OutputLine
End If
Next RowCount

tswrite.Close

End Sub