I work with a system which gives me a fixed width dump of data for each
file I request. This must then be imported into Excel, and the columns
have to be lined up with the import wizard every time (about 50
columns)! The columns will always be the same width. How can I save
or automate the column widths to easily import the data after setting
up the columns just once?

Donnie

Record a macro of yourself doing it

ToolsMacrosrecord new macro and save it to your personal workbook and even
assign a shortcut key or button to run it

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"donnie darko" wrote in message
ups.com...
I work with a system which gives me a fixed width dump of data for each
file I request. This must then be imported into Excel, and the columns
have to be lined up with the import wizard every time (about 50
columns)! The columns will always be the same width. How can I save
or automate the column widths to easily import the data after setting
up the columns just once?

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 name of the file to open from the user and then
include code that adds some more stuff--like formatting, filters, subtotals,
page setup (headers/footers/rows to repeat at top/etc).

Then it actually becomes a tool that makes life a lot easier.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

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

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

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

donnie darko wrote:

I work with a system which gives me a fixed width dump of data for each
file I request. This must then be imported into Excel, and the columns
have to be lined up with the import wizard every time (about 50
columns)! The columns will always be the same width. How can I save
or automate the column widths to easily import the data after setting
up the columns just once?

--

Dave Peterson

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 name of the file to open from the user and then
include code that adds some more stuff--like formatting, filters, subtotals,
page setup (headers/footers/rows to repeat at top/etc).

Then it actually becomes a tool that makes life a lot easier.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

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

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

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

donnie darko wrote:

I work with a system which gives me a fixed width dump of data for each
file I request. This must then be imported into Excel, and the columns
have to be lined up with the import wizard every time (about 50
columns)! The columns will always be the same width. How can I save
or automate the column widths to easily import the data after setting
up the columns just once?

--

Dave Peterson

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 name of the file to open from the user and then
include code that adds some more stuff--like formatting, filters, subtotals,
page setup (headers/footers/rows to repeat at top/etc).

Then it actually becomes a tool that makes life a lot easier.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

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

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

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

donnie darko wrote:

I work with a system which gives me a fixed width dump of data for each
file I request. This must then be imported into Excel, and the columns
have to be lined up with the import wizard every time (about 50
columns)! The columns will always be the same width. How can I save
or automate the column widths to easily import the data after setting
up the columns just once?

--

Dave Peterson

--

Dave Peterson

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 name of the file to open from the user and then
include code that adds some more stuff--like formatting, filters, subtotals,
page setup (headers/footers/rows to repeat at top/etc).

Then it actually becomes a tool that makes life a lot easier.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

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

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

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

donnie darko wrote:

I work with a system which gives me a fixed width dump of data for each
file I request. This must then be imported into Excel, and the columns
have to be lined up with the import wizard every time (about 50
columns)! The columns will always be the same width. How can I save
or automate the column widths to easily import the data after setting
up the columns just once?

--

Dave Peterson

--

Dave Peterson

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 name of the file to open from the user and then
include code that adds some more stuff--like formatting, filters, subtotals,
page setup (headers/footers/rows to repeat at top/etc).

Then it actually becomes a tool that makes life a lot easier.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

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

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

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

donnie darko wrote:

I work with a system which gives me a fixed width dump of data for each
file I request. This must then be imported into Excel, and the columns
have to be lined up with the import wizard every time (about 50
columns)! The columns will always be the same width. How can I save
or automate the column widths to easily import the data after setting
up the columns just once?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

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 name of the file to open from the user and then
include code that adds some more stuff--like formatting, filters, subtotals,
page setup (headers/footers/rows to repeat at top/etc).

Then it actually becomes a tool that makes life a lot easier.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

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

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

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

donnie darko wrote:

I work with a system which gives me a fixed width dump of data for each
file I request. This must then be imported into Excel, and the columns
have to be lined up with the import wizard every time (about 50
columns)! The columns will always be the same width. How can I save
or automate the column widths to easily import the data after setting
up the columns just once?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

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 »

Glad you got it working...

Someday, you may find that another macro that you create by recording when you
import a text file won't work--you'll get an out of memory error.

If you ever get this, MS has a workaround:

http://support.microsoft.com/default...EN-US;q134826&
XL: "Out of Memory" Message Using the OpenText Method



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 name of the file to open from the user and then
include code that adds some more stuff--like formatting, filters, subtotals,
page setup (headers/footers/rows to repeat at top/etc).

Then it actually becomes a tool that makes life a lot easier.

My tweaked code could look a little like:

Option Explicit
Sub Testme01()

Dim myFileName As Variant

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

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

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

donnie darko wrote:

I work with a system which gives me a fixed width dump of data for each
file I request. This must then be imported into Excel, and the columns
have to be lined up with the import wizard every time (about 50
columns)! The columns will always be the same width. How can I save
or automate the column widths to easily import the data after setting
up the columns just once?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson