Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing CSV file (saved as Text) into XL as Text -- over 60 colu | Excel Discussion (Misc queries) | |||
EXCEL97 FILE NAMES TRUNCATED TO THE FIRST LETTER IN THE FILE NAME | New Users to Excel | |||
Importing text file, only option to edit existing file | Excel Discussion (Misc queries) | |||
Importing Text File | Excel Discussion (Misc queries) | |||
Importing text file | Excel Discussion (Misc queries) |