Need help combining

[Curt]

First go at creating and writing to text file. Having read copy paste not way
to go. Found code that will create text file in help. Also searched and found
code that will write all data and not write blank rows or cells. Having a
problem bringing the code together. Also have had method or data member not
found show up on .CreateTextFile & .writeline Output. Once I get this
combined can move on.
Will enclose the code I have in place.
Thanks to All

Sub AAAA()
Dim lastrow As Variable
Dim RowCount As Integer
Dim lastcol As Variable
Dim ColCount As Integer
Dim OutPutLine As Variable
Dim Delimiter As Variable
Dim tswrite As Variable
Dim writeline As String
Dim FSO As Scripting.FileSystemObject
Dim AAA As Scripting.TextStream
Set FSO = New Scripting.FileSystemObject
Set AAA = FSO.CreateTextFile("C:\Parade\ZZZ.txt")
AAA.writeline "This Is Line One"----This part works fine as is
'Worksheets("mailE").Activate

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 ---this is writeline hang up.
CreateTextFile is in another module had to set reference to get first part to
operate may need to set more thanks again---
End If
Next RowCount

End Sub

 

[Joel]

When you create the new file ZZZ.txt you use this line

Set AAA = FSO.CreateTextFile("C:\Parade\ZZZ.txt")

AAA is your write stream and it worked ok in the following line

AAA.writeline "This Is Line One"----This part works fine as is

Later in the ocde you used a new name which is the error

from
tswrite.writeline OutPutLine ---this is writeline hang up.
to
AAA.writeline OutPutLine ---this is writeline hang up.

 

[Rick Rothstein \(MVP - VB\)]

I don't use the FileSystemObject, so I won't comment on that part of your
code; but I do notice you use a Delimiter variable without assigning
anything to it; that will make your concatenated lines one long mess.
Anyway, here is how I would write out the data you show being processed in
your code...

Sub SaveData()
Dim FF As Long
Dim RowCount As Long
Dim ColCount As Long
Dim TotalFile As String
FF = FreeFile
Open "C:\Parade\ZZZ.txt" For Output As #FF
For RowCount = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If RowCount > 1 Then TotalFile = TotalFile & vbCrLf
For ColCount = 1 To Cells(RowCount, Columns.Count).End(xlToLeft).Column
If ColCount > 1 Then TotalFile = TotalFile & ","
TotalFile = TotalFile & Cells(RowCount, ColCount).Value
Next
Next
Print #FF, TotalFile
Close #FF
End Sub

Rick

 

[Joel]

Rick: You code probably works fine on small files, but large files it eats
lots of memory and could slow the computer up significanntly. why didn't you
just write each line one at a time.


Sub SaveData()
Dim FF As Long
Dim RowCount As Long
Dim ColCount As Long
Dim LastRow As Long
Dim LastCol As Long
FF = FreeFile
Open "C:\Parade\ZZZ.txt" For Output As #FF
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
Outline = Cells(RowCount, ColCount).Value
Else
OutputLine = OutputLine & "," & Cells(RowCount, ColCount).Value
End If
Next ColCount

If RowCount = LastRow Then
OutputLine = OutputLine & Cells(RowCount, ColCount).Value
Else
OutputLine = OutputLine & vbCrLf & Cells(RowCount, ColCount).Value
End If
Print #FF, OutputLine
Next RowCount
Close #FF
End Sub

 

[Rick Rothstein \(MVP - VB\)]

That is a valid comment. One of the problems I have volunteering here in the
Excel groups is my major experience base is in compiled VB, not Excel;
hence, I tend to still think "compiled VB" when constructing code. I hardly
ever came up against data over in the compiled VB newsgroups that was large
enough to make the posted code inefficient. I seem to keep forgetting that
Excel data can be quite large by comparison and the posted technique (an
attempt to minimize data writes to the hard disk) might fail to be efficient
with it. You are right in noting that it might be better to simply write the
lines out one at a time. Thanks for picking up on this.

Rick

 

[Joel]

I have a BSEE and a MSCS. As part of my MSCS I took course on effiecientcy
of different algorithms. I work on everything from assembly languages to
various different high langueages. I hate BASIC but realize in excel it does
have advantages over other languages. Even in compiles VB, I would still
write one line at a time.