Importing into predefined fields

[Peter Lawton]

I have a text file I need to import into Excel 2002. It is not delimited
however I know the char length for each field. Is there a way I can import
mutliple records into a table with predefined field sizes (e.g. Field 1 - 10
chars, Field 2 - 3 chars, Field 3 - 1 char etc up to 80 fields) so the
correct info drops into the correct cell?

many thanks

Peter Lawton

 

[Pete_UK]

Use the Data Import Wizard - specify that it has fixed-width fields on
one panel and on the next panel you can adjust the width of each field
to suit.

Hope this helps.

Pete

 

[Dave Peterson]

Name the file *.txt (or anything but *.csv).

Then file|open this text file.
Excel will show you the text import wizard where you can specify that your input
file is fixed width. Then you can draw lines (and adjust any lines that excel
guessed incorrectly) to indicate each field.

Then you can tell excel what type of field each is (general, date, text or
skip).

If you're going to do this a lot, you may want to record a macro when you do it
the first time. Then you can just save that macro and replay it when you need
it.

 

[Peter Lawton]

Thanks but I am aware of this wizard. I want to import multiple files with
the same field format and wish to avoid defining up to 80 fields each time I
do so. This is why I asked about pre defining field lengths.



If a macro can do this that would be great. Please could you elaborate a
little as I'm more of an access than an excel man? I tried it quickly but I
have to start the macro before opening the file. If I try to run the macro
later it opens the same file which means I can't apply it to a different
file.

 

[Dave Peterson]

Start a new workbook.

Start recording a macro when you open one *.txt file.

Keep recording when you add all your page setup, filters, headers, freezepanes,
anything you can think of.

Stop recording when you're done.

Close the workbook that contains that imported data--not the macro workbook.

Put a button from the Forms toolbar on a worksheet in that macro workbook.

Assign the macro to that button.

Since you'll want to pick and choose the file(s) to import, you won't want the
file name hardcoded into the macro.

Your code will contain some lines that look like this:

Workbooks.OpenText Filename:="C:\My Documents\excel\myfile.txt", _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(5, 1), _
Array(15, 1), Array(24, 1), Array(38, 1))


You'll want to keep all the other stuff (formatting/headers/etc), but modify
this portion a bit.

Option Explicit
Sub testme01()

Dim wkbk As Workbook
Dim myFileNames As Variant
Dim NewFileName As String
Dim iCtr As Long

myFileNames = Application.GetOpenFilename("Text Files, *.txt", _
MultiSelect:=True)

If IsArray(myFileNames) = False Then
Exit Sub
End If

For iCtr = LBound(myFileNames) To UBound(myFileNames)
'modify this line according to your recorded macro
'fieldinfo:= will change
Workbooks.OpenText Filename:=myFileNames(iCtr), _
Origin:=437, StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(7, 1), _
Array(11, 1), Array(19, 1), Array(21, 1))

Set wkbk = ActiveWorkbook

'rest of recorded code

Next iCtr

End Sub

After you've built this macro to do what you want, you just open that macro
workbook and click that big button to import your file(s).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Peter Lawton]

Thanks so much for such a detailed reply. I really appreciate the time and
effort. You've not only solved my problem you also taught me a lot and led
me into another fascinating area to explore.

peter