Instead of DDE, tagsets or...

edineshkumar_it — Tue, 01 Nov 2016 13:41:33 GMT

Hi Guys,

I have a question. Currently I'm using DDE to copypaste data from one excel into another and for formatting stuffs in local SAS.Can someone help me to do this without DDE is SAS.

I tried tagsets excelxp too but when I tried to do it in a xlsm file the file become unusable. Please help me on this

Re: SAS DDE

LinusH — Tue, 01 Nov 2016 13:40:34 GMT

If you have specific problems with tagsets, please provide more information, such as SAS code used, any warning/error messages, Office version and bitness etc.

If you wish go towards an application design, I would recommend SAS Add-in for MS Office, and let a Stored Process update your spreadsheet.

Re: SAS DDE

RW9 — Tue, 01 Nov 2016 13:47:08 GMT

Hi,

This is a question & answer forum, not a training site. If you have specific question then post them, with example test data (in the form of a datastep) and what the output should look like. Tagsets.excelxp is quite powerful, but bear in mind that it generates XML output which Excel can read and interpret. There is a newer version ods excel:

http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets-from-sas/

Which can create native XLSX files (which are ZIP files with various XML and other files within).

You can also get data out to Excel by using proc export, CSV, libname excel.

DDE is quite old now (decades), and doesn't work in some setups. I would avoid it.

What I would suggest is that if you are using Excel - in this case copy from one workbook to another - then it makes sense to use the native application - Excel + VBA. It is a realy simple process to copy from one file to another in this manner:

Sub Copy()
  Dim ws1 As Worksheet
  Set ws1 = ThisWorkbook.Worksheets("<Your Sheetname>")
  ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
End Sub

No reason to go out to a third party app like SAS to do things like this. You can apply all Excel functions within there, and you don't need to save the VBA to the output file, just save it to an empty Excel file, and load/close your other files.

As a final note, XLSM = Macro Enabled Excel file - this is not the basic Excel sheet, but contains a binary component. SAS won't write out to these very well (they are not open source). I am not surprised you end up with a corrupt file, as stated above tagsets.excelxp writes out XML text to a file which Excel can read and parse, it doesn't update a file in place.

topic Re: SAS DDE in Microsoft Integration with SAS

Instead of DDE, tagsets or...

Re: SAS DDE

Re: SAS DDE