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     [CASE-OPENED] Excel (2010) macro not getting executed in WebFOCUS 7.7.03

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CASE-OPENED] Excel (2010) macro not getting executed in WebFOCUS 7.7.03
 Login/Join
 
Member
posted
Hi there,
Requirement: I need to execute a macro with an on open event from WebFOCUS with help of .mht format.
Ground Work: I have opened excel 2010 version -created an On Open event to colour the whole sheet 1 to yellow. This is working fine when I open the XLS from desktop.
I did save as trail.mht and moved the same on to the WF app path.
when I used ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'trail' SHEETNUMBER 1, the data is getting moved to the pointed sheet but the macro is not getting executed.
Is there some thing that I am missing on.
Please assist. Thanks.

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


WebFOCUS 7.7.05 and 8.x
Windows, All Outputs
 
Posts: 27 | Registered: December 13, 2012Report This Post
Virtuoso
posted Hide Post
This article may help you.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
Hi,
Thanks for the response. I am able to write an on open event excel macro (2007) and was able to execute it successfully on my pc.
Macro: It will change the sheet1 color to yellow when opened.

I tried creating a .mht file (named it as trail) and dumped that file to other folder in WebFOCUS.
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'trail' SHEETNUMBER 1
When I execute this the trail sheet is being called and the data is moved onto sheetnumber 1. However the colour of the sheet remains white, which is supposed to be yellow.
Might I am missing on some thing. Please assist.
If 2007 version excel version doesn't support, will it work fine if I do the same exercise in 2003 version.
Please advice and assist.
With Regards;
Rao


WebFOCUS 7.7.05 and 8.x
Windows, All Outputs
 
Posts: 27 | Registered: December 13, 2012Report This Post
Member
posted Hide Post
Put this in the This_Workbook module in your mht file.

Sub Workbook_Open()

Your_Macro_Function_name

End Sub


WebFOCUS 7.6.1
Windows, All Outputs
 
Posts: 1 | Registered: August 24, 2012Report This Post
Member
posted Hide Post
Please correct me. I did the same on the Sub Workbook_Open() in excel in the VBA under This workbook module.
Example:
Sub Workbook_Open()
MsgBox "Good Morning"
End Sub

Is there a way to do it in the .mht file as well. I doubt.
Please guide me.


WebFOCUS 7.7.05 and 8.x
Windows, All Outputs
 
Posts: 27 | Registered: December 13, 2012Report This Post
<Kathryn Henning>
posted
Hi Prabhakar,

Please open a case on InfoResponse Online so we can work with you on this issue.

Thanks and regards,

Kathryn
 
Report This Post
Expert
posted Hide Post
quote:
I did save as trail.mht

But did the macro work when you opened this file in MS Excel?

The reason that I ask this is MS Excel 2010 very kindly removes macros when saving as a file anything other than xlsx (allegedly).

Therefore if you were to open your MHT file in MS Excel you will, likely as not, find that you have no macros contained within the file.

Hence no macro running when you get your output from WebFOCUS.

The solution is to create the MHT file using MS Excel 2003 and not the 2007 or 2010 versions.

This is a microsoft limitation and has been noted on many other posts regarding Excel template use.

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
Hi,
quote:
The reason that I ask this is MS Excel 2010 very kindly removes macros when saving as a file anything other than xlsx (allegedly).


It's got even a bit harder with Excel 2010. xlsx files will not work with Macros. You have to use xlsm when saving Excel.
@Prabhakar: I read that you have 7.7.03. (Same for me btw.):
unlucky us: Excel 2007/2010 Macros with xlsm / xltm files is only available (again) with WF 7.7.04 onwards.

You may then use something like

 
TABLE FILE CAR
PRINT CAR
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'test_templ1.xltm' SHEETNUMBER 1
END 


where 'test_templ1.xltm' is your Excel 2007/2010 template file - macro included.

But as I said - an upgrade to 7.7.04 is required to get it working.
Otherwise you need to stick to 2003 generating an MHT file (still including a macro) for 7.7.03 and below.

Forgive me: but FOCUS only took several years (from Excel 2007 / Nov 2006 until WF 7.7.04 / Apr 2012 came out) to get it up and running again. Almost 6 years is close to nothing

Cheers Linne


WebFOCUS 7.7.03
 
Posts: 67 | Registered: January 05, 2011Report This Post
Expert
posted Hide Post
quote:
You have to use xlsm when saving Excel

Linne,

Thanks for correcting me Smiler I should have re-read my post before commiting Frowner

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     [CASE-OPENED] Excel (2010) macro not getting executed in WebFOCUS 7.7.03

Copyright © 1996-2020 Information Builders