I have been reading a ton of posts about exl2k and templates for the last 2 days, but I haven't found any in regards to what I'm doing wrong. I thought I wasn't picking up my template, but I just realized I am picking it up. I looked at sheet 2 (which is not used but I did define it in the mht) and my columns 5 - 8 are marked as currency. In my sheet 1, it's like it's overriding the mht. My columns 5 - 8 are all custom (with the heading being general). I took off all formatting of my fields 5 - 8, but it's still showing up as custom. Any thoughts on what I'm doing wrong? This is the code.

SET NODATA = ' '
DEFINE FILE RATES_COMBINED2
MKTVL_PLUS_ACCR/P17.2=GROSS_END_MKT_VAL + GROSS_END_ACCRUED_INCOME;
GROSS_ROR2/D18.4=GROSS_ROR;
NET_ROR2/D18.4=NET_ROR;
VERIFIED_DATE/MDYY=VERIFICATIONDATE;
END

TABLE FILE RATES_COMBINED2
PRINT
ACCOUNT_SELECTED AS 'Account Number'
NBTBSGAC.SHRT_NAME AS 'Account Name'
GROSS_START_DATE AS 'From'
GROSS_END_DATE AS 'To'
VERIFIED_DATE AS 'Verified As of Date'
MKTVL_PLUS_ACCR AS 'Ending Market Value'
GROSS_ROR2 AS 'Total Return,Gross of Fees'
NET_ROR2 AS 'Total Return,Net of Fees'
BY ACCOUNT_SELECTED NOPRINT
BY GROSS_START_DATE NOPRINT
BY GROSS_END_DATE NOPRINT
ON TABLE SET PAGE-NUM OFF

ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'perf_hist_template' SHEETNUMBER 1

END

This message has been edited. Last edited by: Kerry,

quote:

In my sheet 1, it's like it's overriding the mht.

Which is exactly what it does.

When you specify a particular sheet as a target, WebFOCUS replaces the target sheet with the Excel output (ish) including styling etc. etc.

If you want to have formats then you must apply them via VBA.

T

Pam,

WebFOCUS uses Excel templates by populating one or more sheets. Cells in other sheets refer to the cells in these WebFOCUS-populated sheets - these are the sheets that can be reformatted in Excel.

I just wanted to confirm that your code is populating sheet 1 of perf_hist_template. Does sheet 2 refer to cells in sheet 1? Reformatted columns 5 - 8 are in sheet 2?

Francis - sheet 2 does not refer to cells in sheet 1. I just created my mht and formatted sheet 1 and sheet 2 by putting a space in column 1 (on both sheets), formatted columns 5 - 8 as currency (on both sheets) and then saved it.

What I am trying to do is ... my business wants to see 2 digit decimals (123.45) in the column, but if they click on the column they want to see 4 digit decimals (123.4567) in the formula box at the top of excel. I was told I could do this using templates. So, I'm sending 4 digits after decimal to excel spreadsheet, but I have the mht defined as 2 digit after the decimal format currency.

Is this something I can do?

Tony - what is VBA?

The formatting should only be done in sheet 2 - sheet 1 is where WebFOCUS puts the data - any formatting there will be overridden by the WebFOCUS report. Sheet 1 should be ignored by the end user. There are discussions on how to hide the WebFOCUS-populated sheets (sheet 1 in your case).

I usually put the WebFOCUS-populated sheets AFTER the one that the user should be looking at, and I clearly name these as "data" or something to identify them.

VBA - Visual Basic for Applications. Excel macros use VBA. It is another way to format a WebFOCUS-generated spreadsheet after it gets created.

A possible alternative solution is to use output format EXL2K FORMULA. Columns formatted by WebFOCUS as Dxx.2 will appear in the spreadsheet with two decimals, but show the full set of decimals when displayed in the formula box.

Dan, I've never used FORMULA but that worked perfectly! Thank you so much for the suggestion. Much easier solution.

Pam, I'm glad it worked. And thanks to Francis' response to your post, I learned some more about using Excel templates, which I've never used before.