[CASE-OPENED] Excel (2010) macro not getting executed in WebFOCUS 7.7.03

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, 2012

j.gross

Virtuoso

posted

Hide Post

This article may help you.

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Show Post

Prabhakar

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, 2012

Ignored post by Prabhakar posted

Show Post

Ajith

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, 2012

Ignored post by Ajith posted

Show Post

Prabhakar

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, 2012

Ignored post by Prabhakar posted

Show Post

posted

Hi Prabhakar,

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

Thanks and regards,

Kathryn

Tony A

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, 2004

Ignored post by Tony A posted

Show Post

linnex

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, 2011

Ignored post by linnex posted

Show Post

Tony A

Expert

posted

Hide Post