importing text files

[Grodon]

Is there a way to set up an 'import specification' so that
a text file will be imported the same way everytime (data
types, columns, etc.) without having to go through the
steps of the wizard? Access has this feature, just trying
to do the same thing with Excel.

Thanks in adv.
Grodon

 

[losmac]

Yes, record macro and use it every time You want!

 

[Chris Leonard]

You would need to use a bit of VB code.

I started a macro and then opened a file and added a couple of formats, you
could do something similar and then save the macro as an autoexecute, this
would work each time you opened the spreadsheet. If you added a form you
could read in different file names ... etc etc

Sub Macro2()
'
' Macro2 Macro
'
'
Workbooks.OpenText FileName:="My Documents\db.txt" _
, Origin:=xlWindows, StartRow:=1, DataType:=xlFixedWidth,
FieldInfo:= _
Array(Array(0, 1), Array(15, 1), Array(45, 1), Array(47, 1))
End Sub

 

[Earl Kiosterud]

Grodon,

The following is for Excel2002 (maybe 2000, but not 97).

With a workbook open, Data - Import external data - Import data. In the
"Select Data Source" dialog, switch to the folder and select your text file.
The Text Import Wizard will start, where you'll give it the specifics of the
data layout (fixed-length or delimited, formats, etc.).

The next time you want to read the text file, click any cell in the existing
sheet where the data was imported. Now do Data - !Refresh Data. Record a
macro of the refresh, and it gets even easier to refresh it.