I receive weekly data files from the USPS in a fixed
width format. Each file will have the exact same 23
fields defined when opened (position 1, 9, 16, 32, etc.).
Is there a way to create a macro that will automatically
parse the file at the correct 23 columns so this doesn't
have to be done manually each time I receive a new data
file?
Thanks,
Jan

(From a previous post--so don't trust the column layout!)
=========


Start a new workbook.
Tools|macro|Record new macro
Do all your importing and reformatting.
Include all the things you like (worksheet headers, print headers/footers/freeze
panes/Data|Filter|Autofilter)

Stop recording and save that workbook with the recorded code in it.
Put a giant button from the forms toolbar on the first worksheet in that
"importer" workbook. Assign your macro to the button.

You'll probably have 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:\myfile.txt", Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(15, 1), _
Array(41, 1))

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):

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



Jan wrote:

I receive weekly data files from the USPS in a fixed
width format. Each file will have the exact same 23
fields defined when opened (position 1, 9, 16, 32, etc.).
Is there a way to create a macro that will automatically
parse the file at the correct 23 columns so this doesn't
have to be done manually each time I receive a new data
file?
Thanks,
Jan

--

Dave Peterson

Dave,

Worked just great. Thanks so much!

Jan

-----Original Message-----
(From a previous post--so don't trust the column layout!)
=========


Start a new workbook.
Tools|macro|Record new macro
Do all your importing and reformatting.
Include all the things you like (worksheet headers,
print headers/footers/freeze
panes/Data|Filter|Autofilter)

Stop recording and save that workbook with the recorded
code in it.
Put a giant button from the forms toolbar on the first
worksheet in that
"importer" workbook. Assign your macro to the button.

You'll probably have 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:\myfile.txt",
Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array
(0, 1), Array(15, 1), _
Array(41, 1))

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):

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



Jan wrote:

I receive weekly data files from the USPS in a fixed
width format. Each file will have the exact same 23
fields defined when opened (position 1, 9, 16, 32,
etc.).
Is there a way to create a macro that will
automatically
parse the file at the correct 23 columns so this
doesn't
have to be done manually each time I receive a new data
file?
Thanks,
Jan

--

Dave Peterson

.