Has anyone used a template file, which is a web archive .MHT, to retain printer settings in an Excel output report? I've followed the scant directions provided in the help and it seems as though using "templates" will just produce a web page type output. What I'm trying to do is to use a template that has the print settings already set to landscape and print everything on 1 page. Then, when my fex runs, I'd like the Excel file that is created to have these settings.
Any help is greatly appreciated.

p.s. I did find the excel macro solution that was posted previous to WebFocus 7.0, but I was hoping that these templates would actually be useful for this purpose.

You need to create a template that contains a macro to do the formating.

Open a new excel document make sure that you have 2 worksheets and that each sheet
contains at least a single space in at least one cell.

select tools/macro/macros (or press the Alt and f8 keys)

enter the name Auto_open

paste in the following vbscript code which

checks to see if the macro has alredy been run and exits if it has
selects the report area by finding the last populated cell,
sets the print area to A1:last cell ,
sets format as landscape,
sets print option to fit on 1 page high by 1 page wide
sets the page size to A4 (change xlPaperA4 to xlPaperLetter or what you need)
sets a variable that is used by the check to see if the macro has already been run

Public Sub Auto_open()
Dim nm As Name
On Error Resume Next

Set nm = ThisWorkbook.Names("myflag")
If Not nm Is Nothing Then
Exit Sub
End If
ThisWorkbook.Names.Add Name:="myflag", RefersTo:="=1", Visible:=False

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
Dim x As Long, lastCell As Range
x = ActiveSheet.UsedRange.Columns.Count
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintTitleRows = ""
.PrintTitleColumns = ""
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
End Sub

Close the macro editor and save the file as a .mht web archive to a directory
on your app path and change the PCHOLD line to

ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'template file' SHEETNUMBER 1

where template file is the name of your template without the .mht part

Hi JG,

One for the archives (no pun intended) I think, Thanks very much

T

JG,
Thanks for your help, but I still have a stumbling block. When I follow your directions, I get the following error:
(FOC3289) EXL2K TEMPLATE FILE: Error opening file
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND

I'm working in the projects area and the template is right in the same folder as the fex. Is this a problem? I would think that it should be in the APP Path.

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

It’s doable, I've done it. Had a production excel file used that to create a webarchive file (MHT). By doing so I took adavantage of all the predefined print areas, graphs, fonts, etc. All I did was save the preexisting excel file as a WebArchive file MHT extension. Made reference to it in the fex.

The snag I ran into was that you can only populate one worksheet with data or one range. You can’t send multiple record sets to a template cause it will end up pushing the last one to the template. Because of this I let the template technique go. I can see when it can be very useful.

The .mht file has to be in the app path otherwise WebFocus will not find it and will
give you the error you are getting.

Project folders do not have to be in the app path even though they are under the app folder.

Check the app path and mod it if needed or copy the .mht to baseapp.

rc

The method for compound excel and templates works very well.
It allows you to create compound documents with multiple sheets but not a single sheet.
However by using an Auto_open macro you can fix that when it opens.

The basic method is quite simple

report 1 uses your real template
report 2 uses report 1 as its template
report 3 uses repoort 2 etc. etc.

There is an example on http://techsupport.informationbuilders.com/sps/12012126.html

I copied this example in the past into an fex and was still not able to get it to work. Do you phiscaly get it to work? I've also opened a case regarding this example and they were not able to get it to work. If you have actual code working would you mind posting it or send it to me direct if possible.

Works with no problem at all (7.1.3)

code

FILEDEF FRED DISK fred.mht
-RUN
TABLE FILE CAR
PRINT DCOST
ON TABLE HOLD AS 'FRED' FORMAT EXL2K TEMPLATE 'printrange' SHEETNUMBER 1
END
-RUN
TABLE FILE CAR
PRINT MODEL
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'fred' SHEETNUMBER 2
END
-RUN

The template (.mht file) MUST be in a directry that is in your APP PATH.

The template should contain an Auto_open macro that does the post processing that you require.

since i'm still in 7.1.1 I might be having a problem.

Is there a way to set the printing page area to start every 50 rows or so?

Nevermind, I figured out an alternative solution.

I wonder what the alternative solution was?

I just edited his macro to set the vpagebreak to drag off to the right. That way the page height is already set up.

All I used is this:

Public Sub Auto_open()
On Error Resume Next
ActiveWindow.View = xlPageBreakPreview

With ActiveSheet.PageSetup

.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape

End With
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
ActiveWindow.View = xlNormalView
End Sub

It doesnt work for some reason if I dont set it to view by page break so I included that then switch it back to normal view before exiting.

eric

Hi again, I was wondering if anyone else had anyone experiencing problems opening these files being created using excel templates. I recently had a customer that would get an excel.exe (she has excel 2000) error when opening Offset:0005c7b9.

I tried a couple things like resaving it in an older excel format and she was still having the same problems.

As a temprorary solution I copied the worksheet to a new workbook and sent that off and it worked without a problem.

My guess it may have something to do with her not being able to open web archive files...can someone help provide me with some insight into this?

Thanks,
Eric

Hello Eric

i generate the following report with Excel output using also MHT template and it is working fine:
-**************************************************************
TABLE FILE EMPLOYEES
PRINT
EMPLOYEEID
FIRSTNAME
LASTNAME
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K TEMPLATE 'TEST' SHEETNUMBER 1
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END
-*****************************************************
the Template Must Contains 2 work sheet
the first one will contain data that populated by WF/Sheet 2 contain customized Sheet such as contain graph,cell formatting,......etc
Sheet 2 sure reading data from Sheet 1

i hope that will work

Hello again

i forget that Excel Template *.nht must Exist on the same application directory of Procedure

Hi Qalqili, the problem I am having is not in regards to creating the actual report but opening it with excel 2000.

I actually opened a ticket with IBI for this and they said it must be excel 2002 or newer. I asked if they had any alternative solutions and the only things they said were to either upgrade excel...which I cannot do because these reports are going to customers outside of our company or to create the report in standard excel 2k format, which defeats the purpose of using templates.

So I still have no alternative solution.