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.

 

[Jaye7]

Norie, code is

Sub test()
Dim I As Long
Dim arr1
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
end sub

 

[xenou]

I don't see the problem either but I don't believe you need all those anyway. Try this and see if it works (the first column will be opened to text and the rest default to general format):

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)), TrailingMinusNumbers:=True

 

[Jaye7]

Xenou,

You came through again, your code works great and is much easier to adapt than the one I was using.

Thanks heaps.

 

[Norie]

Eh, didn't I post something that looked like that code?

Only thing missing was the part to import column 1 as text, which I don't actually think would make much difference.

That is unless that column was some sort of number that you want to treat as text, eg an ID number.

 

[xenou]

@Norie, yes, I see your post now ... I've been reading posts too quickly lately. I was thinking the same thing about the first column too. Glad we worked it out - I'm still not sure why the array of arrays failed but we can save that for another day

 

[Norie]

xenou

The array of arrays does work - I tested it with various scenarios

eg skipping columns, randomly setting the data type for the columns etc

The code I posted where the array was created didn't have any declarations, and the OP seemed to be saying that was the problem.

Who knows, it's working anyway.