Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 469
Default Need help combining

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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default Need help combining

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.


"Curt" wrote:

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


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Need help combining

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


"Curt" wrote in message
...
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



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default Need help combining

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)" wrote:

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


"Curt" wrote in message
...
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




  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Need help combining

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" wrote in message
...
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)" wrote:

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


"Curt" wrote in message
...
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







  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9,101
Default Need help combining

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.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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)" wrote:

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


"Curt" wrote in message
...
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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combining second row to first row LisaJ27 Excel Discussion (Misc queries) 2 February 26th 09 04:48 PM
Combining Tamsin Charts and Charting in Excel 0 March 5th 07 04:14 PM
Combining look up and last? Sarah Excel Discussion (Misc queries) 4 January 9th 07 11:01 AM
Combining Sum, IF and AND Kenton_SJ Excel Discussion (Misc queries) 1 August 3rd 05 11:30 PM
combining IF and AND Dahlman Excel Discussion (Misc queries) 5 May 12th 05 08:21 PM


All times are GMT +1. The time now is 08:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"