Kbase P97931: How to import multiple text files into separate sheets of an EXCEL workbook?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
22/11/2004 |
|
Status: Unverified
GOAL:
How to import multiple text files into separate sheets of an EXCEL workbook?
FACT(s) (Environment):
Windows
FIX:
The following code will import a list three files containing tab separated data into three separate sheets of an EXCEL workbook:
DEFINE VARIABLE chExcelApplication AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkbook AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorksheet AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chQueryTable AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE cConnection AS CHARACTER NO-UNDO.
DEFINE VARIABLE lResult AS LOGICAL NO-UNDO.
DEFINE VARIABLE cFilesToImport AS CHARACTER NO-UNDO.
DEFINE VARIABLE cSheetTitles AS CHARACTER NO-UNDO.
DEFINE VARIABLE iNumberOfFiles AS INTEGER NO-UNDO.
/* Initialize Excel, File and Title Lists */
CREATE "Excel.Application" chExcelApplication.
chExcelApplication:Workbooks:ADD.
ASSIGN
cFilesToImport = "myfile1.txt,myfile2.txt,myfile3.txt"
cSheetTitles = "Alpha, Beta, Theta"
chWorkbook = chExcelApplication:WorkBooks:Item(1).
/* Import ecah file's data into a new sheet of the workbook */
DO iNumberOfFiles = 1 TO NUM-ENTRIES(cFilesToImport).
ASSIGN
FILE-INFO:FILE-NAME = ENTRY(iNumberOfFiles, cFilesToImport)
cConnection = "TEXT;" + FILE-INFO:FULL-PATHNAME
chWorkSheet = chExcelApplication:Sheets:Item(iNumberOfFiles)
chWorkSheet:Name = ENTRY(iNumberOfFiles, cSheetTitles)
lResult = chWorkSheet:QueryTables:Add(cConnection, chWorkSheet:cells(1,1)).
ASSIGN
chQueryTable = chWorkSheet:QueryTables(1)
chQueryTable:FieldNames = TRUE
chQueryTable:RowNumbers = False
chQueryTable:FillAdjacentFormulas = False
chQueryTable:PreserveFormatting = FALSE
chQueryTable:RefreshOnFileOpen = FALSE
chQueryTable:RefreshStyle = 1
chQueryTable:SavePassword = False
chQueryTable:SaveData = True
chQueryTable:AdjustColumnWidth = True
chQueryTable:RefreshPeriod = 0
chQueryTable:TextFilePromptOnRefresh = FALSE
chQueryTable:TextFilePlatform = 437
chQueryTable:TextFileStartRow = 1
chQueryTable:TextFileParseType = 1
chQueryTable:TextFileTextQualifier = 1
chQueryTable:TextFileConsecutiveDelimiter = False
chQueryTable:TextFileTabDelimiter = True
chQueryTable:TextFileSemicolonDelimiter = False
chQueryTable:TextFileCommaDelimiter = False
chQueryTable:TextFileSpaceDelimiter = False
&nb.sp; chQueryTable:TextFileTrailingMinusNumbers = True
lResult = chQueryTable:Refresh
chQueryTable:BackgroundQuery = False.
END.
/* Make Spreadsheet Visible */
chExcelApplication:Visible = true.
/* Release All Objects */
RELEASE OBJECT chQueryTable NO-ERROR.
RELEASE OBJECT chWorkSheet NO-ERROR.
RELEASE OBJECT chWorkBook NO-ERROR.
RELEASE OBJECT chExcelApplication NO-ERROR..