We have tried to set formatting and print properties in an excel .mht to be used as a template for our .fex output but the formatting is overwritten and the print properties seem to disappear, such as "fit to 1 page wide, 1 page tall" as well as the print areas we have selected.

Are we using excel templates properly? How else would we set print area's and page setup information for an excel spreadsheet output?

Thanks!

Jason,

WF uses the template to build the output sheet within it. Even though it has to contain two sheets, that is primarily because the required format needs to have it that way to ensure header info is correct for WF to recognise it as a "pukka" mht file.

When WF writes the data to the sheet selected, it actually creates a "new" sheet so and makes the target sheet unavailable to Eggshell. Just look in the VBA editor to see what I mean.

The best method of using Eggshell Templates is to set up VBA code to format the worksheets within the "workbook_open" sub. You can set everything from there easily so that when the end user opens the workbook your VBA code runs and sets the worksheets how you need them.

I go one step further and erase the "formatting" code once it's been run successfully so that subsequent "opens" do not get the normal MS security warning. It's easy enough to do but does require changes to both the workbook references and also the end users Eggshell macro settings.

If you put something like this in the "workbook_open" sub within "ThisWorkbook" in your template, you'll see what can be done.

    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&F &A &D"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "&P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.590551181102362)
        .RightMargin = Application.InchesToPoints(0.433070866141732)
        .TopMargin = Application.InchesToPoints(0.62992125984252)
        .BottomMargin = Application.InchesToPoints(0.708661417322835)
        .HeaderMargin = Application.InchesToPoints(0.31496062992126)
        .FooterMargin = Application.InchesToPoints(0.354330708661417)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
'        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 64
        .PrintErrors = xlPrintErrorsDisplayed
    End With

T

One other thing I would mention is that when WF creates the "Excel" output the actual format is not "normal" Excel format but rather an XML equivalent (as near as). The downside of this is that the file contains masses of white space which I always remove by forcing an auto save in "xlNormal" format which reduces the file size by around 66%.

' Now save the file as a "pukka" Excel workbook to reduce file size
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.FullName, FileFormat:=xlNormal
    Application.DisplayAlerts = True

T

That's a great idea Tony.
Is there any way around some of our users having macros disabled or believing the Microsoft notification when they open the .xls that "This may be a virus?"

I don't think we could go through and set all their security to "Low" in excel, and even if we could I don't think it would be a good idea.

Thanks!

i think Tony A's suggestion about removing code after its used is what you need. its one of those thing i wish has thought of years ago when such things would have been a great benefit....

i used to have problems with macros and security levels with users- i would have just deleted the code after it ran to stop the problems (had i thought of it!)

Jason,

Nubi's right, deleting the macros before the end users get their hands on it is the better method. In some of the code I've written I write out some VBS code that is executed on the server at the end of the batch job that creates the Eggshell files. Basically the VBS creates an Eggshell app instance and then feeds each Eggshell file into the app. The workbook_open sub executes OK and deletes itself (the VBA and macros only), the VBS then performs a save, so the Eggshell file is formatted and has no VBA within it. No prompt to the user for a file with macros and no extensive run times whilst the formatting takes place. I have a couple of jobs that do this and the WF creation of the eggshell ouput takes about 3 or 4 minutes for around 350 files and then Eggshell (run on the server) takes about 50 mins or so to open each in turn and run the VBA. Doing this via Reportcaster overnight means the users have their files ready for further analysis when they arrive in the AM.

The only downside is having Eggshell installed on the reporting server but then the advantages far outweigh the disadvantages.

T

Edited to correct smelling pistakes

This message has been edited. Last edited by: Tony A,

its Tony A's idea or reeducation of staff and we both know what that means- yup its often easier to write code than expect people to understand simple instructions....but always check your market; if they are not luddites then it may be worth trying- me? i'd go with code- you can trust code