|Date: ||Tue, 1 May 2007 21:50:27 +0100|
|Reply-To: ||Peter Crawford <Peter.Crawford@BLUEYONDER.CO.UK>|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|From: ||Peter Crawford <Peter.Crawford@BLUEYONDER.CO.UK>|
|Subject: ||Re: Importing From Excel With DDE|
|Content-Type: ||text/plain; charset="iso-8859-1"|
See you there then and you can buy if I can beat your solution !
From: Alan Churchill [mailto:email@example.com]
Sent: 01 May 2007 21:47
To: 'Peter Crawford'; SAS-L@LISTSERV.UGA.EDU
Subject: RE: Importing From Excel With DDE
The reason why DDE is not a good option, IMO, is that DDE and VBA use
Excel COM interface under the covers. Excel COM will only allow for 1
instance at a time on a machine and is dreadfully slow (please see my
on this subject from SGF). 800 sheets becomes an issue due to volume,
especially if it is done on a normal basis.
To avoid the COM issue, a .NET version is available which is Aspose.
can run as many instances as a machine will allow and writes native,
Excel sheets. For reading or printing, its performance far outstrips
COM and it also comes with Intellisense to boot. Aspose can also do
read/writes to any cell in any order.
You can buy me a prickly pear margarita at next year's SGF and we can
into the nuances of DDE and .NET...;-]
Savian "Bridging SAS and Microsoft Technologies"
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Sent: Tuesday, May 01, 2007 2:36 PM
Subject: Re: Importing From Excel With DDE
can I chime in, rarely, disagreeing with Alan Churchill
I think this is probably a "best case" for DDE
Not the whole of these sheets are need - only a small range that
is common to all sheets.
Now (sas9.1) when we are able to use fileVar with DDE, it should
be possible to use one data step to iterate over all required
ranges. I'm just not comfortable with the scale ~800 sheets. In
practise, I would run in increasing batch sizes, until the batch
run-time became unweildy.
Each datastep iteration would :
open the workbook by passing a command through the excel!system
detect and wait until the book is open;
point the infile fileVar= option at the new book's range;
read the 7 rows of data and output ;
close the book through the command topic.
I recommend against using the excel libname, as well as proc
import (in general, but on this occasion in particular), because
the range is not near the top-left of the sheet, and intervening
data will probably fool these import handling methods into
choosing the wrong data types.
I'm glad it isn't my problem !
Good Luck Reeza
On Tue, 1 May 2007 13:28:33 -0600, Alan Churchill <savian001@GMAIL.COM>
>There are multiple ways of handling this issue. There are what I
>'best' ways and then what works.
>The 'best' way to handle Excel, IMO, is to use a 3rd party product such
>Aspose. Another good way is to use VSTO. Another way is to use VBA or
>don't use anything except Aspose. For less complex sheets, other
>be doable but I find all of the alternatives less palatable. For really
>complex sheets or very high volume, nothing, nothing will beat Aspose.
>I have started a section for Excel on sasCommunity.org and have started
>listing out all of the ways to interface with Excel. I would love for
>to start expanding on that section and fill in pertinent information on
>Look here for a place to share files.
>If you want me to post it, send it along and I will toss it out on the
>Savian "Bridging SAS and Microsoft Technologies"
>From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
>Sent: Tuesday, May 01, 2007 1:13 PM
>Subject: Re: Importing From Excel With DDE
>Is there a way to post a file to SAS-L? I don't have access to
>anywhere to make it downloadable, but if you email me, I can
>definitely provide a sample.
>I'm thinking of using a VB script with get.cell function.