excel problem

[danishgambit]

OE 10.2b
excel 2003.

Afternoon 'talkers,

I know there are plenty of posts on this forum about using excel and progress, but I can't find a solution to the problem I'm having. I feel sure it's relatively commonplace so either I've missed it, or it's so straight forward that no-one has had a problem with it.

I've dumped data to 3 csv files that I want to open in 3 separate tabs on an excel file.
I can do 1 file easily enough, using

DEFINE VARIABLE chExcelApplication AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkBook             AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkSheet            AS COM-HANDLE NO-UNDO. 
 
CREATE "Excel.Application" chExcelApplication.
    chExcelApplication:WindowState = 1.
    ASSIGN chExcelApplication:VISIBLE = false.
 
    chExcelApplication:Workbooks:Open(vFileName).
    chWorkBook = chExcelApplication:WorkBooks:ITEM(1).
    chExcelApplication:SheetsInNewWorkBook = 1.
    chWorkSheet = chExcelApplication:Sheets:ITEM(1).
   .....

I can open a spreadsheet with multiple tabs and loop round each worksheet changing the name etc, but I can't workout how to open a csv file for each tab.

Any ideas ?

TIA,

DG.

 

[Rob Fitzpatrick]

So how would you do it manually in Excel? Have you tried?

 

[danishgambit]

Hi Rob,

yep, had a look, and generated a macro, which leads to some code trying to replicate the import routine so I get something akin to the code
below that I got from the kb (# P97931), but I was hoping there's a way to do it with the "open" command, similar to the one used to open 1 file.

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 = TRUE
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.

 

[Stefan]

Easiest, honest, is to create an Excel XML spreadsheet. You then have full control over your sheet by 'simply' creating the correct XML.