Hello,

I am having issue with When creating Compound Excel reports (multiple TABLE request) output to same excel but different worksheets.

Created first excel template, also has macro in it.
First table request I want to populate data using first table request.

Then having a loop to go through it and populate worksheets.

Had created excel template and save as .mht file and kept it under same application folder where data and report is.

Do you know where I am doing wrong or any suggestions will appreciated.

Thanks,

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

Addition to above,

I am getting Foc3289 error opening template file.

what will be reason it can not open the template file. while I can open template(.mht) file in excel.

Thanks,

Viral,

Your template file MUST contain at least two work sheets.
AND each sheet MUST have some content, even if it is a space in cell "A1".

In addition if you want more sheets in the final output then either those sheets must exist
or they must be created by your macro.

Thank you JG.

I will try with some contents in the each worksheets.

Since I am trying with compound report.
I have two templates.
For the final output need to have macro and all those worksheets.
I will create template (temp2) where each worksheets and last worksheet with macro.
No now i have two templates, first for populate summary worksheet tab using first template (temp1)should create same number of worksheets as final output as created in temp2 template.

Thanks,

Viral,

You can not use two seperate template file.

Unfortunately the way it works is quite simple.

The out put of your first report using format template MUST be the template for the second report
which must be the template for the third reprt etc. etc.

This is because everything must be in a single output document.

Your basic process is as follows.

FILEDEF TEMPLATE2 DISK TEMPLATE2.MHT
-RUN
TABLE FILE whatever
.
.
.
ON TABLE HOLD AS TEMPLATE2 FORMAT EXL2K TEMPLATE 'TEMPLATE1' SHEETNUMBER 1
END
-RUN
FILEDEF TEMPLATE3 DISK TEMPLATE3.MHT
-RUN
TABLE FILE whatever
.
.
.
ON TABLE HOLD AS TEMPLATE3 FORMAT EXL2K TEMPLATE 'TEMPLATE2' SHEETNUMBER 2
END
-RUN
FILEDEF TEMPLATE4 DISK TEMPLATE4.MHT
-RUN
TABLE FILE whatever
.
.
.
ON TABLE HOLD AS TEMPLATE4 FORMAT EXL2K TEMPLATE 'TEMPLATE3' SHEETNUMBER 3
END
-RUN
TABLE FILE whatever
.
.
.
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'TEMPLATE4' SHEETNUMBER 4
END
-RUN

You can not create a target worksheet on the fly using a macro,
You can however create a new worksheet and move content from one of your WebFOCUS populated sheets to it.

You need a sheet available in the original template for each step that you run.

If as in the above sudo code you want to populate data to 4 sheets at least 4 sheets must exist.

Targeting the same SHEETNUMBER will over write anything that was previously written.

(It is actually better in some cases to have double the number of Worksheets available and move the contents
to the final destination sheet and hide the WebFOCUS target sheet because WebFOCUS messes with the internal
sheet indexes meaning if you require to create sheet specific subs in your macro (example a print button)
you can not because the sheet will not exist in your original template.
You would then use the macro to hide the extra sheets)

Ideally call your main macro Workbook_Open() and start it using something like this example

Sub Workbook_Open()
' Check the macro name to make sure the macro does not run if you open the template
File_Name = ActiveWorkbook.Name
SheetName = ActiveWorkbook.Sheets(1).Name
If File = "template1.mht" Then
' Skip Macro
Else
' this will prevent the macro running a second time if the workbook has been saved
' providing your macro renames the worksheets
If SheetName <> "Sheet1" Then
' Skip Macro
Else
.
.
' The body of your macro content goes here
.
.
End if
End if
End Sub

Hi JG,

Thank you for your feedback.

But I am confused because what I understood that need different template file.

Since now you suggesting me only one template file.
I had created template file with 41 worksheets which was expected output and last one has macro call for user.

Can we populate data of CAR file using one template file using
First worksheet is summary of all car sales.
Then it will have each country wise car sales in each worksheet.
Can you give me sudo code.

Once again thank you for your suggestions.

Thanks,

Viral, the above example that I posted is all the sudo code that you need.

If you follow it you will see exactly how it works.

Your 1st template file is the one that contains the required number of worksheets
and the macro code that you need.

As I illustrated each subsequent request uses the previous output as it's template.
That is why you must FILEDEF the output with an extension of .mht

Give it a try using the code basis I posted and you should be able to get it to work for you.

Thanks JG,

We tried using your example and it started working.
Only piece of my template now to add macro and see it working.

Once again thank you for your inputs.

Hello,

I am using excel template and it works fine.
Now added Formula to the excel template on column has total of few other columns - rows.

Like
A B C D
100 100 100 =sum(A1:C1) This cell value =300


Each looks fine in the template. But when I run the report and data loads to which is zero.
So when user does entry to A,B and C it still doesn't calculates the D value and remains zero.

What can be done to achieve in the report or template.

Thanks in advance.

Are you writing over the sheet with the macro? If your macro is in sheet1 and you save a report to sheetnumber1 you have written over your macro. Your macro should be something like
=SUM(Sheet2!A1:Sheet2!C1)

Pat,

Macros are not stored in the worksheet and so can not be over written in that way.

However any contents in a template (such as a formula) contained on the target sheet will be over written.

The correct way to do it is to have a macro that inserts any formulas into the required cells
after the sheet has been opened in Excel.

If your sheet format is static (i.e always 50 rows X 25 columns etc.) then it's easy as you
know the cell reference that you need to insert into.

However if the content is variable due to varying dimensions on BY and ACROSS sorts SUBFOOTS etc.
in WebFOCUS then it becomes a bit more complicated as the macro has to find the correct location
to insert the formula.

It will require someone with reasonable VBA skill to set that up.

**** All,

I tried put macro for getting sum for three and it works fine.

But As JG explain I have 3 subtotal on 3 by field.
template removes the formula, if report use excel formula instead of using template then it subtotal is fine.
Any solution to have subtotal working since it is more pain for user side since now they have to do it manually.

Any idea or suggestions are welcome.

Thanks,

Viral,

As I said above, in your case you need a person with VBA experience to create your macro code.

A relatively simple find and replace operation is all that is needed, but it will need someone with some experience.