See you there then and you can buy if I can beat your solution !

Peter C

-----Original Message----- From: Alan Churchill [mailto:savian001@gmail.com] Sent: 01 May 2007 21:47 To: 'Peter Crawford'; SAS-L@LISTSERV.UGA.EDU Subject: RE: Importing From Excel With DDE

Peter,

The reason why DDE is not a good option, IMO, is that DDE and VBA use the 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 talk 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. Aspose can run as many instances as a machine will allow and writes native, binary Excel sheets. For reading or printing, its performance far outstrips Excel COM and it also comes with Intellisense to boot. Aspose can also do random read/writes to any cell in any order.

You can buy me a prickly pear margarita at next year's SGF and we can delve into the nuances of DDE and .NET...;-]

Alan

Alan Churchill Savian "Bridging SAS and Microsoft Technologies" www.savian.net

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Peter Crawford Sent: Tuesday, May 01, 2007 2:36 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Importing From Excel With DDE

Hi Reeza

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 command topic; 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

Peter C

On Tue, 1 May 2007 13:28:33 -0600, Alan Churchill <savian001@GMAIL.COM> wrote:

>Reeza, > >There are multiple ways of handling this issue. There are what I consider >'best' ways and then what works. > >The 'best' way to handle Excel, IMO, is to use a 3rd party product such as >Aspose. Another good way is to use VSTO. Another way is to use VBA or DDE. I >don't use anything except Aspose. For less complex sheets, other options may >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 people >to start expanding on that section and fill in pertinent information on this >topic. > >Look here for a place to share files. > >http://www.xdrive.com/partners/?p=aolgen&sem=1&ncid=AOLXDR0017000000000 1 > >If you want me to post it, send it along and I will toss it out on the web. > >Alan > >Alan Churchill >Savian "Bridging SAS and Microsoft Technologies" >www.savian.net > > > >-----Original Message----- >From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Reeza >Sent: Tuesday, May 01, 2007 1:13 PM >To: SAS-L@LISTSERV.UGA.EDU >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.