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
