10-17-2016 11:30 AM
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.
10-17-2016 01:00 PM
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.
10-17-2016 01:15 PM
10-17-2016 02:15 PM
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.
10-17-2016 01:50 PM
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
Here's an Excel DDE sample code: