Update Excel Sheet

[EnricoCola]

Hi, I'm trying to update an excel file. This file is a pre-formatted template that I use as stencil to generate multiple files. It contains several sheets, a sheet in which there is a sasout and others that have formulas with links to the first. These sheets are small reports and have a header and a table with few rows that should be replicated depending on the number of rows in the db sas.
The steps to take are as follows:
1- Update the sasout sheet
2- Copy the rows present in the report sheet
3- Select n rows according to the number of rows of the SAS db
4- Insert the copied lines

Are there alternative methods to DDE?
Thank you

[Reeza]

1. Replace entire report with a formatted report from ODS EXCEL
2. Update your Excel template to use Tables and update your SASOUT table only and change it to allow the rest to flow through so step 2-4 should disappear. You can use FCOPY to copy the template to a new file.
3. Investigate requirements again and see if another tool is more appropriate, such as a BI tool like Visual Analytics (Tableau/PowerBI).

[EnricoCola]

1. Replace entire report with a formatted report from ODS EXCEL

Re : I won't rewriting all formulas, even complex . In this way my colleagues can modify the formulas without knowing sas 

2. Update your Excel template to use Tables and update your SASOUT table only and change it to allow the rest to flow through so step 2-4 should disappear. You can use FCOPY to copy the template to a new file.

RE:  The problem is that the length of the sasout is not standard

3. Investigate requirements again and see if another tool is more appropriate, such as a BI tool like Visual Analytics (Tableau/PowerBI).

RE: unavaible

I find a code that use VBA for modify excel:

options noxwait noxsync;
%let vba_loc=%sysfunc(getoption(WORK))\vbscript.vbs;
%let open_workbook = &path.\Analisi\Test.xlsx;
data _null_;
file "&vba_loc";
put "Set objExcel = CreateObject(""Excel.Application"") ";
put "objExcel.Visible = True ";
put "objExcel.DisplayAlerts=False";
put "Set wb = objExcel.Workbooks.Open(""&open_workbook"")";
put "wb.sheets(""headings"").Range(""B12"") = ""Prova""";
put "wb.save";
x "'&vba_loc\'";
run;

But I haven't found everything I need. It can be a solution ?

[Reeza · Accepted solution]

---

@EnricoCola wrote:

2. Update your Excel template to use Tables and update your SASOUT table only and change it to allow the rest to flow through so step 2-4 should disappear. You can use FCOPY to copy the template to a new file.

RE:  The problem is that the length of the sasout is not standard

---

The new Tables feature in Excel makes that a moot point, the table grows as the data does. 

---

@EnricoCola wrote:

1. Replace entire report with a formatted report from ODS EXCEL

Re : I won't rewriting all formulas, even complex . In this way my colleagues can modify the formulas without knowing sas 

---

ODS Excel allows you to write formulas to cells, not just raw data, so your colleagues maintain their ability to update the file.

[EnricoCola]

Thanks, I try to use ODS

[SASKiwi]

Another way to easily update an Excel Sheet is to use the SAS Add-in to MS Office. You are required to run the update tasks manually though from the SAS Menu in Excel.

[EnricoCola]

I have considered this solution, but with the new version of excel installed, the Add-In are not available.

[Kurt_Bremser]

Doing BI work in Excel when SAS is at hand is at best stupid. Excel provides so many opportunities for undetected mistakes that I (and I am not alone in this) consider using it for business critical tasks as gross negligence.

In our company, internal audit works hard at preventing such practice.

I second what others have suggested: move the whole calculation into SAS, and use Excel for presentation only.

[ammarhm]

I second what everyone else has said about moving the whole solution to SAs instead.

However, if that is not possible for some reason, you might want to look at the following two documents they might set you in the right direction. 

https://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKh...

https://support.sas.com/resources/papers/proceedings14/1793-2014.pdf

Kind regards

[EnricoCola]

Thanks for the advice, unfortunately I face different opinions and habits that I am not able to eradicate. So I must find other solutions that satisfy my customers