BookmarkSubscribeRSS Feed
edineshkumar_it
Calcite | Level 5

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

2 REPLIES 2
LinusH
Tourmaline | Level 20

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.

Data never sleeps
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

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.  

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 2 replies
  • 918 views
  • 0 likes
  • 3 in conversation