Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Excel Formula with multiple tabs
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Excel Formula with multiple tabs
 Login/Join
 
Silver Member
posted
Hi,

I would like to create the multile sheet excel book and write the formulas and populate values in a worksheet referencing cells from other sheets. Is there a way to do it? Please share some sample code.

Thank You
Sandhya

This message has been edited. Last edited by: Kerry,
 
Posts: 46 | Registered: December 06, 2011Reply With QuoteReport This Post
Gold member
posted Hide Post
I don't know how many tab you will need, but here is a simple one with the Excel Template having three tabs.

The first tab I have mapped to the cells of the second tab.

You need to save the Excel Template as a ".mht" file.

 
APP FI OUTPUT1 DISK output1.mht

TABLE FILE CAR
SUM
     CAR.BODY.DEALER_COST
     COMPUTE DIFF/D13 = (RETAIL_COST - DEALER_COST);
BY  LOWEST CAR.ORIGIN.COUNTRY
BY  LOWEST CAR.COMP.CAR
BY  LOWEST CAR.CARREC.MODEL
ON TABLE HOLD AS OUTPUT1 FORMAT EXL2K FORMULA TEMPLATE 'CAR_TESTmht' SHEETNUMBER 2
END
-RUN

TABLE FILE CAR
SUM
     CAR.BODY.SALES
     COMPUTE DIFF/D13 = (RETAIL_COST - DEALER_COST);
BY  LOWEST CAR.ORIGIN.COUNTRY
BY  LOWEST CAR.COMP.CAR
BY  LOWEST CAR.CARREC.MODEL
ON TABLE PCHOLD FORMAT EXL2K FORMULA TEMPLATE 'OUTPUT1' SHEETNUMBER 3
END
-RUN

 


WF 7.6.10, Windows, PDF, Excel
 
Posts: 75 | Location: Dallas, TX | Registered: February 12, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
There are also techniques to embed a formula ini the excel spreadsheet, its a little tricky, but can work.

The following example could be expanded to get a value from another sheet if it existed.

e.g.
DEFINE FILE CAR
COL0/A25= 'Clearance/Type';
COL1/A25= 'Count';
COL2/A25= 'Average Cycle Time (Days)';
END
TABLE FILE CAR
HEADING
"<+0 <COL0<+0 <COL1<+0 <COL2"
PRINT SEATS
BY COUNTRY
BY CAR NOPRINT
ON COUNTRY RECAP
FLD0/A250= COUNTRY | '/ ' | CAR;
CLRTYP/A500='<td align=left  x:num x:fmla=''=A1''>0</td>';
ON COUNTRY SUBFOOT
"<FLD0<CLRTYP"
ON TABLE SUBHEAD
"SUMMARY - BY CLEARANCE - TYPE"
ON TABLE SUBFOOT
""
""
ON TABLE SET STYLE *
TYPE=TITLE, COLOR=NAVY, STYLE=BOLD+UNDERLINE, $
TYPE=REPORT, TITLETEXT='Testing', $
TYPE=TABHEADING,STYLE=BOLD,$
TYPE=SUBHEAD,STYLE=BOLD,$
TYPE=HEADING, LINE=1, STYLE=BOLD+UNDERLINE, COLOR=NAVY,$
TYPE=HEADING,HEADALIGN=BODY,$
TYPE=HEADING,OBJECT=TEXT,LINE=1,ITEM=1, STYLE=BOLD+UNDERLINE,
COLOR=NAVY,$
TYPE=HEADING,OBJECT=TEXT,LINE=1,ITEM=2, STYLE=BOLD+UNDERLINE,
COLOR=NAVY,$
TYPE=HEADING,OBJECT=TEXT,LINE=1,ITEM=3, STYLE=BOLD+UNDERLINE,
COLOR=NAVY,$
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
Sandhya,
Just to let you know, the Excel .mht files will work in Excel 2003 and earlier. Microsoft Excel 2007 does not create .mht files; however, if you have an earlier version of Excel you can create the template in it and the WebFOCUS templates will still work. If you have WF version 7.7+, I believe you can use the EXL07 format to create templates.


WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
 
Posts: 38 | Location: Houston, Texas | Registered: May 01, 2008Reply With QuoteReport This Post
Gold member
posted Hide Post
Emily,

You are correct; however, when I used the GUI to select "Excel Formula", the only templates it recognized were my .mht files and not any template created for Excel 2007.

Just a fyi ...


WF 7.6.10, Windows, PDF, Excel
 
Posts: 75 | Location: Dallas, TX | Registered: February 12, 2004Reply With QuoteReport This Post
Member
posted Hide Post
There was an Excel presentation "Summit2011Lab_ExcelSolutions.pdf" by Kathy Kendall in the 'Information Builders Summit 2011 User Conference' that may be of use to you. Hope this helps.


WebFocus 7611
Excel/PDF/HTML
 
Posts: 1 | Registered: December 06, 2011Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Excel Formula with multiple tabs

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.