Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] WF 8008 Excel Question

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] WF 8008 Excel Question
 Login/Join
 
Master
posted
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,


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Guru
posted Hide Post
There are only limited options to control this out of the box. Many settings like margins, page size, etc are controlled by Windows.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Expert
posted Hide Post
Have you considered used Excel Templates for your output?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
To accomplish such a things, you will need to use Excel Macro Template.
All the requested features are controlled by Excel not Focus.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
One of the many things I do using excel templates with an Auto_Open macro.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Master
posted Hide Post
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?


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
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.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
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
.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
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.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
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....


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
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!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
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.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
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...?


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
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.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Master
posted Hide Post
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...


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
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.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Master
posted Hide Post
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?


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
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.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] WF 8008 Excel Question

Copyright © 1996-2020 Information Builders