SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

DDE for exporting data to an existing excel sheet

Reply
New Contributor Neo
New Contributor
Posts: 2

DDE for exporting data to an existing excel sheet

Hi all,

 

I have an excel(2007) workbook with 6 sheets. All contain data. Now i want to replace that data in all the tabs with the data that i have in 6 datasets in SAS.

 

Kindly help me with this.

 

Best Regards,

Super User
Super User
Posts: 7,695

Re: DDE for exporting data to an existing excel sheet

Why do you want to replace it?  If you have the data in SAS, then just export your data to Excel using proc export, ods tagsets.excelxp, libname excel etc.  If you need to keep it as Excel 2007 (and why?) then use proc export.  If it is a template Excel file that you need to update then that is very different.  Personally I would suggest exporting the data from SAS to CSV, then in your Excel file, write some VBA code to open those CSV files and populate the Excel file as needed.  The reason being is that Excel is unstructured/controlled, the data could be anywhere, in any format and if your forced to use that application, then use the functionality within that app to do the task, rather than try to mash it through another app.

New Contributor Neo
New Contributor
Posts: 2

Re: DDE for exporting data to an existing excel sheet

It is a standard template actually. And I send that as a report every month
with updated data. First, I export the data from SAS EG and then copy paste
the data in each sheet in the template manually, so I just wanted to
automate it.

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Super User
Super User
Posts: 7,695

Re: DDE for exporting data to an existing excel sheet

Yes, common thing this.  Look at what you do each time, is it exactly the same cells?  Excel is so loose that you might have to alter it each time anyway.  Now DDE does still work, for how long?  Its very old, and doesn't work on some setups.  Again, I would still go with my original suggestion.  Write some VBA, doesn't even have to be in that Excel file, a separate one which loads that file up, then loads the CSV data, then runs through some VBA code to put the data from the CSV where it should go, then save the file.  Its pretty simple.

Super User
Posts: 19,063

Re: DDE for exporting data to an existing excel sheet

I'm using DDE on Office 2013 with SAS 9.4 so it's still working Smiley Happy

Super User
Posts: 19,063

Re: DDE for exporting data to an existing excel sheet

Here's two references that may be helpful for you:

 

 

This is a macro based on VBScript that allows export to specific cells and/or an Excel Template

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

 

Here's an Excel DDE sample code:

http://www.ats.ucla.edu/stat/sas/faq/sas2excel_dde.htm

Ask a Question
Discussion stats
  • 5 replies
  • 452 views
  • 0 likes
  • 3 in conversation