04-01-2014 10:10 AM
I have a problem with the dde session in sas. I have a sas 9.2 an excel 2010 and win 7 pro.
Sometimes when I'm using dde long or if I open another excel file or if the turn Sleep mode or hibernation and begin again the work of sas dde session stops working and sas process has frozen up.
I turn sas and excel but it does not help. I have to restart computer.
My sas code:
options NOXWAIT noxsync;
FILENAME CMDS DDE 'excel|system';
My question is how to fix it without restart the computer and what to do to be able to use two excel process first with a macro that I use and second without macros (because if the second excel process is active calling macro does not work).
Thanks for help
04-01-2014 10:26 AM
Well, it should have a process attached to it, so cntrl + alt + del then Task manager. Find the process and end it.
As for using multiple Excel instances, I am not sure this is possible. DDE is a historical thing. Whilst its still available you may be better off looking at ods tagsets.excelxp with other operation as MS are going for XML based file format now. If for instance you need to run macros over data, why not create your tagset output in SAS, then have a master Excel document which when you run the VBA macro asks for a directory, then processes all the SAS output in that directory. I.e. use SAS to create output and VBA to process it later.
04-01-2014 11:25 PM
For its time (over 25 years now) DDE was pretty cool stuff. If you could find out how, you could use it to start an Excel session, open an existing spreadsheet or create a new one, populate cells or ranges of cells with data, format them, launch old style Excel macros, or alternately read data from spreadsheets, and save and close files. It was very popular with SAS users for reporting and much more flexible than the alternatives of creating a CSV file to interchange data or purchasing SAS Access to PC Files Formats. It was built into Base SAS from late version 6. That people are still using it today is a testament to its strengths, and none of the more recent technologies cover the full functionality.
Sadly DDE fell out of favour although some parts of Windows (copy and paste, allegedly) still use DDE. Microsoft moved on to OLE and .NET, and provided the Jet data technology for accessing Excel and MS Access. More recently XML has been adopted as the 'universal' interchange technology. Old style Excel Macros have been superseded by VBA.
The biggest problem with DDE is that it was a data exchange between applications that were currently open in the same Windows session. If you had multiple Excel instances, it was hard to predict which session DDE would link up with. It was not robust if anything happened to either of the applications communicating via DDE, as seems to be the case in the example above. The cure is to treat DDE as a temporary process and not to leave it going while you go away and do something else.
Because DDE requires applications to be open in the same Windows session it is not scalable. I have seen instances where reporting suites have been moved from running on individual PCs onto Unix servers (which breaks DDE immediately) or Windows servers using Enterprise Guide as the local front end. All such reports have to be completely rewritten to use Proc Export or ODS tagsets to move data from SAS to Excel. And while Microsoft still supports DDE there is no guarantee this will continue. So despite the nice features of DDE it is no longer safe to continue to develop applications using it.
Message was edited by: Richard Carson
04-02-2014 03:30 PM
No, because your code is running a VB macro, RefreGu, that is embedded in the book most likely.
Without seeing the rest of the code, or the VB its hard to offer alternatives.
One option is to write a VBS, or .Net script that does this and then use the X command to call that script.
04-03-2014 03:59 AM
I would agree with Reeza. One other I would suggest is to investigate what that code is doing in the workbook (and why). If it is important then look at the problem from the other way round. As I mentioned previously use SAS to create output files - very straightforward with the tagset:
ods tagsets.excelxp file="...";
proc report data=...
ods tagsets.excelxp close;
This generates output files which can be used in Excel. At this point you have the base data, then open Excel and run that macro, if it needs input files modify it to point to the SAS output. If it is just adding some formulae then this can be done directly within the proc report code, here is a good example of such:
04-05-2014 04:30 AM
I understand you.
My Excel macro is very simple it is refreshing graph link.
ActiveWorkbook.UpdateLink Name:="D:\Tr\Ex_Gu_1.xlsx", Type:= _
I tried vbs but it is at least twice slower than dde and at the rate I am very dependent.
Thank you very much for any help.
04-05-2014 12:18 PM
You may be able to update links directly using DDE without the VB script.
The issue with DDE is that you can't do anything else while doing it.
My issue when running multiple processes with DDE/VB is that the DDE wasn't waiting for the VB to finish and it was running all over itself, need ing to full restarts on my end.
04-07-2014 04:00 AM
Sorry, I may not fully understand what you are doing here, but if it is just updating the links, then when not put the code in Private Sub Workbook_Open()
That way the links will update anytime the document is opened, hence avoiding the necessity to do it from SAS at all?
04-07-2014 05:53 AM
Not sure I understand your point. What I am saying is to move the code which updates the workbook to the Workbook Open function in Excel rather than a macro that you need to run automatically. Then when someone opens the Excel file, the links would be updated at that point. Completely separate from and SAS processing.