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     [SOLVED] scheduling output to Excel with existing pivot tables

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] scheduling output to Excel with existing pivot tables
 Login/Join
 
developing
Silver Member
posted
Hello everyone!
I was wondering if anyone knows if it is possible to schedule a report to run and have the data output to one sheet in an Excel where the pivot tables are already created and formatted in another sheet. The report would be scheduled to output to the Excel sheet once a day and the Excel file would have VB which refreshes the pivot tables when the file is opened.

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


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
jbanas6
Gold member
posted Hide Post
you should build a query call in excel to call your focexec. Then in excel perform the following below as a workbook open event:

  
Private Sub Workbook_Open()
    Call RefreshAllData
End Sub

Public Sub RefreshAllData()
    Dim x As Integer
    Dim qt
    
    For x = 1 To ActiveWorkbook.Worksheets.Count
        For Each qt In ActiveWorkbook.Worksheets(x).QueryTables
            qt.Refresh
        Next
    Next x
    
    For x = 1 To ActiveWorkbook.PivotCaches.Count
        ActiveWorkbook.PivotCaches(x).Refresh
    Next x

End Sub


I would also look into excel templates


WF 7.6.10 /IIS 6/ JBoss Enterprise 4.3
Windows XP SP 2/Windows 2003 Server
MVS 7.3.3
 
Posts: 76 | Location: Hartford, CT | Registered: August 30, 2005Report This Post
developing
Silver Member
posted Hide Post
Unfortunately, I can't use a webquery for this situation because the fex takes long to run so I'd like to schedule it to run once a day. Should I use an Excel template to accomplish this? If so, is it possible to schedule the data to output to an Excel template and have the pivot tables (which are already created in the template) refresh when the file is opened?


WebFOCUS 7.6.10, Windows Vista, Oracle, Output-Excel/PDF/HTML
 
Posts: 39 | Registered: September 20, 2007Report This Post
<JG>
posted
I would use a macro in the template file to create the Pivot table.

Populate the data using EXL2K TEMPLATE into a second worksheet and
have the macro create the Pivot table from that.

If you name the macro as Workbook_Open or Auto_Open then it will
automatically populate but you need to ensure that you arrange for
the macro just to run once or it will cause problems if a user saves it
and tries to re-open.
 
Report This Post
Tony A
Expert
posted Hide Post
I have many outputs fed from Report Caster processes that provide the data for pukka Excel Pivot tables (not FORMAT HOLD EXL2K PIVOT) but due to the varying number of rows of output I build the pivot tables afresh from within a macro and would recommend this approach (as J.G. suggests above). If your pivot table sources are always the same number of rows and columns then you may get away with loading the sheet. I have a command button on the pivot table sheet that the end user can push at anytime to wipe out all changes and recover to the original pivot table (via deletion and recreating anew) - I also give them charts as well which is why I do not use EXL2K PIVOT output.

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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] scheduling output to Excel with existing pivot tables

Copyright © 1996-2020 Information Builders