Since we can't use macros (in 2007) to copy data from the sheet that has the WebFOCUS data to the sheet that has the preset formatting and styling etc., Is there any other way to get the data in the sheet - some sort of referencing syntax that can say this sheet (which let's say contains my Excel FOOTER) references all the data on another sheet?

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

If your aim is to purely apply the data into the pre-styled sheet then turn it around. Load the data and then use macros to style the sheet. The scope you have with VBA is immense so just get your head around it and don't be too blinkered in your analysis.

T

The reason I was trying to get away from VBA is because of the Excel 2007 problem - to recap:

WebFOCUS requires you to save your templates in .mht files. Microsoft stopped supporting VBA in .mht files as of 2007. If your organization has moved over to 2007 you're out of luck...

quote:

you're out of luck...

Says it all really if you can't get an earlier format

Of course, if you knew of a consultant who could provide you with your requirement .......

T

Yeah - we could always find someone with Excel 2003 (myself included) to give us the template but something tells me that would NOT be a good idea from a support/maintenance perspective...

I've just tested creating an .mht file from within Excel 2007 perfectly OK. I've also looked at it via an editor and it looks OK and seems to have the necessary headers but have not tested it from WebFOCUS.

Have you actually tried it?

T

Yes, I tried it - the problem is when you have VBA in it - Microsoft won't save the VBA.

What do I need the VBA - I would like to get an Excel Footer into my template. If I put it on the same sheet as where the WebFOCUS data goes, it gets wiped out. If I put it on a different sheet, I was using VBA to copy the data over but the VBA doesn't get saved now...

So this brings us back to my original post on this thread - is there any other way to get the data to the sheet with the footer (either by some reference or somehow have the Footer on the same sheet where WebFOCUS dumps the data and not have the Footer wiped out)?

do it the hard way, look at this article.

http://www.informationbuilders.com/support/developers/macros.html

When you say "Excel Footer", do you mean one assigned during the print setup? If so then you either have to do it as per J.G.'s article mentioned by him above or add it via a WebFOCUS footing.

T

We make extensive use of VBA in Excel templates for our reporting, so I have a strong interest in finding a solution to this problem before our company upgrades to Office 2007. I've been doing some research.

I came across something called Excel Named Ranges, and I'm wondering if this would work in place of copying data from the data worksheet to the formatted worksheet. Take a look and see what you think - it's in the Dev Studio help index under Excel Named Ranges. Apparently a named range in an Excel template will expand to hold whatever volume of data is shipped to it.

I was thinking of doing something along those lines but our report widths and lengths are variable. Would that approach still work?

Judging only from the Help file info (which is sometimes not precisely true) it sounds like Excel Named Ranges would accommodate variable-row and variable-column layouts. We haven't actually tested this yet - I was talking this over with a colleague late yesterday afternoon, and we may be trying it soon. One thing that concerns me is that the help file says you can't use this method with subtotals - that could be a vexing limitation in some cases.

Thanks.

If it's true that it doesn't work with subtotals then I'd say this is not an option for us.

Hi Tony A:

quote:

I've just tested creating an .mht file from within Excel 2007 perfectly OK

Did you try putting a formula; such as in a cell on sheet1 referring to another cell on sheet2? I tried to save the mht file. Excel 07 would not save the formula.

Mike,

I must admit that I did not try that particular scenario. My main requirements for Excel templates is the VBA ability and, because WebFOCUS overwrites all contents including formulae, I would not normally code formulae direct but would use VBA in the workbook_open sub.

T

I have just tested an .MHT file saved from within Excel 2007.

Upon saving the file you get the ... some features are not compatible etc. etc. ... but the file appears to save OK. If you edit the file using a text editor then the layout looks perfectly OK from the point of view of headers.

However, if you open the file from within Excel, the VBA code is no longer in existance (I guess that includes any formulae etc. as well!). A BIG problem for the future and one that reduces the functionality and useability of Excel templates from within WebFOCUS. I hope IB will issue recommendations other than "stick with Office 2003" which, corporatly, is not an option. I guess that "some features" actually means "most if not all features"!

I suppose J.G's article method might be possible - a bit retrograde though!.

I'm not sure if Cognos can utilise Excel to the same extent as WebFOCUS, but if it can then this could sign a warrant for WF at some of my clients as one, in particular, has around 1000 multi sheet Excel files produced per day - each one is internally formatted using VBA code as WF cannot achieve the page settings, borders etc.

Finally, yes, I know that the problem is caused by MicroShaft shifting their policy with Excel 2007 (who needs backward compatability anyway ) but I am sure that there are many IB Customers out there, like my Client, who utilise this functionality and will be left up the provebial creek without so much as a paddle if we cannot find a way forward!

Because I am a Contractor, I will maintain one of my own machines with Orifice 2003 so that I can still create template files - always open to consultancy requests for piece work

Horrified from England!

It's a comforting feeling to know there are others sharing in my pain...

quote:

others sharing in my pain...

but are you actively seeking a solution or waiting on others?

Me, I've just taken the VBA out of the Excel workbooks and created the necessary bits in VBScript. Of course, it helps if you can code your requirements in VBScript .....

T

Very much so! If I come up with anything, or if IBI provides anything, I will let you all know. I had put the question up to see if others had already come up with decent workarounds.

quote:

Very much so! If I come up with anything, or if IBI provides anything, I will let you all know. I had put the question up to see if others had already come up with decent workarounds.

what about the VBScript solution Tony A offered? if it was me i would have started thinking about trying to do it in VB as the languages are so similar but VBScript is even better suited to it...

I guess I didn't think about it because I am not familiar with it. I will research and see if this will work.

Would the VBScript go in the .mht file?

quote:

Would the VBScript go in the .mht file?

I'd do your research first and then answer this question yourself.

I now have a working alternative and I can assure you that it does the same job as the internal (clue!) VBA code. Only four more template files to replicate now

T