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

 

[carolynlush]

vds1 - The data has leading zeros that I need to keep. The format does show text but the data looks wacky even when I increase the column width:

Extrnal Ref

1.00012E+12

1.00012E+12

1.00012E+12

1.00013E+12 ?? Any ideas?

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[carolynlush]

Rick - I m getting the error after the first line runs. Yes, I am looking for the data in the format you have outlined.

 

[Rick Rothstein]

Rick - Yes, I am looking for the data in the format you have outlined.

I would like you to try this alternative code on a copy of your workbook... this is not final code... I just want to make sure it works for you in a general way. If it does work, then you need to tell us what is in Column 17 and 49 that they have to be formatted as Text (are they dates that come out as 5-digit numbers, currency with decimal and commas reversed, numbers with leading or trailing zeroes in which the trailing zeroes are lost, etc.)?

Sub LoadSemiColonDelimitedData()
  Dim X As Long, FileNum As Long, TotalFile As String, Records() As String
  FileNum = FreeFile
  Open "c:\temp\DataFile.txt" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Records = Split(TotalFile, vbNewLine)
  Application.ScreenUpdating = False
  For X = 0 To UBound(Records) - 1
    Rows(X + 1) = Application.Transpose(Split(Records(X), ";"))
  Next
  Application.ScreenUpdating = True
End Sub

 

[carolynlush]

Rick - Since I am new to VB not sure what you mean about it "working for me in a general way". When I put this code in it does not work. I am getting multiple errors even when I tell it to continue to see if the end result is ok. Text info has leading zeros so it needs to be in text.

 

[Rick Rothstein]

Rick - Since I am new to VB not sure what you mean about it "working for me in a general way". When I put this code in it does not work. I am getting multiple errors even when I tell it to continue to see if the end result is ok. Text info has leading zeros so it needs to be in text.

One reason it probably did not work is I forgot to change the path/filename from my test location to the path/filename you posted in Message #1. The code below now has that path/filename, so see if it runs for you (that is all I meant by "run in a general way... if any data needs special formatting, I planned to address that later... for now, I just want to know if the file ends up in the proper cells on the active worksheet)...

Sub LoadSemiColonDelimitedData()
  Dim X As Long, FileNum As Long, TotalFile As String, Records() As String
  FileNum = FreeFile
  Open "C:\Documents and Settings\LUSHC\My Documents\CSR 04-30-13\file2.txt" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Records = Split(TotalFile, vbNewLine)
  Application.ScreenUpdating = False
  For X = 0 To UBound(Records) - 1
    Rows(X + 1) = Application.Transpose(Split(Records(X), ";"))
  Next
  Application.ScreenUpdating = True
End Sub

 

[carolynlush]

Still no luck. Now I am getting another error

Run Time Error '1004':
Method 'Rows' of object'_Global' failed

 

[carolynlush]

The error is pointing to this row

Rows(X + 1) = Application.Transpose(Split(Records(X), ";"))

 

[vds1]

If you can share the text file (maybe edited version in case you have sensitive data) in a skydrive (or free upload site) and paste the link here that would be great.