The AccuTerm forum has moved. Go to community.rocketsoftware.com to register for the new Rocket forum. |
Object Bridge & Excel - Delete a sheet |
Post Reply |
Author | |
AHoeben
Newbie Joined: June 30 2008 Location: United States Status: Offline Points: 10 |
Post Options
Thanks(0)
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
|
|
PSchellenbach
Admin Group Moderator Joined: December 15 2003 Location: United States Status: Offline Points: 2150 |
Post Options
Thanks(0)
|
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 |
|
Post Reply | |
Tweet
|
Forum Jump | Forum Permissions You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum |