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

This article may help you.

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

Put this in the This_Workbook module in your mht file.

Sub Workbook_Open()

Your_Macro_Function_name

End Sub

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.

Hi Prabhakar,

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

Thanks and regards,

Kathryn

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

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

quote:

You have to use xlsm when saving Excel

Linne,

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

T