importing text file in excel97
 

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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

importing text file in excel97
 

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.


--
Regards,
Tom Ogilvy

"BubBob" wrote in message
...
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.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/