Too many Line Continuations

[sidney102]

Hi,

I would lik to create a macro to create a Text Delimeted file to Excel converting from General (text delimted General to Text) the problem I am encountering (ERROR) "Too many line continuations", your help is extremely apreciated. I have over 150 columns (YES OVER 150 COLUMNS) I am using Excel 2007. please send solution.

Sub Text_to_excel()
'
' Text_to_excel Macro
'

Sub Macro4()
'
' Macro4 Macro
'

'
    ChDir "C:\Documents and Settings\EA3227\Desktop"
    Range("GG12").Select
    Windows("22917674text.txt").Activate
    ActiveWindow.Close
    Range("GG8").Select
    Workbooks.OpenText Filename := _
        "C:\Documents and Settings\EA3227\Desktop\22917674text.txt", Origin := 437, _
        StartRow := 1, DataType := xlDelimited, TextQualifier := xlDoubleQuote, _
        ConsecutiveDelimiter := FALSE, Tab := TRUE, Semicolon := FALSE, Comma := FALSE _
        , 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), _
End Sub

Problem too many line continuations!

[si_the_geek]

Make each line "wider", for example instead of this:

...
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), _
End Sub

...use this:

...
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), _
End Sub

It isn't quite as easy to read (as you'll be more likely to need to scroll), but it will stop the current problem which is far larger.


In addition to that, you can use a variable to store the entire Array(Array(...)) section, and just put the variable after FieldInfo:=

[Siddharth Rout]

Array(119,1),Array(120,1),Array(121,1),Array(122,1),Array(123,1),Array(124,1), _
End Sub

Welcome to the forums sidney102

Further to what Si mentioned above, remove the last UNDERSCORE before the End Sub in your code...

[Ecniv]

Hi

I know this isn't much help but wouldn't you just write your own file import routine and drop the results into a spreadsheet?

I mean, using the following functions:
open, close, line input #...

If the file is csv (character separated) then you could try using split function to put into an array, then use a for...next loop to put into a row of cells. Then increment the row pointer variable...

Depends if you get your code to work or not though. Hopefully it works

[sidney102]

Hi

I know this isn't much help but wouldn't you just write your own file import routine and drop the results into a spreadsheet?

I mean, using the following functions:
open, close, line input #...

If the file is csv (character separated) then you could try using split function to put into an array, then use a for...next loop to put into a row of cells. Then increment the row pointer variable...

Depends if you get your code to work or not though. Hopefully it works

Please show an example,I appreciate the help and great ideas.

[Ecniv]

Ok... off the top of my head and may not compile..

dim strFN as string
dim lngRowOp as long
dim lngCol as long
dim strLineOfData as string
dim varAry as variant 'try not to use variants... but works with split
dim sht as worksheet

set sht = activesheet
strFN = "C:\thefile.txt"
close #1
open strfn for input as #1
line input #1, strLineofdata 'this ignores the first line as its usually headers
lngrowop=5
do until eof(1)
    lngrowop=lngrowop+1
    line input #1, strlineofdata
    varAry = split(strlineofdata,"|") ' put the separator character in here - note , * may be used elsewhere, so use a | pipe or ¬ or a very unusal character to separate data
'---- also note if your data has speechmarks then you'll need to get creative as the split doesn't recognise speechmarks...
    for lngcol = 0 to ubound(varary) 'arrays start at 0... cells at 1
        sht.cells(lngrowop,lngcol+1)=varary(lngcol)
    next
loop
close #1
set sht=nothing 'clear off and free resource

It may not work but should give you an idea what I meant...



>>> May is probably here - probably wont work