CODEWITHSUNDEEP
excelvbscript
vicjun
vicjun

Reputation: 19

Delimitate txt file in Excel 2003 using VBScript: errors

As with my previous questions, if I was using VBA this would be quite simple, but I have to use VBScript for this. I know how to import a txt file using OpenText like so:

objExcel.WorkBooks.OpenText "path\xxxx.txt"

This works fine, but the text isn't automatically put into columns in Excel. So I'm assuming I need to specify delimiters.

However, I get errors when trying to add options to the method. For instance

objExcel.WorkBooks.OpenText "path\xxxx.txt", , , xlDelimited, , , , , , True, " "

gives me an error saying "OpenText method of Workbooks class failed", code 800A03EC. What am I doing wrong? I've seen other people use the method like this. There's nothing special about how my txt file is formatted, it's just text.

Thanks for any help.

Upvotes: 0

Views: 1805

Answers (2)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200293

VBScript doesn't recognise VBA named constants. If you want to use xlDelimited in your code, you must define the constant in your script:

Const xlDelimited = 1

otherwise you'll have to use the literal value as chris neilsen pointed out.

Another issue is that the 11th parameter in your method call is of the wrong type. That parameter must be a boolean value indicating if you want to use a different delimiter character. It's parameter #12 where you actually specify that custom delimiter character, so you need to change the line

objExcel.WorkBooks.OpenText "path\xxxx.txt", , , 1, , , , , , True, " "

into

objExcel.WorkBooks.OpenText "path\xxxx.txt", , , 1, , , , , , , True, " "

to make it work.

Of course, if you want to import space-delimited text, you could just set parameter #9 to True without specifying an OtherChar value:

objExcel.WorkBooks.OpenText "path\xxxx.txt", , , 1, , , , , , True

See the documentation for further details.

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53126

VBScript does not have named constants.

Use

objExcel.WorkBooks.OpenText "path\xxxx.txt", , , 1, , , , , , True, " "

Upvotes: 2

Related Questions