Also, I am not picking a fight here: use whatever you want to use. However, I think my approach has a lot of merit.

The fastest way to work with Excel is at a pure binary level. DDE, VBA, and several of Microsoft's offerings (VSTO) use COM which, ultimately, does go to a binary level. However, you cross a lot of bridges in between until you get to it and it is woefully inefficient.

There are a number of 3rd party products that do write Excel to binary. These products are mostly .NET based products because .NET is extremely fast and efficient. Most SAS programmers do not know .NET and have no desire to learn .NET. What I did was purchase one of these 3rd party products, wrap it with custom code specifically targeted to SAS users. I then exposed that code in XML so that a SAS user can make modifications in either a data step or by hand and then the code will execute.

Hence, you get the power of the 3rd party product w/o having to know how to code in .NET.

How fast is it? 100x faster than DDE? 1000x? Basically, a lot. Which is why I wrote a paper describing the performance impact. I went from 6 CPUs running 18 hrs to 1 CPU in under 5 mins to create 10K spreadsheets.

Alan

Alan Churchill Savian www.savian.net

-----Original Message----- From: data _null_, [mailto:datanull@gmail.com] Sent: Thursday, July 31, 2008 5:43 AM To: Alan Churchill Cc: SAS-L@listserv.uga.edu Subject: Re: DDE Excel problem -- dealing with multiple Excel sessions

I expect that .NET is a fine program. But it is not installed on the versions of Windows that I use. I do not have the luxury of configuring PCs with new software. I need to use what is available. I'm just a user not a "consultant".

As you are the expert how can I determine if .NET is install on a PC?

Searching at support.sas.com "SAS-compliant XML" No results were found for your search.

On 7/30/08, Alan Churchill <savian001@gmail.com> wrote: > DN, > > I never said it was worthless. Deprecated? Yes. > > You can only run 1 Excel session at a time. The other sessions will step on > the running one. Someone correct me if I am wrong. > > What is needed, IMO, is for each EXCEL READER/WRITER to be an object. Not an > Excel instance (since that has COM legacy around it) but an Excel writer > object that stands alone and handles Excel binary format. I didn't create > this, I simply wrapped it and made it easy for SAS users to work with. > > Restart is not a good option, IMO. You won't have multiple versions of Excel > and Word running if you do not use the Excel or Word applications as > objects. When you work with DDE, that is what you are doing. You are > dredging up the entire thing to simply write a spreadsheet. > > I am nuts though: I don't accept status quo as how it should be done. > > I installed SaviCells on a fresh PC today, copied over 2 test datasets, and > had it running in under 5 mins. Simple, simple, simple. Converted the 2 > datasets to a fully formatted spreadsheet and then converted that > spreadsheet back to SAS-compliant XML and did the whole process in a > fraction of a second. Speed courtesy of .NET. > > Neither Excel or SAS is installed on the test machine. > > Alan > > Alan Churchill > Savian > www.savian.net > > > -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of data > _null_, > Sent: Wednesday, July 30, 2008 4:46 PM > To: SAS-L@LISTSERV.UGA.EDU > Subject: Re: DDE Excel problem -- dealing with multiple Excel sessions > > Yes, we all know DDE is depreciated and worthless. > > However it does work. > > And as best as I can tell from here the code you posted works too. > When I submit the code with EXCEL already running it does nothing. > When I submit the code and EXCEL is not running it starts excels with > the default workbook just as if I had started it myself. > > So show us some of your other code. > > As always with PCs a RESTART is always a good place to start testing. > When I am experimenting with office automation I often have "hidden" > sessions of EXCEL or WORD running that I can see from TASK MANAGER, > but cannot see on my desktop. Some times when I kill them Windows > seems more normal. Other times that does not work. "Windows normal" > is that an oxymoron? > > If you can tell us more about you application that would be good too. > Perhaps using another interface to EXCEL will be a better choice. > > On 7/30/08, Alan Churchill <savian001@gmail.com> wrote: > > Don't use DDE? > > > > Too flippant, I know. However, you are dealing with COM legacy issues and > I > > don't believe there is a way around it. > > > > SaviCells just went out for a quick beta run on the new version. As soon > as > > I have feedback on it, I will post a link to the download. It handles > import > > and export from SAS to Excel. > > > > Alan > > > > Alan Churchill > > Savian > > www.savian.net > > > > > > -----Original Message----- > > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of > > mischman > > Sent: Wednesday, July 30, 2008 3:49 PM > > To: SAS-L@LISTSERV.UGA.EDU > > Subject: DDE Excel problem -- dealing with multiple Excel sessions > > > > Everything works great unless there happens to be another instance of > > Excel running, in which case everything turns into chaos. I use a > > standard DDE routine to open an Excel session (see below). The > > problems occur when I try to write to Excel further on down in the > > program -- I end up writing results to the wrong Excel session, or > > worse. Any suggestions? > > > > %macro openExcel(); > > options noxsync noxwait; > > filename sas2xl dde 'excel|system'; * reference to open/close > > excel ; > > data _null_; > > length fid rc start stop time 8; > > fid=fopen('sas2xl','s'); > > if (fid le 0) then do; > > rc=system('start excel'); > > start=datetime(); > > stop=start+10; > > do while (fid le 0); > > fid=fopen('sas2xl','s'); > > time=datetime(); > > if (time ge stop) then fid=1; > > end; > > end; > > rc=fclose(fid); > > run; > > %mend openExcel; > > > >