Opening several space delimited files

I have been sent several files that I want to open in
Excel. What is the procedure to convert files from
..txt to .xls without manually doing it for each file?

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.


Alex wrote:
>
> I have been sent several files that I want to open in
> Excel. What is the procedure to convert files from
> .txt to .xls without manually doing it for each file?

--

Dave Peterson
ec35720@msn.com