Has anyone successfully been able to create a compound request with an Excel Template? I have a procedure that that runs a few different report requests and needs to populate various components on an Excel template. Only one component appears to be updating when this procedure runs (although, if run independently, each step will return updated output).

I found this article on the tech support site:

http://techsupport.informationbuilders.com/sps/12012126.html

But I can't seem to get the technique to work. I encounter a problem with the first graph, if I rename the file in the hold statement (as shown in the example) it will open just an empty Excel file (if exiting the procedure at this point), or gives me a foc350 "error writing output file:" if I run it as the first request in a series. The final error after the last report request will be: "(FOC3289) EXL2K TEMPLATE FILE: Error opening file "

Any experience with this or input would be appreciated.

Regards,

Debbie

Deborah, it looks like no one has any ideas for you!

I am at v5.3.2 so I don't have Excel Templates.

Have you tried copying the example and running it exactly as is - if you still get the error, I would open a case with IBI - the example should work.

Good luck,

Hi Debbie,

Have this issue been resolved? Have you opened a case for this issue? Many thanks for Francis' help on this one.

I ran a quick search regarding the error message and came across the following topics which may be of interest:

EXL2K TEMPLATE:How to access MHT file located on MVS SSCTL?
http://techsupport.informationbuilders.com/sps/21462081.html

Hope this helps.

Cheers,

Kerry

Hi Debbie,
The trick here is to make sure you truly have a good template that has at least two sheets. A single worksheet will be saved in the standard HTML format regardless if you choose SAVE AS and try to make it a Web archive.
Edit the file with Notepad and verify the line of the file. A web archive file will say:
X-Document-Type: Workbook
Hope this helps.
Rich

quote:

Have this issue been resolved?

Wow, I'm sorry, I never saw this. I was able to resolve this. This is the info I posted in our company Focal Point Forum (as reference for anyone that may come up against this issue):


I'm replying to my own post. The technique that ended up working in this case is at the bottom of this post (the example is just an example and not my actual Focus code).

A couple of notes:
1) In my specific case, each graph required some SQL code and various hold files and some other Focus techniques. I ended up changing my code around to accomplish all of the parameter setting and data gathering for all three graph requests first (previously each was being created in it's own -INCLUDEd fex, driven by the main fex).
2) When creating multiple components with one Excel Template target (as in the example below) be careful with case. Excel Template seems to prefer lower case, Focus seems to prefer upper.

So, in other words, your filedef would look something like:

APP FI COMPXLSH1 DISK compxlsh1.mht

and your hold statement like:

ON TABLE HOLD AS COMPXLSH1 FORMAT EXL2K TEMPLATE 'compxls_single'



The full code example:

"-* File newfex.fex
-* Original .mht template file in this example is called COMPXLS_SINGLE.mht
-*APP HOLD SAI_TEMP
APP FI COMPXLSH1 DISK compxlsh1.mht
TABLE FILE CAR
HEADING
"Report 1: W Germany"
PRINT DCOST BY COUNTRY BY CAR
IF COUNTRY EQ 'W GERMANY'
-* Using HOLD instead of PCHOLD and referencing ORIGINAL template file (.mht).
-* Note that the HOLD AS name is FILEDEF'd or APP FI'd to a file with .mht
-* extension.
ON TABLE HOLD AS COMPXLSH1 FORMAT EXL2K TEMPLATE 'compxls_single' SHEETNUMBER 1
END
APP FI COMPXLSH2 DISK compxlsh2.mht
TABLE FILE CAR
HEADING
"Report 1: Italy"
PRINT DCOST BY COUNTRY BY CAR
IF COUNTRY EQ 'ITALY'
-* Using HOLD instead of PCHOLD and referencing ORIGINAL template file (.mht).
-* Note that the HOLD AS name is FILEDEF'd or APP FI'd to a file with .mht
-* extension.
ON TABLE HOLD AS COMPXLSH2 FORMAT EXL2K TEMPLATE 'compxlsh1' SHEETNUMBER 2
END
APP FI COMPXLSH3 DISK compxlsh3.mht
TABLE FILE CAR
HEADING
"Report 1: Italy"
PRINT DCOST BY COUNTRY BY CAR
IF COUNTRY EQ 'ITALY'
-* Using HOLD instead of PCHOLD and referencing ORIGINAL template file (.mht).
-* Note that the HOLD AS name is FILEDEF'd or APP FI'd to a file with .mht
-* extension.
ON TABLE HOLD AS COMPXLSH3 FORMAT EXL2K TEMPLATE 'compxlsh2' SHEETNUMBER 3
END
TABLE FILE CAR
HEADING
"Report 2: England"
PRINT RCOST BY COUNTRY BY CAR BY MODEL
IF COUNTRY EQ ENGLAND
-* Since this is the last request, PCHOLD is used instead of HOLD.
-* Note that the template filename is the HOLD file (.mht file) from prior
-* request.
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'compxlsh3' SHEETNUMBER 4
END "

Hi Deborah,

I was trying your example and it still shows the same error message "(FOC3289) EXL2K TEMPLATE FILE: Error opening file "


Do I have to do special setting in my reporting server to do this.?

Viral,

Have you created the original .mht file first?

T

Tony, I have tried the above code and am still getting the same error as Viral above. The Original file has been created with 4 worksheets with at least one space in one cell. What I'm I doing wrong.

I'm using MRE 7.1.6.

Have you created an .MHT file in Excel and stored it in baseapps? Then you save your reportby sheet number.
Also, you have to use PCHOLD in the final step to open the sheet.

Also, the .mht file has to contain at least 2 worksheets. There has to be at least 1 blank entered into one cell in both sheets. I have generated a compound report with 97 worksheets using this method.

Mike,

Even though you are running in MRE, you still have to place the .mht file into a folder that is searched within your EDA path - use APP SHOWPATH to see the current folders within the search (EDA or APP) path.

Also, when you create the file in MS Excel, make sure that you perform a "save as" to a "Web Archive (*.mht;*.mhtml)".

T