Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Excel template with Images and VB Controls

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Excel template with Images and VB Controls
 Login/Join
 
Platinum Member
posted
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,
 
Posts: 118 | Location: Omaha, NE | Registered: June 12, 2003Report This Post
Expert
posted Hide Post
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.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
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

 
Posts: 865 | Registered: May 24, 2004Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 118 | Location: Omaha, NE | Registered: June 12, 2003Report This Post
Member
posted Hide Post
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
 
Posts: 17 | Registered: May 10, 2007Report This Post
Expert
posted Hide Post
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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
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.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
Despite the name Wink 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 Wink). 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,



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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Master
posted Hide Post
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

 
Posts: 865 | Registered: May 24, 2004Report This Post
Silver Member
posted Hide Post
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
 
Posts: 29 | Location: Milwaukee, WI | Registered: September 10, 2008Report This Post
Gold member
posted Hide Post
@wdusbank

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


Tharun Katanguru
SBOX- 8205 DEV/TEST/PROD : 8105 8205
Linux, All Outputs
 
Posts: 52 | Location: BOWL OF PASTA | Registered: October 13, 2016Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Excel template with Images and VB Controls

Copyright © 1996-2020 Information Builders