Too many line continuations

[Jaye7]

I am trying to record a macro to open a text file in excel however I keep getting the message too many line continuations.

My file that I am trying to open is 45000 rows but the problem is that it is 164 columns.

I have run a smaller file that only has 4 rows but still the 164 columns (as I can't change that) but I still get the message.

Can anyone help with a script to open it, I can't record a script at all therefore I have nothing to start with.

The first column needs to be as text.

 

[Norie]

Why not record when you do it manually for only a few columns?

Then look at the code generated by that and adapt it for your needs.

Try and work out which part of the code relates to the columns and how they are imported - look out for a lot of Arrays if you are importing using the wizard.

 

[Jaye7]

Thanks Norie,

After much googling I found a solution, the macro did actually record a partial code, I had to remove the Array( _ from the end of the lines and add in the rest of the code manually, it ended up as.

Workbooks.OpenText filename:="P:\JInventTestCRC.asc", Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _
Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), 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), Array(124, 1), Array( _
125, 1), Array(126, 1), Array(127, 1), Array(128, 1), Array(129, 1), Array(130, 1), Array(131, 1), Array(132, 1), Array(133, 1), Array(134, 1), Array(135, 1), Array(136, 1), Array(137, 1), Array(138, 1), Array(139, 1), Array(140, 1), Array(141, 1), Array(142, 1), Array(143, 1), Array(144, 1), Array(145, 1), Array(146, 1), Array(147, 1), Array(148, 1), Array(149, 1), Array(150, 1), Array(151, 1), Array(152, 1), Array(153, 1), Array(154, 1), Array(155, 1), Array(156, 1), Array(157, 1), Array(158, 1), Array(159, 1), Array(160, 1), Array(161, 1), Array(162, 1), Array(163, 1), Array(164, 1)), _
TrailingMinusNumbers:=True

:

 

[Norie]

Is the file in CSV format?

If it is you could try this which seemed to work for me.

Workbooks.OpenText filename:="P:\JInventTestCRC.asc", Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True

The only thing is I don't think you have control over the datatype of each column being imported, that's what the array is for.

This code creates the array and imports the file.

ReDim Preserve arr1(I)
 
For I = 0 To 163
 
ReDim Preserve arr1(I)
 
arr1(I) = Array(I + 1, xlColumnDataType.xlGeneralFormat)
 
Next I
 
Workbooks.OpenText Filename:="C:\JInventTestCRC.asc", Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, FieldInfo:=arr1

As it is every column is set to import as General but you should easily be able to change individual elements in the array to change that or to skip columns.

 

[Jaye7]

Thanks Norie,

I tried the code but it kept saying variable not set at code

ReDim Preserve arr1(I)

The file is ASC format, however sometimes I have to remove the .asc from my code as one day it is .asc and the next it has no extension. It is on my work's own windows system.​

 

[Norie]

It should be I in the parentheses and you should add this as the top.

Dim I As Long

As for the file extension, it shouldn't really matter.

A file extension doesn't really dictate what's in the file, it's more of a description.

When testing I used the asc extension and it worked fine.

 

[Jaye7]

Thanks Norie,

I have tried it again with your suggestions but still bugs out on the same line, thanks anyway, the code I provided does the job even though it isn't the best script so I will keep using it.

 

[Norie]

Oops, try adding this too.

Dim arr1

 

[Jaye7]

Norie,

It still doesn't like the code on the same line.

I am using excel 2000 version at work, I might try it at home on excel 2003 version.

Maybe you could help me with another problem that I have posted.

http://www.mrexcel.com/forum/showthread.php?t=496744

 

[Norie]

Weird.

Can you post the exact code you tried using?