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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     What features saved in an Excel Template will apply to wf output

Read-Only Read-Only Topic
Go
Search
Notify
Tools
What features saved in an Excel Template will apply to wf output
 Login/Join
 
hammo1j
Master
posted
Hi

I am doing a simple wf template and have followed the rules in that it is has 2 worksheets and I want to put freeze panes from the template.

ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'r1290x' SHEETNUMBER 1

I store the freeze panes in the template and also do some formatting on one of the columns and have changed the name of the worksheet.

When I run the only thing picked up is the rename of the worksheet tab.

Are there certain things you cant do in templates that will survive the loading with wf data. Is a way round this to create a series of ON LOAD excel macros in the template so that the formatting is done when the spreadsheet is rendered in the browser.

Does using the named ranges stylesheet output help in that it can be used to avoid obliterating cells that contain vital info?

Thanks in advance as usual...


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
hammo1j
Master
posted Hide Post
It seems one way is to have your unformatted data in sheet1 and the formatted in sheet2 as a series of references to sheet1.

If you need to get rid of the unformatted sheet you would need a macro but this would be very simple and the same each time.

However I have just hit a snag - created my formatted sheet and because my unformatted sheet was of indeterminate size did

Sheet1!A1 = Sheet2!A1

and then cntrl A to select all in the formatted sheet and it killed my machine.

So it seems that this kind of methodology is only suited to situations where there is a maximum known size of webfocus unformatted output.

Where there are no limits you have to rely on macros.

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


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
hammo1j
Master
posted Hide Post
aRRRRGH

Now I have hit all sorts of anti macro running software in Excel to make sure a virus is not running when you extend the workbook_open functionality!


Sub freezefilter()
'
' freezefilter Macro
' Macro recorded 02/04/2008 by John.Hammond
'

'
    Range("B2").Select
    ActiveWindow.FreezePanes = True
    Selection.AutoFilter
End Sub

Private Sub Workbook_Open()

freezefilter()

End Sub


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
I know this won't help at all, but I was a FUSE yesterday and it was mentioned that a lot of people have been asking to have the WebFOCUS data worksheet hidden in the Excel template, so, apparently, it is being worked on.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
hammo1j
Master
posted Hide Post
Cheers Francis

Here's some stuff on security in excel

http://msdn2.microsoft.com/en-us/library/aa203729(office.11).aspx

Use tools-> macros -> security to change level in excel.

The best guess seems to be to set it to medium while using wf and enable macros. Does anyone go through the process of signing their macros?

My worry is that if users have medium security they might inadvertently let something malicious through.

Interested to hear your thoughts.

Regards

JOhn

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


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
Tony A
Expert
posted Hide Post
John,

Don't forget that the Workbook_Open macro should be within the Workbook and not the Macro.

For everyone, here are some macros that I've tended to use over time for templates as a base. It also has the delete code (commented out) for when you only really want one worksheet. I first do a check so as to only run the code when it's not the web archive file (can't use xlWebArchive because .xht is also treated as one).

Private Sub Workbook_Open()
  If Right(ThisWorkbook.Name, 3) <> "mht" Then
    Call format_ws
  End If
End Sub
Public Sub format_ws()
  For Each ws In Worksheets
    With ws
      If ws.Name = "Sheet2" Then
' You can use this section to delete surplus worksheets
'        Application.DisplayAlerts = False
'        ws.Delete
'        Application.DisplayAlerts = True
      Else
' This will turn of screen updating so that the changes do not flash
' on the screen at the end user. No epileptic fits thank you!
        Application.ScreenUpdating = False
' Find the extents of the report
        ws_last_col = Cells.SpecialCells(xlLastCell).Column
        ws_last_row = Cells.SpecialCells(xlLastCell).Row
' More formatting code goes here
' Turn screen updating on again
        Application.ScreenUpdating = True
      End If
    End With
  Next
End Sub

Hope it helps

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
hammo1j
Master
posted Hide Post
Thank you Tony.

What you posted should be in the manual as a template for a template (joke) as pretty much all you have to do is record the macro and drop it into the above code.

IB documenters: take the above post and stick it in the manual!


Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report 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     What features saved in an Excel Template will apply to wf output

Copyright © 1996-2020 Information Builders