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,

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

 

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

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.

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 ...

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.