Using ODS excel with a predefined Excel Spreadsheet template

[CHalla1]

I have an excel spreadsheet template that has to be filled in every week with SAS data. Way back when, I used DDE to do this and place data in pre-defined cells in the excel template. Since DDE is gone to pasture, I'd like to be able to do this with ODS excel if that is possible. Anyone have an idea of how I would start. 

I use ODS Excel all the time to create fresh excel tables but never for a pre-defined excel spreadsheet template.

Help.

[SASKiwi]

Unfortunately that is not possible. It is one of the restrictions of ODS EXCEL that it has to build workbooks from scratch. When you think about it, ODS EXCEL is just generator of XML that the Excel application renders as a workbook, so it would be way too unreliable and tricky to try and decipher existing XML to figure out what has to be amended or added.

An alternative approach could be to use PROC EXPORT to add or replace an existing data sheet (just simple rows and columns of data), then have formulas and / or macros in the workbook to copy the data over to the custom template.

[Patrick]

I've seen that done the way @SASKiwi explains. 

1. create a copy of your template

2. Use Proc Export and add data sheets

I consider it a sound design to keep report layout (structure) and data in separate sheets.

If you want to directly modify cell values in a existing sheet as you could using dde then look into Python package openpyxl that provides such functionality.

I've used this package in the past for use cases where ods excel didn't provide what I needed (i.e. creating Excels with lookup validations). SAS and Python work nicely together and even though I'm on a Python junior level but I was so far capable to "Google" all I needed to get to a solution.

[KevStopsk]

One trick I've found helpful is to first make sure your Excel template is set up exactly how you want your final output to look. This includes all your formatting, headers, and even formulas if needed. Once that's set, you can use ODS Excel to populate your data into the right cells.

I also found that checking out excel online tutorials can give you some great insights and tips on optimizing your template. You may want to experiment with PROC REPORT to control exactly where each piece of data lands in your template. Sometimes small adjustments, like ensuring that column widths in SAS match those in Excel, can make a big difference.

[Ksharp]

You could try UPDATE statement of PROC SQL.

libname x excel 'c:\temp\temp.xlsx' scan_text=no getnames=no;

proc sql;
update  x.'Sheet1$'n
 set F1='02' 
  where F3='2';
quit;

libname x clear;

[CHalla1]

Interesting. What if I wanted to write data at D3?

[Ksharp]

Sorry . You can't , due to there is no such variable in dataset after importing SAS from Excel.

[Tom]

This can only work if you can run EXCEL.  

What you showed will work when SAS is running on a Windows machine that also has a compatible version of EXCEL that it can execute.

You might also be able to use EXCELCS libref engine if you can get SAS PC FILES SERVER working on a machine that has EXCEL and also has access to the XLSX file you want to update.

[Sajid01]

One option is to consider Python.

Look here

https://stackoverflow.com/questions/51582786/using-python-to-load-template-excel-file-insert-a-dataf...