Fixed length file import??

[Trey]

I have a fixed length file, thats 700 characters long. Each week i
have to go in and manually import the file, and tell excel where the
fields are. The fields are the same length everytime. Is there a way
to open a new excel sheet and have it automatically import the
information? I have been looking at some scripts, but i have no idea
where to put them.

 

[Tom Ogilvy]

Turn the macro recorder on

Open the file and go through the text import wizard.

Turn off the macro recorder.

Look at the recorded code. This contains the specificiations you gave.

You can add code to prompt you for a file name and feed it into the first
argument of the OPENTEXT method.

then to import the file, you just run the code.

If you need help on modifying the recorded code to accept a dynamic file
name, post back with the recorded code.

some informationon getting started with macros at David McRitchie's site
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Trey]

Tom,

Thanks for the info, its working great so far. I would like to make it
accept a dynamic file name. Here is the code.

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;p:\My Documents\Clients\Campus Partners\020406.txt",
Destination:=Range _
("A1"))
.Name = "020406"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 2, 2, 2, 2, 1, 1, 1, 2, 1,
1, 2, 1, 1, 2, 2, 2, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 1, 1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(1, 5, 30, 9, 25, 25, 25, 18,
9, 1, 1, 10, 10, 9, 5, 9, 2 _
, 2, 1, 2, 1, 1, 10, 10, 10, 6, 10, 10, 10, 7, 1, 7, 7, 7, 7,
3, 3, 3, 3, 3, 3, 10, 1, 7, 1, 1, 6, 10, 10, 10, 10 _
, 5, 5, 1, 5, 5, 10, 10, 10, 10, 10, 7, 10, 7, 1, 1, 1, 2, 3,
30, 25, 25, 25, 18, 9, 1, 1, 10)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


Thanks

 

[Tom Ogilvy]

Sub GetFile()
Dim sName as String
sName = Application.GetOpenFilename( _
FileFilter:="Text Files (*.txt),*.txt")
If sName = "False" then exit sub
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & sName, Destination:=Range("A1"))
.Name = "020406"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 2, 2, 2, 2, _
1, 1, 1, 2, 1,1, 2, 1, 1, 2, 2, 2, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(1, 5, 30, _
9, 25, 25, 25, 18, 9, 1, 1, 10, 10, 9, 5, 9, 2, _
2, 1, 2, 1, 1, 10, 10, 10, 6, 10, 10, 10, 7, 1, _
7, 7, 7, 7, 3, 3, 3, 3, 3, 3, 10, 1, 7, 1, 1, 6, _
10, 10, 10, 10, 5, 5, 1, 5, 5, 10, 10, 10, 10, _
10, 7, 10, 7, 1, 1, 1, 2, 3, 30, 25, 25, 25, 18, _
9, 1, 1, 10)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub