If your text files are always the same layout, maybe you could record a macro
when you do it once manually.
Then generalize it to retrieve the file you want.
Post back if you need help. Post your recorded macro and I'm sure you'll get
lots of suggestions.
===
Some notes from an earlier post:
If these flat files are always the same layout, you may want to record a macro
when you do it by hand the next time.
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
And you may need to adjust some ranges (depending on what you did when you
recorded the macro). Post back with a snippet of your code (not the workbook)
and explain your problem.
But now whenever you need to import a text file with that layout, you can just
open your "importer" workbook and click the giant button.
