Has any one used the excel template with embeded images and VB controls?

I inserted some images and VB controls in the excel and then saved it as a (.mht) template. When webfocus populated the data into the template, it wiped out the images and VB controls in all sheets. Is there any way to retain the images and VB controls?

Thanks.

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

I tried doing the same, but it worked.

Can you post the code, perhaps it is some thing to do with the stylesheet.

What format are you using, EXL2K, EXL2K FORMULA, EXL2K PIVOT ?

What if you change the sheet number you are writing to, it is is the first, change it to the last.

FYI, I think Microsoft is doing away with a supported feature that will prohibit the use of templates in the upcomming year or so.

Waz,

I did not code anything in the stylesheet yet, as you can see from the code below. I have static images in Visual Basic controls in the all sheets.

FILEDEF MYTEMPLATE DISK D:\TNT\MNT\PUSHTECK\MYTEMPLATE.MHT
FILEDEF HOLD1 DISK hold1.mht
FILEDEF HOLD2 DISK hold2.mht
TABLE FILE CAR
PRINT CAR MODEL BY COUNTRY
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD1 FORMAT EXL2K TEMPLATE 'MYTEMPLATE' SHEETNUMBER 1
END
TABLE FILE CAR
PRINT CAR MODEL BODYTYPE
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD2 FORMAT EXL2K TEMPLATE 'HOLD1' SHEETNUMBER 2
END
TABLE FILE CAR
PRINT MODEL BODYTYPE SEATS
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'HOLD2' SHEETNUMBER 3
END

quote:

FILEDEF MYTEMPLATE DISK D:\TNT\MNT\PUSHTECK\MYTEMPLATE.MHT
FILEDEF HOLD1 DISK hold1.mht
FILEDEF HOLD2 DISK hold2.mht
TABLE FILE CAR
PRINT CAR MODEL BY COUNTRY
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD1 FORMAT EXL2K TEMPLATE 'MYTEMPLATE' SHEETNUMBER 1
END
TABLE FILE CAR
PRINT CAR MODEL BODYTYPE
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD2 FORMAT EXL2K TEMPLATE 'HOLD1' SHEETNUMBER 2
END
TABLE FILE CAR
PRINT MODEL BODYTYPE SEATS
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'HOLD2' SHEETNUMBER 3
END

I am interesting in use a template and tried to run this example on my machine but have got an error:

(FOC3289) EXL2K TEMPLATE FILE: Error opening file
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND
(FOC3289) EXL2K TEMPLATE FILE: Error opening file
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND
(FOC3289) EXL2K TEMPLATE FILE: Error opening file
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

Any idea why?

Thanks

Because it can not find the original template file or it is in the wrong format.

Search the Forum (using link top right) for Excel Templates for information as to how to create and use them.

T

Lusheng, What sheet(s) are you images in.

As far as I am aware, if your image is in a sheet that you write to, it will be lost.

Despite the name I agree with "Waz", that is, if you are loading a worksheet that contains anything, it will be lost.

This is because the sheet is "virtually" recreated. Just open the VB editor and view the explorer bar. You will see that the original sheet names are still there and they have macros associated with them, (if they existed), but they cannot be run. The sheet that was originally the sheet that you loaded is now called something different.

For instance, I have a sample excel template with three sheets, they are named in order - Car Data; GG Data; and Empty Sheet. I run a sample fex that popultaes sheets 1 and 2 with data from CAR and GGSALES (yes, really ). Once the fex has run and I open the VB editor, sheets 1 and 2 now have no name association and the "originals" have become sheets 4 and 5. I can open sheet 1 macro but cannot run it (activex error).

Therefore, I make the following suggestions -

• Do NOT include images in the original template worksheets that you intend to populate with WF
• ONLY add VBA code to the "ThisWorkbook" (or Macro) section

If you have to include images then either add them within WF styling or place them using VBA code in the open subroutine or a called macro from within the open subroutine.

T

This message has been edited. Last edited by: Tony A,

There ia a new optional addon feature to WebFOCUS called Quick Data it allows you to start WebFOCUS Query inside of an excel spreed sheet and layout the worksheet. This might be something worth checking into.

I don't know, if this is still and open issue with you, but I found a way to use an Excel Template and include our company logo in either the Excel Header or Footer OR in the actual Excel Report itself.

You add an extra page to the template that contained the page formatting & image. Let's call that tab 'FORMATTEDTAB'. WebFOCUS is not to write to this page. Once the WebFOCUS runs using the tempalte it loads Sheet1. Now, in the excel macro, you copy the contents of Sheet1 to the FORMATTEDTAB, and then delete Sheet1. We also use this in compound templates.

@wdusbank

Could you post the macro code for copying the contents of sheet1 to formattedtab and then hiding the sheet1?