Solved: How to delete all " characters of a Txt file?

[Erdin? E. Ka]

Hi everyone,

I want to learn that how to delete all " characters of a txt file via VBA code?

(I want to delete all Chr(34) from the txt file in located "C:\")

The file contains as below:
"<ACB>","<DEF>","<TARIH>","<ZAMAN>","<EDT>","<CTX>","<LKD>","<CVS>"
"ANKARA","60","20000714","123000","0.032","0.029","0.030","24827"

I wish do like this:
<ACB>,<DEF>,<TARIH>,<ZAMAN>,<EDT>,<CTX>,<LKD>,<CVS>
ANKARA,60,20000714,123000,0.032,0.029,0.030,24827

Thanks in advance.

[mdmackillop]

Try
[VBA]
Sub DelQuotes()
Dim fs, a, strLine As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.OpenTextFile("C:\AAA\Quotes.txt", 1, 0)
strLine = a.readall
strLine = Application.WorksheetFunction.Substitute(strLine, Chr(34), "")
a.Close
Set a = fs.OpenTextFile("C:\AAA\Quotes.txt", 2)
a.writeline strLine
a.Close
End Sub

[/VBA]

[Erdin? E. Ka]

Hi mdmackillop,

It's ok. Perfect!!!

Thank you very much

[Ken Puls]

Doh! Beat me to it, Malcolm.

I'm posting what I have anyway, although it outputs it to a new file:

[vba]Sub TextStreamTest()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fso As Object
Dim fsoFileToWrite As Object, textStreamToWrite As Object
Dim fsoFileToRead As Object, textStreamToRead As Object
Dim strText As String

'Link to the file to read and open text stream
Set fso = CreateObject("Scripting.FileSystemObject")
Set fsoFileToRead = fso.getfile("C:\Documents and Settings\kenp\Desktop\TextTest.txt")
Set textStreamToRead = fsoFileToRead.OpenAsTextStream(ForReading, -2)

'Creaete new file to write to
fso.createtextfile ("C:\Documents and Settings\kenp\Desktop\TextTestOutput.txt")
Set fsoFileToWrite = fso.getfile("C:\Documents and Settings\kenp\Desktop\TextTestOutput.txt")
Set textStreamToWrite = fsoFileToWrite.OpenAsTextStream(ForAppending, -2)

'Loop through the file, replacing "" with blanks
Do Until textStreamToRead.atendofstream
strText = textStreamToRead.ReadLine
strText = Application.WorksheetFunction.Substitute(strText, Chr(34), "") & vbNewLine
textStreamToWrite.Write strText
Loop

'Close text stream and release objects
textStreamToRead.Close
textStreamToWrite.Close
Set textStreamToRead = Nothing
Set textStreamToWrite = Nothing
Set fsoFileToRead = Nothing
Set fsoFileToWrite = Nothing
Set fso = Nothing
End Sub[/vba]
Malcolm, yours is much shorter though.

[mdmackillop]

Hi Ken,
I didn't realise that the original text would be overwritten until testing. I thought it would append. Right result, so I kept it!

[Erdin? E. Ka]

Hi Ken,
As you said, Malcolm's code is shorter.
But thank you too.
I saved yours too.

[Ken Puls]

Hi Ken,
I didn't realise that the original text would be overwritten until testing. I thought it would append. Right result, so I kept it!

Sounds reasonable to me! It's a pity that things don't work out like that more often, isn't it? LOL!

[mdmackillop]

Always something new to learn, however one discovers it.