11-01-2016 09:26 AM - last edited on 11-01-2016 09:41 AM by LinusH
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
11-01-2016 09:40 AM
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.
11-01-2016 09:47 AM
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:
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.