VBA replace code not working on text document from Excel

Question

I have the below code to replace tabs with commas in a text document:

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("Path:\CSV.txt", ForReading)

strText = objFile.ReadAll
strTab = vbTab

strText = Replace(strText, strTab, ",")

objFile.Close

Set objFile = objFSO.OpenTextFile("Path:\CSV.txt", ForWriting)

objFile.Write strText

objFile.Close

This works fine on a unicode text document that I create myself, however throws up an error when used on a unicode text document created from excel.

Line: 16 Char: 1 Error: Invalid procedure call or argument Code:800A0005

Here is an example of a text file created from excel that gives the error:

CSV.txt

Can anyone see what is different about this text document that would be causing the error?

EDIT: I have just tried writing the result to a new text document instead of overwriting the same one, but this has just given a garbled result in the document:

ÿþa 黐멸ȯ                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 , , , , , 

 , , , , , , , 

 , , , , , , ,

EDIT2: the suggestion of using TristateTrue from 'Siddharth Rout' gets me past the initial 'Invalid procedure call' error, but now i'm stuck with the garbled Chinese character output - Can anyone advise as to how to fix this?

Could you tell us which is Line 16?

Michael Russo
– Michael Russo

2016-08-11 12:35:49 +00:00
Commented Aug 11, 2016 at 12:35 — Michael Russo
– Michael Russo, Commented Aug 11, 2016 at 12:35
Line 16 is objFile.Write strText

Siddharth Rout
– Siddharth Rout

2016-08-11 12:37:23 +00:00
Commented Aug 11, 2016 at 12:37 — Siddharth Rout
– Siddharth Rout, Commented Aug 11, 2016 at 12:37

Siddharth Rout · Accepted Answer · 2016-08-11 12:36:38Z

2

Replace

Set objFile = objFSO.OpenTextFile("Path:\CSV.txt", ForWriting)

with

Kill "Path:\CSV.txt"
Set objFile = objFSO.CreateTextFile("Path:\CSV.txt", True, True)

Now try it

answered Aug 11, 2016 at 12:36

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Siddharth Rout Over a year ago

Did you replace "Path:\CSV.txt" with the relevant path?

Tom Over a year ago

Yes I have replaced the path and I have put it on two lines as in your post.

Tom Over a year ago

Any idea why it doesn't for me? I have just tried writing the result to a new text file instead of overwriting the same one, but this has just given a garbled result. (see edit to original post)

Siddharth Rout Over a year ago

Try with Set objFile = objFSO.OpenTextFile("Path:\CSV.txt", ForReading) with TristateTrue as shown Here

Tom Over a year ago

This gets me past the initial 'Invalid procedure call' error, but now i'm stuck with the garbled Chinese character output...

Collectives™ on Stack Overflow

VBA replace code not working on text document from Excel

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related