EXCEL COM using Direct Methed Issue

[Michael Rice]

I am trying to use EXCEL via Direct Methed. I can't figure out where I am going wrong with this. Seems to have an issue with the Worksheet Object. This is my first attempt to use the direct method, so I am sure it is me...

The program crashes with "Excel_PB_Support.exe has encountered a problem and needs to close. We are sorry for the inconvenience."

I have marked where the error occurs in the code. I sent a note to support, but hope to send them an update that it was a programming error (mine) and to disregard. BTW - PBCC50

Thanks for any help you can provide.
Michael

Update: PowerBASIC support states that a bug in EXCEL does not allow use of the Direct Interface.

 
#COMPILE  EXE
#DEBUG    ERROR ON
#DIM      ALL
#REGISTER NONE
#TOOLS    OFF
#INCLUDE "ExcelApp_v2.inc"
#IF NOT %DEF(%False)
   %False = 0&
#ENDIF
#IF NOT %DEF(%True)
   %True = NOT %False
#ENDIF
FUNCTION PBMAIN
'  -----------------------------------------------------------------------------
   DIM oExcelApp       AS Int__Application  ' Excel application object
   DIM oExcelWorkbook  AS Int__Workbook     ' Excel workbook object
   DIM oExcelWorksheet AS Int__Worksheet    ' Excel worksheet object
   DIM ExcelSheetsInNewWorkbook AS LONG     ' Excel SheetsInNewWorkbook
'  -----------------------------------------------------------------------------
   ' ---------------------------------------------------------------------------
   ' -                     Open a (NEW) instance of Excel                      -
   ' ---------------------------------------------------------------------------
   ' Start Excel
   oExcelApp = NEWCOM $PROGID_Excel_Application
   ' Could Excel be opened? If not, terminate this application...
   IF ISFALSE ISOBJECT(oExcelApp) OR ERR THEN
      STDOUT "Excel could not be started"
      SET oExcelApp = NOTHING
      EXIT FUNCTION
   ELSE
      oExcelApp.Visible(0) = %True
   END IF
   ' ===========================================================================
   ' ---------------------------------------------------------------------------
   ' -                         Open an Excel Workbook                          -
   ' ---------------------------------------------------------------------------
   ExcelSheetsInNewWorkbook = oExcelApp.SheetsInNewWorkbook(0)
   oExcelApp.SheetsInNewWorkbook(0) = 1     ' Set SheetsInNewWorkbook to 1
   oExcelWorkbook = oExcelApp.WorkBooks.Add
   IF OBJRESULT OR ERR THEN
      STDOUT "Could not open Excel workbook"
      SET oExcelApp = NOTHING
      SET oExcelWorkbook  = NOTHING
      EXIT FUNCTION
   ELSE
      PRINT ACODE$(oExcelWorkBook.Name)
   END IF
   oExcelApp.SheetsInNewWorkbook(0) = ExcelSheetsInNewWorkbook  ' Restore value
   ' ===========================================================================
   oExcelWorkSheet = oExcelWorkBook.ActiveSheet
   IF ISFALSE ISOBJECT(oExcelWorkSheet) OR ERR THEN
      STDOUT "Excel WorkSheet object error"
      EXIT FUNCTION
   END IF
   PRINT OBJRESULT
   PRINT "Worksheet pointer: ";OBJPTR(oExcelWorkSheet)
   ' This line causes the program to crash......................................
   PRINT ACODE$(oExcelWorkSheet.Name)
   ' ...........................................................................
'  oExcelWorkSheet.Activate(1)
 
'  oExcelWorkSheet.Name = "New worksheet name"
   ' ===========================================================================
   SET oExcelWorksheet = NOTHING
   SET oExcelWorkbook = NOTHING
   SET oExcelApp = NOTHING
END FUNCTION

[Michael Mattias]

Doesn't that have to be ACODE$(VARIANT$(oExcelWorksheet.name)) ?

Or maybe go thru the assignments explicitly?
vntName = oExcelworksheet.name
strName = VARIANT$(vName)
FinalNameToPrint = ACODE$(sName)


I just got my first real COM program working and I had to learn to think VARIANT, VARIANT, VARIANT......

Just don't tell me ......

oExcelApp = NEWCOM $PROGID_Excel_Application

... is new syntax for ....

    SET oExcelApp = NEW ExcelApplication IN $PROGID_ExcelApplication11

... because I do not want to hear that.


MCM

[Richard Angell]

Michael,

Life does get simpler if you read the manual
LET statement (with Objects)

SEE THE BOXED STATEMENTS near the bottom of the topic:
(Empaphasis added to quotes)

Previous versions of PowerBASIC Compilers supported another syntax for the creation of objects:
LET objvar = [NEW] Interface name ON ClassName|ProgID$
While this syntax will still be temporarily supported for the sake of compatibility, it will be removed in the next major release of PowerBASIC. We urge you to change existing source code to the new simplified syntax as soon as possible. It's as simple as inserting a director keyword of CLASS, NEWCOM, GETCOM, or ANYCOM, and removal of InterfaceName/ON.

Previous versions of PowerBASIC Compilers used the SET statement for creation of objects. LET now includes all the functionality of the old SET statement, so you should plan to remove all SET statements as soon as possible. This involves nothing more than changing every SET to LET, or simply deleting every SET.

Actually in the PBWin 8 and PBCC 4 help it said:

SET has been superceded by the LET statement, although SET remains supported for a limited period. Existing code should be converted to the new syntax as soon as possible. Simply remove the keyword SET from your source code.

So SET in this sense is being sunSETted

[Michael Mattias]

Well, I just quoted the job yesterday. The first program was created with 8x, and all it does (COM-wise) is a couple of reads from an Oracle database using ADO. (FWIW, the same code works with at least Oracle 8.1 and 10g).

I think If I get this contract I'll probably do the job with 5x/9x, since the user will be responsible for long-term maintenance and I may as well give him something compatible with the compiler he will have to order.

I actually have not decided if it would be PB/Win or PB/CC yet. I could go either way. That's kind of a neat thing about the PB compilers, you can do the same stuff in two totally different contexts with the same source code.

MCM

[Richard Angell]

That's kind of a neat thing about the PB compilers, you can do the same stuff in two totally different contexts with the same source code.

Yes, sometimes, it is. Also, with the new additions to the command set for GRAPHIC WINDOWS, one could even do a pretty nice GUI in PBCC easier than before.