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.

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

--
Regards,
Tom Ogilvy


"Chris Smith" wrote:

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.

Worked much better with the array!

Thank you.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"Chris Smith" wrote:

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.