03-21-2016 04:53 AM - edited 03-21-2016 06:22 AM
I often use Send to Excel to export my output dataset for further use in Excel. EG then starts a separate session of Excel, also when Excel is already open. Is it also possible to Send the output to a new Workbook in an existing Excel session? I am running EG 6.1.
When having two separate Excel sessions, I cannot use copy - paste values. Also, I cannot use formulas from the first Excel to the second session.
Hope my problem is clear!
Thanks for your suggestions,
03-21-2016 05:14 AM
It sounds like one of two issues, and I don't use EG so not sure which. First, it could be that EG is using something could DDE to send the data to Excel. This is very old technology and really shouldn't be used at all anymore. If you want to create output, then use one of the new tech's, e.g. tagsets.excelp, libname excel, even proc export. As I said though I don't use EG so don't know if that is indeed the problem.
The other issue could be Excel's object instance, generally speaking Office objects like to be exclusive, i.e. one object, within multiple workbooks within it. Depending on implementation.
At the end of the day however the best recommendation when anyone mentions the name, is not use Excel at all for any purpose as it was never designed to do <insert what you want to do with it here>.
03-21-2016 05:38 AM
When I use Send to Excel in EG say from the Output window a box pops up saying do I want to open a new workbook/session or add to an existing workbook/session. Does that not happen for you?
03-21-2016 06:36 AM
No it is not the case.
Doc says: "You can send the results to a new document or an active document. If you send the results to an active document and you have multiple documents open, you can select which document to send the results to."
This question is not asked.
So please when any of the SAS employees has arrived...
After reading more of the same doc, it seems that I have to install the SAS Add-In for Microsoft Office. After I did, nothing changes. The other way around also does not work: I can choose "Open a SAS data source" from Excel, but it does not show my own (WORK) datasets or any of my own defined libraries.
Thanks for your reply!
03-21-2016 08:54 AM
Have you tried writing the table in a library defined within metadata and afterwards trying to open the table using Sas Add-Inn?
I use this technique quite often and there is no problem to have the data in the same excel session.
03-21-2016 09:48 AM - edited 03-21-2016 10:59 AM
Those metadata is readonly for me. I define my libraries by "submit sas code when server is selected". Still, it is a start!
03-22-2016 03:34 AM - edited 03-22-2016 04:02 AM
Rethinking, it may have something to do with the fact I am running EG on a BI Server. That server apparently is capable of starting an Excel session on my PC and dumping data into that session, but it probably cannot join an existing Excel session. Can somebody confirm this, or is there hope for me :-). Cheers, Eric
03-22-2016 02:43 PM
So do you remotely logon to your BI Server and run EG there? If so EG would only know about server Excel sessions not your PC Excel sessions.
Try opening Excel first on your BI server before a send. Do you get the window pop up then?
03-23-2016 02:18 AM
No, EG is running on my PC sending SAS commands to the BI Server and receiving output in the output tab. That output can be sent to Excel, but only to a new Excel session for every other dataset.