I've searched Focal Point and I see a lot of posts where it says we can use Excel 2007/2010 with Excel 2003 (or maybe earlier) templates. However, I think that's misleading because it's only true if the WebFocus output is EXL2K, not EXL07, correct? Thus, I'd like someone to tell me if they think I've got this right, or wrong:

We have Excel 2010. If I write a focus report with EXL2K (.xls) as the output format, I can use a template (.mht) I previously created with Excel 2003. That works fine, but we get an annoying message about .xls not being the correct format for Excel 2010. Hitting [Yes] to that message opens the spreadsheet, but it's still annoying. When the .xls opens, the template works fine.

If we use EXL07 (.xlsx) as the WebFocus output, we don't get that annoying message, but the template doesn't work.

We'd like to use EXL07 (to avoid the annoying message) with Excel formatting macros. Does this mean our only choice is to use Vb Script (which replaces the Excel template)? If so, do you recommend any good websites for learning Vb Script?

Note: I've heard there's a registry hack to get rid of the annoying message of opening .xls with Excel 2010, but I don't think that's an option for all of our users.

This message has been edited. Last edited by: <Kathryn Henning>,

If you are able to upgrade to WebFOCUS 7.7.04, you can make use of macro-enabled templates for EXL07.

JR,

I've never quite gotten the hang of the different component names and version #s of WebFocus.

We have WF Client 7.7.03, and WebFocus Reporting Server 7.7.05.

When you refer to WebFocus 7.7.04, are you referring to one of the two I listed, above? If so, which one.

Do you know of a good place - specific book, manual, PDF - for documentation for the macro-enabled templates for EXL07?

Folkey,

There is no client version higher then 7.7.03 currently. 7.7.03 goes up to Hot Fix 7 I believe. Also you need to be on Hot Fix 5 or above if you have Reporting server 7.7.04. Since you have Reporting server 7.7.05, you are already at a version that allows macro enabled templates according to JR Lewis.

I haven't found much documentation on Excel templates with EXL07, but I did attend a Summit presentation on the topic last year.

So, I did some testing and was able to get it to work for me. Below is a snippet of code I came up with to populate three worksheets in an Excel template, and use Excel formulas to populate cells on a fourth worksheet.

-SET &ECHO = 'ALL';

APP HOLD JOHNTEST
-RUN
-*
SET EXCELSERVURL = ''
-*
TABLE FILE CAR
SUM DEALER_COST RETAIL_COST SALES
BY COUNTRY
ON TABLE HOLD AS HOLD1 FORMAT XLSX TEMPLATE 'test_exl07_template.xltm' SHEETNUMBER 1
END
-RUN

TABLE FILE CAR
SUM DEALER_COST RETAIL_COST SALES
BY CAR
ON TABLE HOLD AS HOLD2 FORMAT XLSX TEMPLATE 'HOLD1.xlsm' SHEETNUMBER 2
END
-RUN

TABLE FILE CAR
SUM DEALER_COST RETAIL_COST SALES
BY BODYTYPE
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'HOLD2.xlsm' SHEETNUMBER 3
END
-RUN

My copy of 'test_exl07_template.xltm' has 4 worksheets. Sheets 1-3 have only the value of 'empty' in cell A1 of each sheet. Sheet 4 includes some references to cells in other worksheets just to make sure I could get that to work.

There is some server-side configuration that needs to be done as well. Java Services needs to be established, and a JAVA_HOME environment variable needs set up. I am not so familiar with these items - IBI can provide some assistance with these steps if you want to proceed.

Thanks for the info. I know we have a hotfix applied to our WF Client, but I don't remember the number. It says "branch7703_hotfix" on the WF Admin Console page under WF Info.

After today, I may not be able to work on this for another week - but I'll get back to you then. Keep the suggestions coming.

I am not able to get data on any of my reports since the upgrade for format EXL07, however EXL2K opens fine. Is there really a hotfix for this? We upgraded to 7.7.3

From what I understand, Excel 2010 has 3 types of template files, ending in .xlsm, .xltm and .xltx. Using the following lines in a focexec, either nothing happens (when format is EXL07) or I get a FOC3289 error when using format EXL2K:

ON TABLE PCHOLD FORMAT EXL07 TEMPLATE mark/LandscapeBoldHeadingTemplate.xlsm SHEETNUMBER 2 - nothing happens
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE mark/LandscapeBoldHeadingTemplate.xlsm SHEETNUMBER 2 - error foc3289

ON TABLE PCHOLD FORMAT EXL07 TEMPLATE mark/LandscapeBoldHeadingTemplate.xltm SHEETNUMBER 2 - nothing happens
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE mark/LandscapeBoldHeadingTemplate.xltm SHEETNUMBER 2 - error foc3289

ON TABLE PCHOLD FORMAT EXL2K TEMPLATE mark/LandscapeBoldHeadingTemplate.xltx SHEETNUMBER 2 - error foc3289

However, when I use:

ON TABLE PCHOLD FORMAT EXL07 TEMPLATE mark/LandscapeBoldHeadingTemplate.xltx SHEETNUMBER 2

I get my output in worksheet # 2, but the formatting I originally had in the template (big fonts and colors) are no longer in sheet # 2. In the template, I also put Excel formulas in sheet # 1, and the non-formatted data in sheet # 2 was copied, and formatted in sheet # 1. However, I can't create a universal template (applicable to all of my reports) which will copy all the data in sheet # 2 to sheet # 1 (& then delete sheet # 2). I don't remember, but either VBA (to copy and delete sheet # 2) isn't allowed in .xltx, or if it is allowed, WebFocus will ignore the VBA.

I haven't looked into the JAVA_HOME environment variable yet. All of my Excel templates have at least one non-empty cell.

As stated in my first post, we can use EXL2K with an Excel 2003 template, but that results in an annoying message about the wrong format. Not sure if that's what's meant when Tracie says EXL2K opens fine.

It's interesting that JR uses a format of XLSX instead of EXL07. I didn't notice that in the manual. I get the same results whether I use XLSX or EXL07.

Here is the technical memo for 7.7.03. This is what I used to setup my environments. I used the JSCOM3 approach on the reporting server on 7.7.03 and 8002M


EXL07

Building the .xlsx Workbook File
Microsoft changed the format and structure of the Excel workbook in Excel 2007. The new
.xlsx file is a binary compilation of a group of xml files. Generating this new file format using

WebFOCUS is a two-step process that consists of generating the xml files containing the
report output and zipping the xml documents into the binary .xlsx format. The Reporting
Server performs the xml generation process. The zipping process can be completed either
by the client (WebFOCUS Servlet) or the server (JSCOM3):

WebFOCUS Servlet. -- The WebFOCUS Client within the application server performs the
zipping process. This can be done within the local client or through a remotely accessed
client. The servlet method is the default approach defined for each WebFOCUS client,
with the client by default pointing to itself.

JSCOM3. -- The Java layer of the Reporting Server performs the zipping operation. This
option should be used when the WebFOCUS Servlet is configured on a secured Web or
application server. This is because JSCOM3 does not require URL access to a remote
WebFOCUS Servlet.


In order to get the Java Services started on the WFRS



I needed to setup the JAVA_HOME variable on my server





In each procedure that I am going to put out as EXL07 I add this

SET EXCELSERVURL = ''


Now between 7.7.03 and 8002M, I have noticed some differences in how templates behave so my assumption is there are differences between 7.7.03 and .04. I think some of the big differences is the ability for macros in templates in the later versions.

You may want to ask your admin, how your environment is setup. Maybe this option will work for you.

Hope this helps.

I believe the XLSX and EXL07 are interchangeable. I have used both successfully.

If you want formatting and formulas to be retained, you will need the .xltm format (Excel template with macros).

The same applies to using VBA in your template.

IBI's tech support site has information that may be helpful in resolving the issue.

quote:

I get my output in worksheet # 2, but the formatting I originally had in the template (big fonts and colors) are no longer in sheet # 2. In the template, I also put Excel formulas in sheet # 1, and the non-formatted data in sheet # 2 was copied, and formatted in sheet # 1. However, I can't create a universal template (applicable to all of my reports) which will copy all the data in sheet # 2 to sheet # 1 (& then delete sheet # 2). I don't remember, but either VBA (to copy and delete sheet # 2) isn't allowed in .xltx, or if it is allowed, WebFocus will ignore the VBA.

Let me preface this with I'm still on 7.6.11. Which means I have to build my excel templates with Excel 2003 because Excel 2007 & 2010 are not supported in 7.6.11. The user community uses Excel 2010.

With that said, I have never been able to get excel template file (.mht files in 2003) to save any formats. The reason for this is the report overwrites everything on the worksheet. I do as much formating in the .fex as I can and use an auto open macro in the templace to do the rest. I've never had any trouble doing any excel specific formatting (i.e. inserting forumulas, copying data from 1 sheet to another, deleting a sheet, inserting a new sheet, etc.) with method.