Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining second row to first row | Excel Discussion (Misc queries) | |||
Combining | Charts and Charting in Excel | |||
Combining look up and last? | Excel Discussion (Misc queries) | |||
Combining Sum, IF and AND | Excel Discussion (Misc queries) | |||
combining IF and AND | Excel Discussion (Misc queries) |