[CLOSED] Excel template with Images and VB Controls
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,
April 06, 2008, 05:54 PM
Waz
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.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
April 07, 2008, 09:22 AM
TexasStingray
FYI, I think Microsoft is doing away with a supported feature that will prohibit the use of templates in the upcomming year or so.
Scott
April 07, 2008, 09:45 AM
Lusheng
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
April 07, 2008, 12:20 PM
walker
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
April 07, 2008, 12:36 PM
Tony A
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
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
April 07, 2008, 05:55 PM
Waz
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.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
April 08, 2008, 02:30 AM
Tony A
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.
TThis message has been edited. Last edited by: Tony A,
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
April 08, 2008, 09:42 AM
TexasStingray
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.
Scott
February 10, 2012, 09:44 AM
wdusbank
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.
WF 8.2.01M, MF MVS 7.6.5
January 19, 2017, 03:11 PM
tomatosauce
@wdusbank
Could you post the macro code for copying the contents of sheet1 to formattedtab and then hiding the sheet1?