[Openroad-users] Transferring data into an Excel spreadsheet
Tulloch, Alex
ATulloch at aviagen.com
Fri Feb 29 20:35:57 EST 2008
The easiest way I have found of doing this sort of thing is controlling it from the excel end. I have always hit all sorts of obstructions dealing with Excel and Word and have tended to create a table in ingress, then use an ODBC call to populate the correct data-area on the worksheet, from a macro on the excel sheet.
I produce invoices from our OR sales system using word mail-merge in exactly this method as it wasn't reliable enough calling it all from Openroad.
I do have a way of retrieving values from an excel spreadsheet with active-x which could easily be converted to placing the values in the sheet rather than retrieving them.
(The only problems being the occasional ropyness of Microsoft Active-x).
I would think something like the following might work (sorry I don't have any time to confirm this, but it should be near!)
ExcelClass being a Microsoft excel object library!!!
declare
app = ExcelClass!Application;
wbk = ExcelClass!Workbook;
sht = ExcelClass!Sheets collection of ExcelClass!Worksheet;
wsh = ExcelClass!Worksheet;
wshkey = ExcelClass!Worksheet;
loadfile = varchar(200) not null default ' ';
row = integer not null;
col = integer not null;
minrow = integer not null;
mincol = integer not null;
totrow = integer not null;
totcol = integer not null;
rngcollist = ExcelClass!range;
rngrowlist = ExcelClass!range;
enddeclare
wbk = app.Workbooks().open(load_file);
sht = wbk.worksheets();
for row = minrow to totrow - 1 do
for col = mincol to totcol do
curframe.statustext='PROCESSING SHEET- '+varchar(shtno)+
' ROW-'+varchar(row)+
' COL-'+varchar(col)+' !';
rngcollist.cells().item(row,col).value = :h_variable;
endfor;
endfor;
wbk.save();
app.quit();
Alex Tulloch.
Systems Developer.
Aviagen Ltd.
* atulloch at aviagen.com
* +44 131 333 1056
rngcollist.cells().item(row,col).value
curframe.statustext='OPENING PERSONAL.XLS ...........!';
wbk2 = app.Workbooks().open('C:\Program Files\Microsoft Office\Office10\XLSTART\Personal.xls');
curframe.statustext='RUNNING BRP_VAL MACRO ...........!';
app.run('Personal.xls!brpnew_val');
wbk2.close();
curframe.statustext='CLOSING PERSONAL.XLS ...........!';
for shtno = 1 to 3 do
if shtno > 1 and h_loadtype = 'UK1' then
continue;
else
wsh = sht[shtno];
endif;
curframe.statustext='PROCESSING SHEET '+varchar(shtno)+' !';
if shtno= 1 then
/** GET SHEET TYPE **/
rngcollist = wsh.rows().item(1);
rngrowlist = rngcollist.cells().item(1,5);
h_loadtype = rngrowlist.value;
/** GET SHEET VERSION NUMBER **/
rngcollist = wsh.rows().item(1);
h_version = rngcollist.cells().item(2,11).value;
h_version = right(h_version,(length(h_version)-1));
if left(h_version,4) != h_curr_version then
rollback;
message 'THE SHEET YOU HAVE CHOSEN TO LOAD IS NOT THE CORRECT VERSION';
message 'THE CURRENT VERSION IS:- V' + h_curr_version + ' YOU ARE LOADING :- V'+h_version;
wbk.close();
app.quit();
h_ret = terminate_app('excel');
if h_ret != ER_OK then
message 'FAILED TO REMOVE EXCEL SESSION!!!';
message 'PLEASE USE YOUR TASK MANAGER TO CHECK + DELETE EXCEL SESSIONS IF NECCESARY!';
endif;
sht = NULL;
wsh = NULL;
wbk = NULL;
app = NULL;
curframe.flush();
resume;
endif;
/** GET MAX ROWS IN SHEET **/
rngrowlist = wsh.columns().item(1);
cell_val1 = rngrowlist.cells().item(1,5).value;
if cell_val1 = '' then
totrow = 0;
else
totrow = rngrowlist.find('',,,,,,,,).row;
endif;
if h_loadtype = 'UK1' then /** UK SMALL SHEET **/
totcol=26;
else
totcol=22;
endif;
elseif shtno = 2 then
if farmtog=FALSE then
continue;
endif;
if h_loadtype = 'UK1' then /** UK SMALL SHEET **/
continue;
else
totcol=13;
endif;
elseif shtno = 3 then
if proctog=FALSE then
continue;
endif;
if h_loadtype = 'UK1' then /** UK SMALL SHEET **/
continue;
else
totcol=26;
endif;
endif;
mincol=1;
minrow=4;
wsh = sht[shtno];
if totrow > (500 + minrow) and calctog = TRUE then
message 'THERE ARE TOO MANY ROWS IN THE SPREADSHEET TO LOAD!!';
wbk.close();
app.quit();
h_ret = terminate_app('excel');
if h_ret != ER_OK then
message 'FAILED TO REMOVE EXCEL SESSION!!!';
message 'PLEASE USE YOUR TASK MANAGER TO CHECK + DELETE EXCEL SESSIONS IF NECCESARY!';
endif;
sht = NULL;
wsh = NULL;
wbk = NULL;
app = NULL;
curframe.flush();
resume;
endif;
rngcollist = wsh.rows().item(1);
cell_val2 = rngcollist.cells().item(1,1).value;
if cell_val2 = '' then
rubbish = 0;
else
rubbish = rngcollist.find('',,,,,,,,).column;
endif;
for row = minrow to totrow - 1 do
for col = mincol to totcol do
curframe.statustext='PROCESSING SHEET- '+varchar(shtno)+
' ROW-'+varchar(row)+
' COL-'+varchar(col)+' !';
if h_loadtype = 'UK1' then /** UK SMALL SHEET **/
h_ps_age = NULL;
h_stock_dens = NULL;
h_rec_typ = 'CUSTOMER DATA';
if col = 1 then
curframe.trace(text='h_cust=rngcollist.cells().item(row,col).value;');
h_cust=rngcollist.cells().item(row,col).value;
elseif col = 2 then
curframe.trace(text='h_loc=rngcollist.cells().item(row,col).value;');
h_region=rngcollist.cells().item(row,col).value;
h_region=shift(h_region,-1);
elseif col = 3 then
-----Original Message-----
From: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] On Behalf Of Kim Ginnerup
Sent: 29 February 2008 09:08
To: International OpenROAD Users
Subject: Re: [Openroad-users] Transferring data into an Excel spreadsheet
I would really like to har about this as well.
I have used copytoclipboard with success.
But would like to make a more automated way I need to do graphs as well
Regards,
Kim
________________________________
Fra: openroad-users-bounces at peerlessit.com [mailto:openroad-users-bounces at peerlessit.com] På vegne af Allan Biggs
Sendt: 29. februar 2008 09:40
Til: openroad-users at peerlessit.com
Emne: [Openroad-users] Transferring data into an Excel spreadsheet
I have to report some data in an excel spreadsheet - I can think of many ways to do this outside of OpenROAD - but have decided that OpenROAD will be the simplest.
I wonder if anyone has a simple example that they would be prepared to share to get me started?
All I want to do is place some values into specified cells and save the spreadsheet.
Thanks in advance Allan
This communication is for use by the intended recipient and contains
information that may be Privileged, confidential or copyrighted under
applicable law. If you are not the intended recipient, you are hereby
formally notified that any use, copying or distribution of this e-mail,
in whole or in part, is strictly prohibited. Please notify the sender by
return e-mail and delete this e-mail from your system. Unless explicitly
and conspicuously designated as "E-Contract Intended", this e-mail does
not constitute a contract offer, a contract amendment, or an acceptance
of a contract offer. This e-mail does not constitute a consent to the
use of sender's contact information for direct marketing purposes or for
transfers of data to third parties.
Francais Deutsch Italiano Espanol Portugues Japanese Chinese Korean
http://www.DuPont.com/corp/email_disclaimer.html
*****************************************************************************
This e-mail contains confidential information and is intended solely for use by the individual named or entity to whom it is addressed. Please notify the sender and postmaster at aviagen.com immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the named addressee, you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. Aviagen accepts no liability for the content of this e-mail and any consequences of actions taken in reliance on the contents, unless that information is subsequently confirmed in writing. Any views, opinions or actions presented in this e-mail are solely the author's and have not been approved by Aviagen. Any defamatory statements or infringing communication is contrary to Aviagen policy and outside the scope of the employment of the individual concerned. No employee or agent is authorized to conclude any binding agreement on behalf of Aviagen with another party by e-mail. Aviagen has taken reasonable precautions to ensure no viruses are present in this e-mail, but cannot accept responsibility for any loss or damage arising from the use of this e-mail or attachments.
*****************************************************************************
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.peerlessit.com/pipermail/openroad-users/attachments/20080229/1c24fbf9/attachment-0001.html
More information about the Openroad-users
mailing list