New VB user needs help with Line Continuation

[carolynlush]

Hello - I have searched all over the internet and can't find the answer to this question. Can you help?

I am creating a macro that uses the text import wizard. All is fine until I get to the section where I need to mark 2 columns as text and leave the rest as general. The text file has quite a few columns in it.

Can someone explain why this is creating an issue? And how I solve it? I saw another posting that suggested removing the dash marks. I tried that and now get a syntax error....HELP!

This is my macro. I really appreciate any help. Thanks!

Workbooks.OpenText Filename := _
"C:\Documents and Settings\LUSHC\My Documents\CSR 04-30-13\file2.txt", Origin _
:= 437, StartRow := 1, DataType := xlDelimited, TextQualifier := xlDoubleQuote _
, ConsecutiveDelimiter := FALSE, Tab := FALSE, Semicolon := TRUE, Comma := _
FALSE, Space := FALSE, Other := FALSE, 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,2),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,2),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

 

[vds1]

Welcome to Mrexcel.com

Which two columns are you looking forward to format as text?

Do you find any errors while running the above code? If yes, what is the exact error message?

Please post the entire code.

 

[shg]

If you really need all of that field info (versus just using the semicolon delimiter), you can put it in a two-column range:

Workbooks.OpenText _
        Filename:="C:\Documents and Settings\LUSHC\My Documents\CSR 04-30-13\file2.txt", _
        Origin:=437, _
        StartRow:=1, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=True, _
        Comma:=False, _
        Space:=False, _
        Other:=False, _
        [COLOR=#FF0000]FieldInfo:=Worksheets("Bob").Range("A1:B124").Value[/COLOR]

 

[Rick Rothstein]

Hello - I have searched all over the internet and can't find the answer to this question. Can you help?

I am creating a macro that uses the text import wizard. All is fine until I get to the section where I need to mark 2 columns as text and leave the rest as general. The text file has quite a few columns in it.

Can someone explain why this is creating an issue? And how I solve it? I saw another posting that suggested removing the dash marks. I tried that and now get a syntax error....HELP!

This is my macro. I really appreciate any help. Thanks!

Workbooks.OpenText Filename := _
"C:\Documents and Settings\LUSHC\My Documents\CSR 04-30-13\file2.txt", Origin _
:= 437, StartRow := 1, DataType := xlDelimited, TextQualifier := xlDoubleQuote _
, ConsecutiveDelimiter := FALSE, Tab := FALSE, Semicolon := TRUE, Comma := _
FALSE, Space := FALSE, Other := FALSE, 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,2),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,2),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

Your long continued line ends incorrectly... you cannot have ", _" as the last 3 characters of a code line. More than likely those characters can be replaced by a closing parenthesis for the first Array function that I highlighted in red.

 

[carolynlush]

Rick - I added the missing ) parenthesis and am now getting this error message:

Compile error - Syntax error.

SHG - I need to use the semicolon delimiter since this is downloaded from another system that way.

vds1 - I need the 17th column and the 49th column in text. Rest can remain in general format.

 

[shg]

If you put the FieldInfo on a sheet, A1:A124 is just =row(), B1:B124 is all 1s, and then put a 2 in row B17 and B49.

 

[vds1]

My Try,

Sub Test_Macro()


    Workbooks.OpenText Filename:="C:\Documents and Settings\LUSHC\My Documents\CSR 04-30-13\file2.txt", Origin:= _
        437, StartRow:=1, DataType:=xlDelimited, Tab:=True, Semicolon:=True
    Cells.NumberFormat = "General"
    Range("S:S").NumberFormat = "@"
    Range("AY:AY").NumberFormat = "@"
End Sub

 

[Rick Rothstein]

Rick - I added the missing ) parenthesis and am now getting this error message:

Compile error - Syntax error.

On what line??? If you did what I told you correctly, the line of code you posted will not have a syntax error any more.... it may not run, but that would be for a reason other than VB syntax (can't test for this without a file to execute it against).

Just so I know (for a possible alternate method of loading the file), your file looks something like this, right?

Line1Data1;Line1Data2;Line1Data3;...etc...;Line1Data123;Line1Data124
Line2Data1;Line2Data2;Line2Data3;...etc...;Line2Data123;Line2Data124
and so on line-after-line to the bottom of the file

And you want this in on worksheet, right?

	A	B	C	D	DS	DT	DU
Line1Data1	Line1Data2	Line1Data3	...etc...	Line1Data123	Line1Data124
Line2Data1	Line2Data2	Line2Data3	...etc...	Line2Data123	Line2Data124
and so on

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 91px"><COL style="WIDTH: 91px"><COL style="WIDTH: 91px"><COL style="WIDTH: 91px"><COL style="WIDTH: 91px"><COL style="WIDTH: 91px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

</TBODY>

We may need to tweak the results later (I don't know what Origin:=437 means and you would need to tell us what is in Columns 17 and 49 that they need to be Text, but right now I am just trying to get a start using simpler code.

 

[carolynlush]

vds1 - Getting close! It imports the file but columns S & AY are not in text format. Any other ideas?

 

[vds1]

Carol,

What data you have in column S and AY ? Could you post some samples ? What format does it show in home tab ?