I've seen many questions on this and answered very many different ways but nothing definitive.

Has anyone found a way to actually write to an existing sheet in a template rather than WF replacing the sheet?

Before realizing that WF replaces the sheet I made a sheet with the perfect headers, froze top row and all columns filtered.

I'd like to predefine column widths etc. I just can't seem to get it the way I want in WF styling. I saw the mention of mht but no one I know has a copy of Excel 2003 to be able to export it for that to work.

I tried using the EXL2K option with HFREEZE but when it opens in Excel, you have to manually scroll the page. For whatever reason, Excel ignores the scroll wheel on the mouse.

This message has been edited. Last edited by: FP Mod Chuck,

As far as I know, you will need to use an Excel Macro Template where the macro will generates and format the Excel file as you wish once the data will be loaded (exported to Excel) by WF : auto run macro.

I believe MartinY is correct. We use a lot of excel templates and what has always worked is to just do a data dump to the first sheet and then have the template vlookup the data to populate the template and have a macro run upon opening to get the formatting correct.

HFREEZE is only for HTML output, that is documented (at least in our current version).

Whenever WebFOCUS writes to a sheet, EVERYTHING is replaced. It has always worked that way.

When I do a report, it is almost always in Excel. You can count on 1 hand the number of HTML and PDF reports we have. In Excel, we use templates with an Auto_Open macro. I start with a report and have WebFOCUS do what it can do easily. Then I use Excel to finish the formatting. Below is an example of one of the simpler macros. I use Excel to rotate column heading 90 degrees, auto fit column widths, freeze panes, turn on filters, draw borders and group borders. I even have a technique for wrapping column headings and doing vertical center. Instead of using a comma in the AS phrase, I use a colon. Then in the macro I change the colon to a Line Feed. The trick is finding out how many rows and columns there are and using the RANGE(Cells(r,c),Cells(r,c)).SELECT format of the RANGE command.

Sub Auto_Open()
  Application.ScreenUpdating = False
  Application.PrintCommunication = False
  Sheets(1).Select
  If Sheets(1).Name = "Sheet1" Then
    Sheets(1).Select
    Sheets(1).Name = "Pending Completion"
    Call format_ws
  End If
  Application.PrintCommunication = True
  Application.ScreenUpdating = True
End Sub
Public Sub format_ws()
  ws_last_col = Cells.SpecialCells(xlLastCell).Column
  ws_last_row = Cells.SpecialCells(xlLastCell).Row
  Range(Cells(1, 1), Cells(ws_last_row, ws_last_col)).Select
  Selection.VerticalAlignment = xlCenter
  Range("A8").Select
  Selection.RowHeight = 17.25  
  Range("A9,B9,J9:L9,Q9:S9,U9,X9,Y9,AC9,AD9,AF9,AG9").Select
  Selection.RowHeight = 89.25
  Selection.Orientation = 90
  Range(Cells(10, 1), Cells(ws_last_row, ws_last_col)).Select
  Selection.WrapText = False
  Range(Cells(9, 1), Cells(9, ws_last_col)).Select
  Selection.Replace What:=":", Replacement:="" & Chr(10) & ""
  Range(Cells(9, 1), Cells(ws_last_row, ws_last_col)).Select
  Selection.Columns.AutoFit
  Call borders
  Range("A8:C9,D8:E9,F8:G9,H8:U9,V8:X9,Y8:AE9,AF8:AI9,AJ8:AK9,AL9:AN9").Select
  Call thick_borders
  Range(Cells(9, 1), Cells(9, ws_last_col)).Select
  Selection.AutoFilter
  Range("A10").Select
  ActiveWindow.FreezePanes = True
End Sub
Public Sub borders()
  Selection.borders(xlDiagonalDown).LineStyle = xlNone
  Selection.borders(xlDiagonalUp).LineStyle = xlNone
  Selection.borders(xlEdgeLeft).Weight = xlThin
  Selection.borders(xlEdgeTop).Weight = xlThin
  Selection.borders(xlEdgeBottom).Weight = xlThin
  Selection.borders(xlEdgeRight).Weight = xlThin
  Selection.borders(xlInsideVertical).Weight = xlThin
  Selection.borders(xlInsideHorizontal).Weight = xlThin
End Sub
Sub thick_borders()
  Selection.borders(xlDiagonalDown).LineStyle = xlNone
  Selection.borders(xlDiagonalUp).LineStyle = xlNone
  Selection.borders(xlEdgeLeft).Weight = xlMedium
  Selection.borders(xlEdgeTop).Weight = xlMedium
  Selection.borders(xlEdgeBottom).Weight = xlMedium
  Selection.borders(xlEdgeRight).Weight = xlMedium
End Sub

Remember, once you have your report in Excel, almost everything you can do in Excel, you can put in a macro. The only limitation is your imagination.