Import-FixedWidth-Template?

[zach]

I routinely receive a file which I import to Excel (2000) using fixe
widths on the excel import wizard. As the column breaks are always th
same, I wanted to know if there is any way I can save these (or creat
some sort of template) so that I do not have to manually take out al
the column breaks that the Wizard inserts, and manually put in th
correct column breaks.
Thanks

 

[Earl Kiosterud]

Zach,

With Excel 2002, you can set up a query, and refresh it to re-read the text
file. It remembers the field widths, formats, etc. I think that doesn't
exist in 2000 (I don't have it here). But you can record a macro while
using Data - Get External data. Or something similar. Either way you go
through the text import wizard once.

 

[Dave Peterson]

I think I'd start a new workbook and record a macro when I did it once manually.

Then I could just replay this macro whenever I wanted to import a file with the
same layout.

After you've recorded the macro, you'll probably want to adjust the code a
little to make it more generic. When you recorded your macro, you got something
that looked like:

Option Explicit
Sub Macro1()

Workbooks.OpenText Filename:="C:\My Documents\excel\Sample1.txt", Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

End Sub

Well, instead of having your filename in the code, you can give the user a
chance to pick it themselves (take a look at getopenfilename in VBA's help).
(If the filename never changed, you wouldn't even need this change.)

Sub macro1A()

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

I like to drop a big old button from the Forms toolbar on a worksheet in the
workbook that contains the code. I assign the macro to the button. And I add a
few notes to that worksheet.

Then just hit the big old button when I want to bring in my text file.


=======
And keep recording to add as much as you want--titles, page setup, filters,
freezepanes, all that stuff.