topic Re: DDE for exporting data to an existing excel sheet in Microsoft Integration with SAS

DDE for exporting data to an existing excel sheet

Neo — Mon, 17 Oct 2016 15:30:18 GMT

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,

Re: DDE for exporting data to an existing excel sheet

RW9 — Mon, 17 Oct 2016 17:00:24 GMT

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.

Re: DDE for exporting data to an existing excel sheet

Neo — Mon, 17 Oct 2016 17:15:57 GMT

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

Re: DDE for exporting data to an existing excel sheet

Reeza — Mon, 17 Oct 2016 17:50:10 GMT

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

Re: DDE for exporting data to an existing excel sheet

RW9 — Mon, 17 Oct 2016 18:15:32 GMT

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.

Re: DDE for exporting data to an existing excel sheet

Reeza — Mon, 17 Oct 2016 18:17:01 GMT

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