Kbase P98746: How to access the EXCEL instance created in a Procedure after the close of that Procedure?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
20/05/2008 |
|
Status: Unverified
GOAL:
How to access the EXCEL instance created in a Procedure after the close of that Procedure?
GOAL:
How to pass the COM-HANDLE of the EXCEL application object as a parameter from one procedure to another?
GOAL:
How to pass the COM-HANDLE of an application object as a parameter from one procedure to another?
FACT(s) (Environment):
Windows
Progress 8.x
Progress 9.x
OpenEdge 10.x
FIX:
1. Define the COM-HANDLE of the EXCEL application object as variable in the calling procedure (ProcedureA.p):
DEFINE VARIABLE chExcelApplication AS COM-HANDLE NO-UNDO.
2. Define the COM-HANDLE of the EXCEL application object as an OUTPUT PARAMETER in the called procedure (ProcedureB.p):
DEFINE OUTPUT PARAMETER chExcelApplication AS COM-HANDLE NO-UNDO.
3. Run the procedure that creates the EXCEL application object using the statement:
RUN procedureB.P(OUTPUT chExcelApplication).
4. When ProcedureB.p is closed, the EXCEL instance is still accessible to ProcedureA.p through its handle chExcelApplication.
5. In the following example:
5.1. ProcedureA.p invokes ProcedureB.p passing it the COM-HANDLE chExcelApplication as an OUTPUT PARAMETER.
5.2. ProcedureB.p creates an instance of the EXCEL application object.
5.3. ProcedureB.P loads two text files into sheet1 and sheet2 of the default workbook object.
5.4. ProcedureB.P finishes by returning the COM-HANDLE chExcelApplication to ProcedureA.p.
5.5 ProcedureA.p uses the COM-HANDLE chExcelApplication to access the EXCEL instance and load a third text file into the third sheet.
/*** The calling procedure: ProcedureA.p ***/
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 cFileToImport AS CHARACTER NO-UNDO.
DEFINE VARIABLE cSheetTitle AS CHARACTER NO-UNDO.
/* Initialize Excel, File and Title List */
RUN procedureB.P(OUTPUT chExcelApplication).
ASSIGN
cFileToImport = "myfile3.txt"
cSheetTitle = "Theta"
chWorkbook = chExcelApplication:WorkBooks:Item(1).
/* Import the third file in this procedure into a new sheet of the workbook */
ASSIGN
FILE-INFO:FILE-NAME = cFileToImport
cConnection = "TEXT;" + FILE-INFO:FULL-PATHNAME
chWorkSheet = chExcelApplication:Sheets:Item(3)
chWorkSheet:Name = cSheetTitle
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
chQueryTable:TextFileTrailingMinusNumbers = True
lResult = chQueryTable:Refresh
chQueryTable:BackgroundQuery = False.
/* 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.
/*** The called Procedure: ProcedureB.p ***/
DEFINE OUTPUT PARAMETER 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 List */
CREATE "Excel.Application" chExcelApplication.
chExcelApplication:Workbooks:ADD.
ASSIGN
cFilesToImport = "myfile1.txt,myfile2.txt"
cSheetTitles = "Alpha, Beta"
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
&nbs.p; 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
chQueryTable:TextFileTrailingMinusNumbers = True
lResult = chQueryTable:Refresh
chQueryTable:BackgroundQuery = False.
END.
/* Make Spreadsheet Visible */
chExcelApplication:Visible = true..