Write text data to text file without quotes

R

Richard

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?
 
J

Joel

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
 
D

Dave D-C

Hmm, I want to look at scripting.
But merely using
PRINT #1, ..
should give him what he wants. Dave D-C

Joel said:
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 said:
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?
 
C

Curt

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 said:
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 said:
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?
 
R

Rick Rothstein \(MVP - VB\)

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 said:
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 said:
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 said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top