Repeatable Fixed Width Import

D

donnie darko

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?
 
N

Nick Hodge

Donnie

Record a macro of yourself doing it

Tools>Macros>record 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
(e-mail address removed)
www.nickhodge.co.uk
 
D

Dave Peterson

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
 
D

donnie darko

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
 
D

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.
 
D

donnie darko

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
 
D

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 said:
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
Click to expand...
 
D

donnie darko

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),Array(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),Array(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),Array(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),Array(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),
_
 
D

donnie darko

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.
 
D

Dave Peterson

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.aspx?scid=KB;EN-US;q134826&
XL: "Out of Memory" Message Using the OpenText Method



donnie said:
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),Array(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),Array(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),Array(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),Array(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),
_
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel 2002, problem with Text to Columns 0
Unable to set the desired column width for a table with merged cells 0
Unable to set the desired column width for a table with merged cells 0
Fixed column widths on by default? 2
Import of Text Delimited file into excell 1
Fixed Width Text Import Settings 4
Locking column widths & importing data in Excel 2003 0
Text to column, fix width 2

Top