Creating Excel files from Priority

A question that I see frequently asks how one can create an Excel file from Priority. There are two different answers to this question - one solution creates CSV files that can be read into Excel with no problem, and the other solution creates XLSM files that are native Excel files. The 'M' in this extension stands for Macro; one can include in these files a macro that executes on the data. Unfortunately, such files are often caught by email anti-virus filters, so sending them to third persons can be problematic.

For the purpose of this blog entry, I assume that there exists a report TEST_OUTPUT2EXCEL. It doesn't matter what the contents of this report are.

CSV method: this is very simple and creates, as expected, a CSV file. This means that the file does not allow any formatting of the contents.

:XLFILE = 'C:/TMP/TEST_OUTPUT2EXCEL.CSV';
EXECUTE WINACTIVF '-x', :XLFILE, '-R', 'TEST_OUTPUT2EXCEL';
:EMAIL = 'someone@somecompany.com';
MAILMSG 1 TO EMAIL :EMAIL DATA :XLFILE;

The first line defines a file name and the second line executes the TEST_OUTPUT2EXCEL report and sends the output to the denoted file. As far as I can figure out, the first parameter to WINACTIVF, i.e. '-x', means create an Excel/CSV file; the second parameter is the name of the output file; the third parameter is the report type: R = simple report, P = procedure, and the fourth and final parameter is the name of the report/procedure to be executed. The third line in the above snippet defines an email address and the fourth line shows a simple way to send a file to someone via email. The report itself does not have to be defined in any menu but the person executing the above code has to have permission for the report. This means that the report does not have to appear on a menu if it is executed solely by the task scheduler, but execution by 'normal' users requires the report to appear on a menu.

XLSM method: this is more complicated, naturally. There is a preliminary stage for this method that requires that the report appear on a menu: one has to create an Excel template for the report. One performs a right click on the menu option and chooses 'Create Excel template' then one runs the report. All the required formatting is done on the Excel file that is created, then one saves the file without changing its name, extension or location (the file name will be the name of the report and the extension XLT; the location will be ..\..\system\document). Once the template has been prepared, the report can be removed from the menu if it is not intended to be run manually. The template can be edited at a later stage, but again requires that the report appear on a menu.

The next stage is to find the template's number. There is no simple way of doing this; I opened WINDBI then typed 'SELECT * FROM EXCELTEMPLATES WHERE UDATE > SQL.DATE8 FORMAT'. This query will list all the templates that have been created today - there shouldn't be very many! The value of the EXEC field will be the EXEC number of the report. For the purposes of this blog, the template number will be 207, but I warn that this number has to be found empirically.

Once all the above has been done, the execution is simple:

:XLFILE = 'C:/TMP/TEST_OUTPUT2EXCEL';
EXECUTE WINACTIV '-R', 'TEST_OUTPUT2EXCEL', '-X', :XLFILE, 207; 
:EMAIL = 'someone@somecompany.com';
MAILMSG 1 TO EMAIL :EMAIL DATA STRCAT (:XLFILE', '.XLSM');

Here the order of the parameters seems more logical: type of report, name of report, Excel, name of output file (note that one does NOT give an extension) and number of template. This creates the file and adds the extension name; the extension has to be added automatically when sending the file by email, although as I noted at the beginning, such a file is likely to be stopped by an email anti-virus filter. This file will be formatted according to the format of the template file; one can also define a macro within the template file that is supposed to execute automatically when the file is opened (according to this page; I haven't tried this myself and there are likely to be problems executing macros on network files).

Update regarding how to find the template number: the template file will be in directory ..\..\system\document and its name will be something like TEST_OUTPUT2EXCEL_207.xlt, i.e. the name of the procedure or report for which the template was defined, followed by the number of the template. One can take the number directly from here instead of issuing a query via WINDBI.