importing text file in excel97

  • Thread starter Thread starter BubBob
  • Start date Start date
B

BubBob

It's really easy to import and parse a text file in excel 2000-2003,
using get external data, but how to do it in excel 97? Example:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\...", _
Destination:=Range("A1"))
.Name = "ytdorder"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 11
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 9, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1)
.TextFileFixedColumnWidths = Array(11, 41, 8, 5, 3, 9, 8, 8, 8,
10, 12)
.TextFileDecimalSeparator = "."
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Is the any way to do it? At least a textfile can be opened in excel 97
in fixed width and splitted to columns (and parsed). Any ideas how to
make a macro.
 
Application.ScreenUpdating = False
set sh = ActiveSheet
workbooks.OpenText Filename:="C:\TextFiles\mytext.txt", . . . other args to
parse file . . .
Activesheet.UsedRange.copy Destination:=sh.Range("A1")
ActiveWorkbook.Close SaveChanges:=False
Application.ScreenUpdating = True

Assuming you text files will have identical format, the easy way to get the
arguments to OpenText is to turn on the macro recorder and bring in the file
using the text wizard.
 
Back
Top