On 7/31/08, Alan Churchill <savian001@gmail.com> wrote: > Also, I am not picking a fight here: use whatever you want to use. However, > I think my approach has a lot of merit.

Nor am I. I am trying to learn and help others in some small way, in doing so learn more for myself. My motives are completely selfish.

I agree that .NET is probably the best solution, my experience is limited. Your programs don't run on my computer.

> > 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.

I'm sure everyone will agree with that.

> > 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.

That is where you have an advantage. I am a simple user in a sea of users, using the tools on the computers we are given to work on.

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

So is .NET a language or do you call the "objects" from another language I have seen you mention C#, I belive.

> > 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.

I've looked at the paper, and agree that your program is fast.

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