Aha! I'm sure other power users already know how to handle this--but I
just removed some of the continuations and made longer lines of code
that stretched off the screen, and it worked! Thanks for your help
Dave, and I'll post if I have additional questions.

On Dec 6, 10:22 am, "donnie darko" wrote:
Got it. Now, though, Excel is telling me I have too many line
continuations and quits the macro halfway through... How do I break
this up so it's acceptable? *In this version of the macro, I just
opened the file, but I plan to add the variant once I get this set up.

Sub Macro3()
'
' Macro3 Macro
'

'
Application.CommandBars("Task Pane").Visible = False
ChDir "D:\"
Workbooks.OpenText Filename := "D:\Test.lst", Origin := 437,
StartRow := 1, _
DataType := xlFixedWidth, FieldInfo :=
Array(Array(0,2),Array(8,2),Array(25,2), _

Array(33,2),Array(34,2),Array(39,2),Array(45,2),Ar ray(46,2),Array(47,2),Array(
_

48,2),Array(49,2),Array(52,2),Array(53,2),Array(54 ,2),Array(55,2),Array(56,2),
_

Array(57,2),Array(58,2),Array(59,2),Array(60,2),Ar ray(61,2),Array(62,2),Array(
_

64,2),Array(66,2),Array(68,2),Array(69,2),Array(70 ,2),Array(71,2),Array(72,2),
_

Array(73,2),Array(74,2),Array(75,2),Array(76,2),Ar ray(77,2),Array(78,2),Array(
_

79,2),Array(80,2),Array(81,2),Array(82,2),Array(83 ,2),Array(84,2),Array(85,2),
_

Array(86,2),Array(87,2),Array(88,2),Array(89,2),Ar ray(90,2),Array(91,2),Array(
_

92,2),Array(93,2),Array(94,2),Array(95,2),Array(96 ,2),Array(97,2),Array(98,2),
_

Array(99,2),Array(100,2),Array(101,2),Array(102,2) ,Array(103,2),Array(104,2),
_

Array(105,2),Array(106,2),Array(107,2),Array(108,2 ),Array(109,2),Array(110,2),
_

Array(111,2),Array(112,2),Array(113,2),Array(114,2 ),Array(115,2),Array(116,2),
_

Array(117,2),Array(118,2),Array(119,2),Array(120,2 ),Array(121,2),Array(122,2),
_

Array(123,2),Array(124,2),Array(125,2),Array(126,2 ),Array(127,2),Array(128,2),
_

Array(129,2),Array(130,2),Array(131,2),Array(132,2 ),Array(133,2),Array(134,2),
_

Array(135,2),Array(136,2),Array(137,2),Array(138,2 ),Array(139,2),Array(140,2),
_

Array(141,2),Array(142,2),Array(143,2),Array(144,2 ),Array(145,2),Array(146,2),
_

Array(147,2),Array(148,2),Array(149,2),Array(150,2 ),Array(151,2),Array(152,2),
_

Array(153,2),Array(154,2),Array(155,2),Array(156,2 ),Array(157,2),Array(158,2),
_

Array(159,2),Array(160,2),Array(161,2),Array(162,2 ),Array(163,2),Array(164,2),
_

Array(165,2),Array(166,2),Array(167,2),Array(168,2 ),Array(169,2),Array(170,2),
_

Array(171,2),Array(172,2),Array(173,2),Array(174,2 ),Array(175,2),Array(176,2),
_

Array(177,2),Array(178,2),Array(179,2),Array(180,2 ),Array(181,2),Array(182,2),
_

Dave Peterson wrote:
If you want to use the File|Open code, then start by recording a new macro. But
don't use Data|import external data.

Use File|Open to retrieve the data. You're code to parse the data will look
kind of familiar, though.

donnie darko wrote:

Dave, thanks. I am still having trouble with the transition. The
File|Open section of the macro is very useful for what I am doing, so I
would like that to be a part of the final macro. But I can't seem to
get the right coding to apply the Data|Import from that file. It
always gets hung up on the first line of that section. Here's the
current version. Thanks for the help anyone can provide!

Sub ImportFixedWidth()
'
' ImportFixedWidth Macro
'

'

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="List Files,
*.LST", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

With ActiveSheet.QueryTables.Add(Connection:=myFileName ,
Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2 _
, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2 _
, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
.TextFileFixedColumnWidths = Array(8, 17, 8, 1, 5, 6, 1, 1, 1,
1, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 2, 2, 2, 26, 2, 4, 4, 63, 65, 2, 2, 2, 10, 2, 2, 2, 6,
1, 3, 1, 9, 3, 8, 7, 4, 6, 1, 3, 6, 8, 6, 8, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Dave Peterson wrote:
I was suggesting that you record a macro when you used File|Open, not
Data|Import external data. I'm still suggesting that you record a macro when
you do this and you'll be pretty close. But post back if you have questions.

But you may want to try just deleting this line:

Workbooks.OpenText Filename:=myFileName

if you want to keep the query.

donnie darko wrote:

Thanks! I'm on my way, and sorry for being a noob with regard to
macros--here is what I have so far. I'm getting hung up in the
transition from Dave's suggested opening (to get the file) and the code
after that (With ActiveSheet.QueryTables.Add....) Is there something
wrong with my syntax here, or did I make an illogical jump?

Sub ImportFixedWidth()
'
' ImportFixedWidth Macro
'

'

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="List Files,
*.LST", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName
With ActiveSheet.QueryTables.Add(Connection:=myFileName ,
Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2 _
, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2 _
, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
.TextFileFixedColumnWidths = Array(8, 17, 8, 1, 5, 6, 1, 1, 1,
1, 3, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 2, 2, 2, 26, 2, 4, 4, 63, 65, 2, 2, 2, 10, 2, 2, 2, 6,
1, 3, 1, 9, 3, 8, 7, 4, 6, 1, 3, 6, 8, 6, 8, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Dave Peterson wrote:
I like to create a dedicated macro workbook that contains the code. And then I
put a big button from the Forms toolbar on the only worksheet in that workbook.
I'll add a few instructions to that sheet, too.

I'd tweak the code to get the...

read more »