Is it possible to send formatting commands, ie Shrink to 75% or Fit all columns to one page or Margin settings or Repeat headings on Pages etc...so the user need not do any formatting once the page is opened in Excel format; FORMAT EXCEL...and no we do not want the PDF format.

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

There are only limited options to control this out of the box. Many settings like margins, page size, etc are controlled by Windows.

Have you considered used Excel Templates for your output?

To accomplish such a things, you will need to use Excel Macro Template.
All the requested features are controlled by Excel not Focus.

One of the many things I do using excel templates with an Auto_Open macro.

I have a template file, my first. It houses a glossary tab and is the recipient of WF data on tab1 and tab2. It works fine. I want to do some page set up stuff on tab1. I recorded a macro in the template file to set to print to color as a test. I stuck the marco code in the appropriate are to run on open.

It forced me to save the file as .xltm as opposed to .xltx. I copied the .xltm file to my baseapps as I had my .xltx file beforehand.

I altered my code as such:

TABLE
.
.
.
-*ON TABLE HOLD AS tab1 FORMAT XLSX TEMPLATE &APPROOT/baseapp/Formtemplate.xltx SHEETNUMBER 2
ON TABLE HOLD AS tab1 FORMAT XLSX TEMPLATE &APPROOT/baseapp/Formtemplate.xltm SHEETNUMBER 2
.
.
END

TABLE
.
.
.
ON TABLE PCHOLD FORMAT XLSX TEMPLATE tab1.xlsx SHEETNUMBER 1
.
.END


When I run, I now get this error:
0 NUMBER OF RECORDS IN TABLE= 116 LINES= 49
0 XLSM FILE SAVED ...
0 NUMBER OF RECORDS IN TABLE= 965 LINES= 57
(FOC3289) TEMPLATE FILE: Error opening file
(FOC3317) Error processing template file.
Any ideas?

Replace

ON TABLE HOLD AS tab1 FORMAT XLSX TEMPLATE &APPROOT/baseapp/Formtemplate.xltm SHEETNUMBER 2
.
.
END

TABLE
.
.
.
ON TABLE PCHOLD FORMAT XLSX TEMPLATE tab1.xlsx SHEETNUMBER 1
.

By

ON TABLE HOLD AS tab1 FORMAT XLSX TEMPLATE &APPROOT/baseapp/Formtemplate.xlsm SHEETNUMBER 2
.
.
END

TABLE
.
.
.
ON TABLE PCHOLD FORMAT XLSX TEMPLATE tab1.xlsm SHEETNUMBER 1
.

I use an Excel Macro-Enabled Workbook file to perform the same, not a template.

Almost right, the initial template is still .xltm, not .xlsm:

[QUOTE]Originally posted by MartinY:
By

ON TABLE HOLD AS tab1 FORMAT XLSX TEMPLATE &APPROOT/baseapp/Formtemplate.xltm SHEETNUMBER 2
.
.
END

TABLE
.
.
.
ON TABLE PCHOLD FORMAT XLSX TEMPLATE tab1.xlsm SHEETNUMBER 1
.

ok that worked but...

When the file opens I can see the wf data on the first tab then must click: Enable Editing followed by clicking: Enable Content. Then I see the little message I stuck at the end of the on open macro (following some page formatting) that says 'Macro has run'. The trouble is it does not seem to have performed any of my formatting.

If I just open the macro outside of wf, the formatting occurs and message is received but in this case wf has not stuck any data there.

actually the formatting is working EXCEPT for some reason I turned off the greyscale feature so it would print in color but for some reason it is leaving it, or turning it back on. My custom margins did make it through though....perhaps I need to re-record the macro....

yea..for whatever reason it will not let remove the default print in greyscale on the WF populated tabs. On my static glosasry tab it does...ideas? Sort of defeats all the color coding put in the report!

I'm not at all sure what you're doing, but it seems like you have "stuff" (anything counts) on the sheets that WF writes to?

I don't think that will work, as per my understanding, WF overwrites everything on those sheets. If you need to reference anything in there, you can reference it from other sheets though.

We usually handle the template-sheets populated by WF as pure data sheets and put actual report functionality on other sheets.

It seems:

1) WF inserts the new sheets onto whatever two sheets pre-exist in slot 1 and 2 of the macro template workbook. We pre-named blank sheet1 as: DeptData and blank Sheet2 as: SalesData.

We end up with WF data populating these two sheets. This works fine.


2) The recorded macro then runs. It was recorded to format Sheet1/Sheet2 and uniquely set the margins and orientation for each. Also recorded was the act turning off the greyscale printing ..though I am not sure code is being generated for that...not sure why.

This also works. I can tell because I oriented Sheet one horizontally at 65% and Sheet2 as vertical and 85% and those attributes are in place. Turning off the greyscale does not occur.



I think you are suggesting, something we just discussed, letting WF insert Sheet1/Sheet2, but have another sheet essentially reference everything on them and havethe macro hide these sheets. Our preference though would be to record a macro that does a copy/paste to a existing Sheets: FinalDeptData and FinalSalesData, defaulted to color printing (that seems to work) and then have the macro delete the sheets WF created after copying.


or something along those lines...?

That is correct. When WebFOCUS writes to a sheet, anything and everything on the sheet is replaced. Say you pre-format sheets 2 & 3, then WebFOCUS should write to sheets 4 & 5 and your macro copy what is needed to sheets 2 & 3. At the end of the macro, delete sheets 4 & 5 if needed. I gave a presentation on this technique at our last local user group meeting. I'll also add, make sure you add logic in the macro to make sure it doesn't run again if the user saves it then re-opens it. By default, since the workbook is a .xlsm, if the user saves it, it will save as a .xlsm, which will keep the macro. I also instruct my users to save the workbook as a .xlsx, which will strip out the macro. I find recording macros very tedious, which is why I have built a library of code that I can just copy and paste into a text file, then create an empty macro, then copy and paste the text file into the empty macro. Very handy, especially when the report changes.

I am so glad you mentioned the fact that if the do not save is xlsx that macro could run again!

I am not sure we need to copy to other sheets. I recorded a macro to set the page size to 70% and tweak the margins. It worked! Well at least until I switched my default printer...then, even though the printer does not seem to be referenced in the vba code it fails on: Application.PrintCommunication = True....


Unless you have an experience with this..I'll have to continue to tinker...

I found that in excel 2007 and higher, that having a default printer and the depending on the amount of data, number of sheets, etc., macro performance could be significantly affected in a bad way. The reason for this is that excel seem to be making sure everything being done was compatible with the default printer. I always include the following at the beginning and end of my macros:

Application.ScreenUpdating = False
Application.PrintCommunication = False

rest of macro

Application.ScreenUpdating = True
Application.PrintCommunication = True

Turning screen updating off and on, keeps the screen from flashing and the first thing the user sees in the formatted worksheet.

wait, adding that code made no difference. If my default printer is the one I set when I recorded the marco...it works. It I change my default to another printer and open the macro enabled template...it does not.

Since there are dozens of users who will use this new report...each with different default printers from mine, does this mean the whole page formatting thing simply will not work?

I have several thousand users all across the state with different printers. I have lots of reports that do page formatting for printing. I've never had the issue you are describing. PrintCommunication does not change or alter the default printer, it just tells Excel to stop communication with the printer while the macro runs.