OpenText runtime error '1004'

[Chris Smith]

I am trying to write a script to open and format a series of text files.

I have recorded a macro, then edited it to make it more generally useful.
The recorded macro looks like:

Workbooks.OpenText Filename:= _
"C:\Documents and Settings\CSMITH8\Desktop\Project
Simplify\WithHeaders\DETALL.TXT" _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 5), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1),
Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18,
1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1),
Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31,
1), Array(32, 1), Array( _
33, 1)), TrailingMinusNumbers:=True


The code I am trying to run is:

Workbooks.OpenText _
Filename:=strPCFileName, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
FieldInfo:=strDetCols, _
TrailingMinusNumbers:=True

This code fails with "runtime error '1004' Method "OpenText of object
'Workbooks' failed."

I have checked the two variables (strDetCols and strPCFileName), and there
are two differences:

1) strPCFilename does not have double quotes at the beginning and end
2) strDetCols uses the excel constants instead of the numbers (ie. Array(02,
xlGeneralFormat) instead of Array(02, 1)).

I have tried changing the constants to numbers and re-running. I get the
same problem.

I have no idea how to get double quotes into the string, or if that is even
the problem.

Any help that can be given would be greatly appreciated.

Thank you.

 

[Tom Ogilvy]

strPCFilename does not have double quotes at the beginning and end
and it should not. If i did

msgbox strPCFilename
I should see something like C:\Myfolder\Myfile.txt

if strdetcols is a string variable, then there's your huckleberry. It has
to be an array of arrays or a two dimensional array.

see this article
http://support.microsoft.com/kb/134826
"Out of Memory" Message Using the OpenText Method

 

[Chris Smith]

Worked much better with the array!

Thank you.

and it should not. If i did

msgbox strPCFilename
I should see something like C:\Myfolder\Myfile.txt

if strdetcols is a string variable, then there's your huckleberry. It has
to be an array of arrays or a two dimensional array.

see this article
http://support.microsoft.com/kb/134826
"Out of Memory" Message Using the OpenText Method