Too many Line Continuations
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.
Code:
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!:)
Re: Too many Line Continuations
Make each line "wider", for example instead of this:
Code:
...
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:
Code:
...
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:=
Re: Too many Line Continuations
Quote:
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 :wave:
Further to what Si mentioned above, remove the last UNDERSCORE before the End Sub in your code...
Re: Too many Line Continuations
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 :)
Re: Too many Line Continuations
Quote:
Originally Posted by
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 :)
Please show an example,I appreciate the help and great ideas.
Re: Too many Line Continuations
Ok... off the top of my head and may not compile..
Code:
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
