Rocket Software Homepage
Forum Home Forum Home > AccuTerm Knowledge Base (read only) > Advanced Features
  New Posts New Posts RSS Feed - Object Bridge & Excel - Delete a sheet
  FAQ FAQ  Forum Search   Register Register  Login Login

The AccuTerm forum has moved. Go to community.rocketsoftware.com to register for the new Rocket forum.

Forum LockedObject Bridge & Excel - Delete a sheet

 Post Reply Post Reply
Author
Message
AHoeben View Drop Down
Newbie
Newbie


Joined: June 30 2008
Location: United States
Status: Offline
Points: 10
Post Options Post Options   Thanks (0) Thanks(0)   Quote AHoeben Quote  Post ReplyReply Direct Link To This Post Topic: Object Bridge & Excel - Delete a sheet
    Posted: August 26 2015 at 2:14pm
I've looked at the UPDATE.EXCEL.SPREADSHEET in OBJBP which is a great example of how to access the properties and methods of "ExcelApplication"
 
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
Tony
Back to Top
PSchellenbach View Drop Down
Admin Group
Admin Group

Moderator

Joined: December 15 2003
Location: United States
Status: Offline
Points: 2150
Post Options Post Options   Thanks (0) Thanks(0)   Quote PSchellenbach Quote  Post ReplyReply Direct Link To This Post Posted: August 26 2015 at 3:12pm
Hi Tony -

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

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.