My program is passing data from Universe to multiple tabs on the spreadsheet, using FTD.  Some sheets, based on the type of job, don't receive any data.  I would like to delete the tab from the spreadsheet.  I haven't been able to figure out how to delete the sheet from a workbook.  Digging into Excel I see there is a "sheets" object.

 

Can I delete the sheet from the Excel Application object.  Or do I need a workbook object, or a sheet object, or both?

 

I was able to create a macro to delete the sheet, and then have my code invoke the "Run" method to run the macro, but I'd really like to not require Macro's for each sheet that I might want to delete.  here is what I have so far.  Much stolen from UPDATE.EXCEL.SPREADSHEET.  Below is a chunk of my code. 

 

Thanks for any direction you can provide.

 

Tony

 

 

 

CALL ATINITOBJMGR(ERRMSG, OPTS)
IF ERRMSG NE '' THEN PRINT ERRMSG:", HIT ANY KEY" ;  INPUT ZZZ,1
* CREATE EXCEL APPLICATION OBJECT
CALL ATCREATEOBJECT('Excel.Application',ExcelApplication,ERRMSG,OPTS)
IF ERRMSG NE '' THEN PRINT ERRMSG:", HIT ANY KEY" ;  INPUT ZZZ,1
IF ExcelApplication = 0 THEN PRINT "NO APP OBJECT, PRESS ANY KEY" ; INPUT ZZZ,1
* ENABLE EVENTS SO WE CAN USE THEM LATER
CALL ATSETPROPERTY(ExcelApplication, "EnableEvents", 1, ERRMSG,OPTS)
IF ERRMSG NE '' THEN PRINT ERRMSG:", HIT ANY KEY" ;  INPUT ZZZ,1
 * DISABLE ALERTs SO NO DIALOGS POP UP
CALL ATSETPROPERTY(ExcelApplication, "DisplayAlerts",0,ERRMSG,OPTS)
IF ERRMSG NE '' THEN PRINT ERRMSG:", HIT ANY KEY" ;  INPUT ZZZ,1
* GET A REFERENCE TO THE WORKBOOKS COLLECTION
CALL ATGETPROPERTY(ExcelApplication,"Workbooks",Workbooks, ERRMSG,OPTS)
 IF ERRMSG NE '' THEN PRINT ERRMSG:", HIT ANY KEY" ;  INPUT ZZZ,1
IF Workbooks <= 0 THEN PRINT 'NO WORKBOOK COLLECTIONS:' ; CALL ATRESETOBJMGR ; STOP
CALL ATINVOKEMETHOD(Workbooks, 'Open', THE.DIR:"JobReview":@LOGNAME:".XLSM", Workbook, ERRMSG,OPTS)
IF Workbook <= 0 THEN PRINT ERRMSG:", HIT ANY KEY" ; CALL  ATRESETOBJMGR ; STOP
CALL ATGETPROPERTY(Workbook, 'Worksheets', Worksheets, ERRMSG,OPTS)
 IF ERRMSG NE '' THEN PRINT ERRMSG:", HIT ANY KEY" ; INPUT ZZZ,1 ; CALL ATRESETOBJMGR ; STOP
IF Worksheets <= 0 THEN PRINT 'NO WORKSHEEETS COLLECTION:' ; CALL ATRESETOBJMGR; STOP

*** up to here seems to work.

*** HERE ARE A COUPLE OF MY ATTEMPTS AT DELETEING SHEETS.  CLOSE?
* CALL ATINVOKEMETHOD(Worksheet, 'Delete', 'Guillotine Cutting', '', ERRMSG, OPTS)
* CALL ATINVOKEMETHOD(Workbook, 'Sheets("Guillotine Cutting").Delete', '', Worksheet, ERRMSG, OPTS)

*** HERE I'M CALLING THE DELETE MACRO, WHICH DID WORK, BUT I'D PREFER NOT TO HAVE MACROS.

CALL ATINVOKEMETHOD(ExcelApplication,'Run','DelGuill','',ERRMSG,OPTS)
IF ERRMSG NE '' THEN PRINT ERRMSG:", HIT ANY KEY" ; INPUT ZZZ,1 ; STOP
***
CALL ATINVOKEMETHOD(Workbook,'SaveAs',THE.DIR:"JobReview":@LOGNAME:".XLSM",'',ERRORMSG,OPTS)
IF ERRMSG NE '' THEN PRINT ERRMSG:", HIT ANY KEY" ; INPUT ZZZ,1 ; STOP
CALL ATRELEASEOBJECT(ExcelApplication, ERRMSG, OPTS)
IF ERRMSG NE '' THEN PRINT '104 ':ERRMSG:", HIT ANY KEY" ; INPUT ZZZ,1 ; CALL ATRESETOBJMGR ; STOP

Since you have already figured out an Excel macro to delete the unwanted sheets, my recommendation is to convert your macro into a VBA script, then run the script from your file transfer program. You should be able to almost copy/paste the excel macro into AccuTerm's script editor. AccuTerm does not have references to built-in Excel objects, so you will need to make these explicit in the script. If you can run the script from AccuTerm and have it do what you want, then you can convert it into strings in your Pick BASIC program and launch it using ESC STX P ... . Its a lot simpler than getting all the right calls in ObjectBridge.

Thanks,

Pete