Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write text data to text file without quotes
The standard write command encloses text data within quotes.
I need to create text file file that looks lie this: [category] x = Hi But, for example, that following commands: Open text.txt for output as #1 x = "Hi" Write #1, x Produces a text file with line: "Hi" How do I write a macro that produces a text file with text data that is not enclosed by quote marks? -- Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write text data to text file without quotes
When you write CSV data after opening a file using file number #1 excel adds
the extra quotes. You need to open the file using a scripting language open and writes. Here is an example file Sub WriteCSV() Const Delimiter = "," Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fswrite = CreateObject("Scripting.FileSystemObject") WriteFileName = "text.csv" 'open files WritePathName = MyPath + WriteFileName 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 If Range("D" & RowCount) Date Then For ColCount = 1 To 12 If ColCount = 1 Then OutPutLine = Cells(RowCount, ColCount) Else OutPutLine = OutPutLine & Delimiter & Cells(RowCount, ColCount) End If Next ColCount tswrite.writeline OutPutLine End If Next RowCount tswrite.Close End Sub "Richard" wrote: The standard write command encloses text data within quotes. I need to create text file file that looks lie this: [category] x = Hi But, for example, that following commands: Open text.txt for output as #1 x = "Hi" Write #1, x Produces a text file with line: "Hi" How do I write a macro that produces a text file with text data that is not enclosed by quote marks? -- Richard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write text data to text file without quotes
Hmm, I want to look at scripting.
But merely using PRINT #1, .. should give him what he wants. Dave D-C Joel wrote: When you write CSV data after opening a file using file number #1 excel adds the extra quotes. You need to open the file using a scripting language open and writes. Here is an example file Sub WriteCSV() Const Delimiter = "," Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fswrite = CreateObject("Scripting.FileSystemObject") WriteFileName = "text.csv" 'open files WritePathName = MyPath + WriteFileName 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 If Range("D" & RowCount) Date Then For ColCount = 1 To 12 If ColCount = 1 Then OutPutLine = Cells(RowCount, ColCount) Else OutPutLine = OutPutLine & Delimiter & Cells(RowCount, ColCount) End If Next ColCount tswrite.writeline OutPutLine End If Next RowCount tswrite.Close End Sub "Richard" wrote: The standard write command encloses text data within quotes. I need to create text file file that looks lie this: [category] x = Hi But, for example, that following commands: Open text.txt for output as #1 x = "Hi" Write #1, x Produces a text file with line: "Hi" How do I write a macro that produces a text file with text data that is not enclosed by quote marks? -- Richard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write text data to text file without quotes
I've never created a text file before. Your code looks like it may be my
answer. Not sure. What I want to accomplish is to copy (A:E) or (A:F) and use xls.end up to stop at last row. This is to limit the size of text file to only data entered. I will then use the text file for a mail merge. Trying to merge off excel sheet is to slow. Any I will try to comprehend what you have here. Any assistance greatly appreciated. Thanks in Advance. What is CSV? "Joel" wrote: When you write CSV data after opening a file using file number #1 excel adds the extra quotes. You need to open the file using a scripting language open and writes. Here is an example file Sub WriteCSV() Const Delimiter = "," Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fswrite = CreateObject("Scripting.FileSystemObject") WriteFileName = "text.csv" 'open files WritePathName = MyPath + WriteFileName 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 If Range("D" & RowCount) Date Then For ColCount = 1 To 12 If ColCount = 1 Then OutPutLine = Cells(RowCount, ColCount) Else OutPutLine = OutPutLine & Delimiter & Cells(RowCount, ColCount) End If Next ColCount tswrite.writeline OutPutLine End If Next RowCount tswrite.Close End Sub "Richard" wrote: The standard write command encloses text data within quotes. I need to create text file file that looks lie this: [category] x = Hi But, for example, that following commands: Open text.txt for output as #1 x = "Hi" Write #1, x Produces a text file with line: "Hi" How do I write a macro that produces a text file with text data that is not enclosed by quote marks? -- Richard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write text data to text file without quotes
For whatever reason, my newsreader is not showing me any of the thread you
are responding to, so all I see is what is quoted within your response. In that quoted section, Richard uses Open/Output to open a channel and then Write to put text into the file for that channel... and then asks if there is a way to put that text in the file without the quote marks. Rather than the FileSystemObject method Joel proposed (also shown in your quoted text), I would have suggested to the OP to continue using Open/Output, but to use Print #1 rather than Write #1 to eliminate the quote marks around text strings when placing his text into the file. Rick "Curt" wrote in message ... I've never created a text file before. Your code looks like it may be my answer. Not sure. What I want to accomplish is to copy (A:E) or (A:F) and use xls.end up to stop at last row. This is to limit the size of text file to only data entered. I will then use the text file for a mail merge. Trying to merge off excel sheet is to slow. Any I will try to comprehend what you have here. Any assistance greatly appreciated. Thanks in Advance. What is CSV? "Joel" wrote: When you write CSV data after opening a file using file number #1 excel adds the extra quotes. You need to open the file using a scripting language open and writes. Here is an example file Sub WriteCSV() Const Delimiter = "," Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 Set fswrite = CreateObject("Scripting.FileSystemObject") WriteFileName = "text.csv" 'open files WritePathName = MyPath + WriteFileName 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 If Range("D" & RowCount) Date Then For ColCount = 1 To 12 If ColCount = 1 Then OutPutLine = Cells(RowCount, ColCount) Else OutPutLine = OutPutLine & Delimiter & Cells(RowCount, ColCount) End If Next ColCount tswrite.writeline OutPutLine End If Next RowCount tswrite.Close End Sub "Richard" wrote: The standard write command encloses text data within quotes. I need to create text file file that looks lie this: [category] x = Hi But, for example, that following commands: Open text.txt for output as #1 x = "Hi" Write #1, x Produces a text file with line: "Hi" How do I write a macro that produces a text file with text data that is not enclosed by quote marks? -- Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why Saving a file to a CSV, Excel does not put text into Quotes? | Excel Discussion (Misc queries) | |||
How to get Excel CSV format in to write text cells in quotes? | Excel Discussion (Misc queries) | |||
saving an excel file as text without text in quotes | Excel Discussion (Misc queries) | |||
Open CSV file, format data and write output to a text file. | Excel Programming | |||
why are there quotes in my exported text file? | Excel Discussion (Misc queries) |