Javier, Thanks for your reply. I have tried the printing suggestion as per your suggestion, but the Excel document created has rows missing (whereas the RTF one doesn't).
I will have a further look at your code, but the problem is that the report totals up rows etc, and has a few variables as well, so creating all of this in another table is something I would rather not contemplate as yet. Next suggestion is to scan a correct report, put it though OCR, then import it ..., (or hopefully RBTI may resolve the issue which appears to exist with the exporting procedure, at least for my application.) Thank you. Regards John Docherty -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Javier Valencia Sent: Tuesday, 19 July 2005 9:03 a.m. To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Printing to an Excel File - V7.5 10.30715 John: Maybe if you provide more details someone will likely chime in with a solution. I send data to Excel in 2 different ways, the first one is to display the report on the screen and then select the print icon on the upper right hand, select print to file and under type select Excel Document; this works mostly for simple report as you loose a lot of the formatting, I believe that this is basically what you are doing (using the command line). The second option is to export the data directly to Excel, here is a snippet of code I use to do just that: LABEL getfoldername -- Get Directory Name PLUGINS loaddirectoryname.rbl vfolder_name_out + |path_name &vfolder_name_out + |title SELECT folder FOR new spreadsheet + |fullpath ON IF vfolder_name_out = '[Esc]' THEN GOTO done ENDIF IF vfolder_name_out IS NULL THEN PAUSE 2 USING 'Directory Not Selected!' + CAPTION 'Directory Selection Result' + icon stop GOTO getfoldername ENDIF -- Create the file name SET VAR vfile_out = ('''' + .vfolder_name_out + '\' + .vgemp + + '-' + CTXT(.vfrom_date) + '-' + CTXT(.vto_date) + '.XLS' + '''') SET VAR vfile_out = ( SRPL(.vfile_out,'/','_',0)) SET VAR vtab = ( CTXT(.vfrom_date) + '-' + CTXT(.vto_date)) SET VAR vtab = ( SRPL(.vtab,'/','_',0)) -- Confirm export SET VAR vmsg = + ('Export Parameters' + CHAR(013) + + 'Employee: ' + CHAR(9) + .vgemp + CHAR(013) + + 'FROM:' + CHAR(9) + CHAR(9) + CTXT(.vfrom_date) + CHAR(013) + + 'TO:' + CHAR(9) + CHAR(9) + CTXT(.vto_date) + CHAR(013) + + 'Will be exported to:'+ CHAR(013) + + .vfile_out +CHAR(013) + + 'Do you want to proceed?') DIALOG .vmsg vyes_no endkey NO + CAPTION ' Export Parameters ...' + ICON APP + OPTION TITLE_FONT_COLOR BLACK + |TITLE_BACK_COLOR WHITE + |TRANSPARENCY 255 + |WINDOW_BACK_COLOR WHITE + |BUTTON_YES_CAPTION &Proceed + |BUTTON_NO_CAPTION &Cancel + |BUTTON_YES_COLOR GREEN + |BUTTON_NO_COLOR RED + |BUTTON_YES_FONT_COLOR WHITE + |BUTTON_NO_FONT_COLOR WHITE IF ( vyes_no = 'No' ) THEN GOTO done ENDIF -- Export Data SET VAR vcomm = ('GATEWAY EXPORT xls ' + .vfile_out + + ' SELECT labor_date,labor_hr_reg,labor_hr_ot,labor_hr_2times,labor_hr_all + FROM temp_graph1 OPTION col_names ON |tab_name ' + .vtab + ' + |show_progress ON |action openview') &vcomm LABEL done CLEAR VAR vfolder_name_out, vfile_out, vmsg, endkey, vyes_no, vcomm RETURN As you can see, the user is prompted for a directory and the a name for the file is generated based on the name of the employees selected and the "from" and "to" dates and the data is exported; all the data is stored in a temporary file (created early in the process). This approach has worked very well for me; in fact, RBTI fixed some problem with the formatting several releases ago and now it works fine; as the file is exported as raw values, it is very easy to manipulate with Excel, as there is no formatting to worry about. Javier, Javier Valencia, PE President Valencia Technology Group, L.L.C. 14315 S. Twilight Ln, Suite #14 Olathe, Kansas 66062-4578 Office (913)829-0888 Fax (913)649-2904 Cell (913)915-3137 ================================================ Attention: The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from all system and destroy all copies. ====================================================== -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] Behalf Of John Docherty Sent: Monday, July 18, 2005 3:34 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Printing to an Excel File - V7.5 10.30715 As I have managed to get the data to an RTF file, does anyone know how I can get an RTF file into Excel - I need a 'quick fix' rather urgently. Any suggestions would be appreciated. Thank you. Regards John Docherty -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of John Docherty Sent: Tuesday, 19 July 2005 6:57 a.m. To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Printing to an Excel File - V7.5 10.30715 David, I am using 7.5, latest build. (All I was saying re 7.1 is that I haven't changed the report at all to take advantage of the additional features in 7.5.) Regards, John Docherty -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of David M. Blocker Sent: Monday, July 18, 2005 5:18 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Re: Printing to an Excel File - V7.5 10.30715 John THis is a known issue with 7.1. It has been fixed in 7.5 David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "John Docherty" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Monday, July 18, 2005 7:17 AM Subject: [RBG7-L] - Printing to an Excel File - V7.5 10.30715 I am trying to print a report to an Excel file, but I find that some columns are missing, and some rows are dropped altogether. This is a 7.1 type report with the data being printed in a break footer. Is there anything in particular one should do to make this work ? - the report works properly when printed to the printer, but not when using the following command R>PRINT sp_ex OPTION xls |FILENAME spex.xls Printing to an RTF file also works properly, but any suggestions as to how to get the XLS option to work would be appreciated as this is not getting very far very fast. Thank you. Regards John Docherty -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.336 / Virus Database: 267.9.0/46 - Release Date: 16/07/2005 -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.336 / Virus Database: 267.9.0/46 - Release Date: 16/07/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.336 / Virus Database: 267.9.0/46 - Release Date: 16/07/2005
