Importing Text File MacroError "Too Many Line Continuat

[gooniegirl180]

I have a colleague who is attempting to create a macro to import a text file into an Excel spreadsheet. The text file is comma delimited, and it is VERY wide: once converted into Excel it used EVERY column (right up to column IV). The number of rows is variable.

The problem is that when my colleague attempts to record the macro to open this file, he gets an error message "Too Many Line Continuations".

This is how the macro looks once the error has appeared:

Workbooks.OpenText Filename := _
"G:\Concur\In\extract_CASH_GL_V2_p0001358hmnx_20070719010046.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,1),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),Array(34,1), _
Array(35,1),Array(36,1),Array(37,1),Array(38,1),Array(39,1),Array(40,1),Array( _
41,1),Array(42,1),Array(43,1),Array(44,1),Array(45,1),Array(46,1),Array(47,1), _
Array(48,1),Array(49,1),Array(50,1),Array(51,1),Array(52,1),Array(53,1),Array( _
54,1),Array(55,1),Array(56,1),Array(57,1),Array(58,1),Array(59,1),Array(60,1), _
Array(61,1),Array(62,1),Array(63,1),Array(64,1),Array(65,1),Array(66,1),Array( _
67,1),Array(68,1),Array(69,1),Array(70,1),Array(71,1),Array(72,1),Array(73,1), _
Array(74,1),Array(75,1),Array(76,1),Array(77,1),Array(78,1),Array(79,1),Array( _
80,1),Array(81,1),Array(82,1),Array(83,1),Array(84,1),Array(85,1),Array(86,1), _
Array(87,1),Array(88,1),Array(89,1),Array(90,1),Array(91,1),Array(92,1),Array( _
93,1),Array(94,1),Array(95,1),Array(96,1),Array(97,1),Array(98,1),Array(99,1), _
Array(100,1),Array(101,1),Array(102,1),Array(103,1),Array(104,1),Array(105,1), _
Array(106,1),Array(107,1),Array(108,1),Array(109,1),Array(110,1),Array(111,1), _
Array(112,1),Array(113,1),Array(114,1),Array(115,1),Array(116,1),Array(117,1), _
Array(118,1),Array(119,1),Array(120,1),Array(121,1),Array(122,1),Array(123,1), _
End Sub


I don't know if this displays in the same way here as it does in the VBA editor but all this is spread over 14 lines.

I'm sure the problem relates to the fact that we are attempting to chop this file into 256 columns.

Does anyone have any suggestions as to how we can overcome this limitation?

Thanks & Regards,
gooniegirl180

 

[Stormseed]

Maybe Excel 2007?

I don't know if someone can write a program !!

 

[Scott Huish]

I think you can probably drop the whole FieldInfo part:

Workbooks.OpenText Filename := _
"G:\Concur\In\extract_CASH_GL_V2_p0001358hmnx_20070719010046.txt", Origin := _
437, StartRow := 1, DataType := xlDelimited, TextQualifier := xlDoubleQuote, _
ConsecutiveDelimiter := FALSE, Tab := FALSE, Semicolon := FALSE, Comma := FALSE _
, Space := FALSE, Other := TRUE, OtherChar := "|"

 

[gooniegirl180]

HotPepper, that seems to work!

An excellent result, especially since it's so easy!

Thanks,
Jane