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     [CLOSED] Excel template issue in WF8.1.04

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Excel template issue in WF8.1.04
 Login/Join
 
Gold member
posted
Hi,
I have following code and output for that.
I am not sure why it is failing to open the template while fetching data for second tab.


Please suggest..


Code:
FILEDEF TEMP1 DISK TEMP1.xltm
TABLE FILE CAR
PRINT
CAR
ON TABLE HOLD AS TEMP1 FORMAT EXL07 TEMPLATE 'test.xltm' SHEETNUMBER 1
ON TABLE SET BYDISPLAY ON
END

FILEDEF TEMP2 DISK TEMP2.xlsm
TABLE FILE CAR
PRINT
COUNTRY
ON TABLE PCHOLD AS TEMP2 FORMAT EXL07 TEMPLATE 'TEMP1.xlsm' SHEETNUMBER 2
END

OUTPUT:
0 NUMBER OF RECORDS IN TABLE= 10 LINES= 10
0 XLSM FILE SAVED ...
0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5
(FOC3289) TEMPLATE FILE: Error opening file
(FOC3317) Error processing template file.

Regards,
Vivek

This message has been edited. Last edited by: <Kathryn Henning>,


WF 8.1.04,Infoassist,Oracle, Excel, PDF,HTML.
 
Posts: 82 | Registered: January 06, 2014Report This Post
Expert
posted Hide Post
Vivek,

Remove your FILEDEFs, they are not required link to old post.

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
Gold member
posted Hide Post
Thanks Tony for the reply.
I have removed the FILEDEFs but this did not solve the problem.
Where as I am able to resolve the issue by changing the name of hold file. It seems some hold file with name TEMP2 already exists in the session.

Please suggest, how can I clear the existing hold file before creating the new one with same name.

Regards,
Vivek


WF 8.1.04,Infoassist,Oracle, Excel, PDF,HTML.
 
Posts: 82 | Registered: January 06, 2014Report This Post
Expert
posted Hide Post
Check out the documentation for FILEDEF and the CLEAR option.

Alternatively, use a different temporary filename!

Also look at the APP DELETEF syntax to see if that would help you.

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
Virtuoso
posted Hide Post
I'm fairly certain that the extension for the EXL07 hold file is '.xlsx' instead of '.xlsm'.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
quote:
Originally posted by Wep5622:
I'm fairly certain that the extension for the EXL07 hold file is '.xlsx' instead of '.xlsm'.


Depends. If your template contains a macro, the format is .xlsm. The 'm' is for macro enabled. .xlsx files are macro free files.

jvb, you don't say what OS your Reporting Server is running. If it is Unix/Linux, then the filenames are case sensitive. We are Linux and we issue a SET FILECASE=LOWER in the edasprof, forcing all filenames to be lower case.

I suggest that after the first table request insert a -RUN and the issue the appropriate command for your Reporting Server OS and list the contents of the working directory and see what the filename is for TEMP1.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Virtuoso
posted Hide Post
Good point, I forgot about that. Even though we have this in some of our procedures:
-SET &EXTENSION = DECODE &EXTENSION('.xltm' '.xlsm' ELSE '.xlsx');
-SET &TEMPLATE = 'UNI00025H1'|&EXTENSION;


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Guru
posted Hide Post
How to create the macro file(.xltm/.xlsm) in MS Office 2013?

Steps I followed:
============
1.Create a new excel
2.Navigate to View->Macros->Record Macro and stop recording
3.Alt+F11, could see the VB macro code in Modules->Module1
4.File->Save, as a xltm format
5.Close
6.Open the excel, even after clicking the 'enable content' doesn't execute macro unless go to macro editor & press F5.

Please help me, where I'm going wrong?


-Rifaz

WebFOCUS 7.7.x and 8.x
 
Posts: 406 | Location: India | Registered: June 13, 2013Report This Post
Master
posted Hide Post
Have you enabled macros in the Trust Center?


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Guru
posted Hide Post
Yes...Chose, File-->Options-->Trust Center-->Macro Settings->Enable all macros(not recommended...)

Now, coming to my original problem,

Created a xltm file and place it in baseapp.I have below macro code in 'Module1'

 Sub Macro1()
'
' Macro1 Macro

    Range("A1:E10").Select
    With Selection.Font
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.499984740745262
    End With
End Sub 


 TABLE FILE CAR
  SUM RCOST
      DCOST
   BY COUNTRY
   BY CAR
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'car-test.xltm' SHEETNUMBER 1
END
-RUN 


It prompts me to open in 'xxx.xlsm' format(as documented). Only at the very first time, it prompts for "Enable Content" option and it doesn't execute macro after clicking to it. From then, never get "Enable Content" prompt and doesn't execute automatically either.


-Rifaz

WebFOCUS 7.7.x and 8.x
 
Posts: 406 | Location: India | Registered: June 13, 2013Report This Post
Silver Member
posted Hide Post
I believe with Office 2007 on up, to have an Excel Template with MAcros automatically execute any Macros, the Macros have to be in the Auto_Open Routine.
The downside to this however, also means that after they save the Template to something else and open it again at a later date the macro will re-run.


WF 81.5, Windows7
AS/400 Database.
All Outputs

 
Posts: 46 | Registered: November 26, 2008Report This Post
Guru
posted Hide Post
Hi Rogerwilkouk,

When you have time, kindly give me the detail steps to run macros. You can PM me as well.


-Rifaz

WebFOCUS 7.7.x and 8.x
 
Posts: 406 | Location: India | Registered: June 13, 2013Report This Post
Silver Member
posted Hide Post
Run Macro

This great document details running of Macros and at the bottom are the details of how to get them to run automatically when opening the workbook (Should work the same with Templates)


WF 81.5, Windows7
AS/400 Database.
All Outputs

 
Posts: 46 | Registered: November 26, 2008Report This Post
Master
posted Hide Post
I have several base templates that I use because many of our workbooks have multiple sheets but this is the one I use the most. I got the idea for this from a post by Tony A from back in 2008.

The way to keep a macro from running again is to do the following:
Sub Auto_Open()
' 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
  Application.PrintCommunication = False
  Sheets(1).Select
  If Sheets(1).Name = "Sheet1" Then
    Sheets(1).Select
    Sheets(1).Name = "Sheet 1"
    Call format_ws1
    Sheets(2).Select
    Sheets(2).Name = "Sheet 2"
    Call format_ws2
    ...
  End If
  Sheets(1).Select
  Application.PrintCommunication = True
' Turn screen updating on again
  Application.ScreenUpdating = True
End Sub
Sub format_ws1()
' Put formatting for Sheet 1 here
End Sub
Sub format_ws2()
' Put formatting for Sheet 2 here
End Sub


Since Sheet1 has been renamed if the user saves the workbook, the macro will not run the next time they open the workbook. With Excel 2007 and higher, tell users to save the sheet as a .xlsx and it will strip the macro from the saved workbook.

As for getting the macro to run automatically, if the Auto_Open macro name does not do the trick, you may have to change some additional Trust Center setting.

  • Trying add the client server name to Trusted Locations.
  • Under Trusted Documents, check "Allow document on a network to be trusted".
  • If coming as an email attachment and you are using Outlook, under Protected View make sure to uncheck "Enable Protected View for Outlook attachments".
  • Under File Block setting, makes sure "Excel 2007 and later Macro-Enabled Workbooks and Templates" is not checked.

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


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Guru
posted Hide Post
Many Thanks rogerwilouk and jgelona for your suggestions, I got Macro to work from Excel using Workbook_Open method. However, it doesn't help when I run it from WebFOCUS though I placed the macro in the app path, keeps throwing "run time error 1004 select method of range class failed". After a long run in Google, I figured out, shouldn't have "select" statements in my macro. Removing .select method from macro works as expected.

Private Sub Workbook_Open()
'ActiveWorkbook.Sheets("Sheet1").Select
With Worksheets("Sheet1").Range("A1:D20")
With .Font
.Name = "Arial"
.Color = vbRed
End With
End With
End Sub
  

Sweating


-Rifaz

WebFOCUS 7.7.x and 8.x
 
Posts: 406 | Location: India | Registered: June 13, 2013Report 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 issue in WF8.1.04

Copyright © 1996-2020 Information Builders