How to write to pre-existing Excel template starting at row 2 or 3 and save the file using DDE?

[Stalk]

What changes should I make to the following code to write to the existing template starting from row 2, column 1 and then save the file. 

options noxwait noxsync;
x '"C:\temp\Pd_import_v2_blank.xlsx"';
filename example2 dde 'excel|PD Import!r3c1';

I am open to any any other better options to write to locked excel template and saving it in another folder with filename derived from a macro variable?

Thank you,
data _null_;
file example2;

run;

[Reeza]

filename example2 dde 'excel|PD Import!r3c1';

R3C1 is Row 3 Column 1, ie A3 in Excel. 

Sounds like you want to switch it to A2 instead?

I think you can see how to change. 

AFAIK there is no other way to write data to a template, if you don't want a header row and have a very specific location. 

PROC EXPORT will export to a defined range if you have the range, but it always includes a header. I work around this by linking my data but that doesn't work if you have a dynamic range. 

[Stalk]

Row 2 or Row 3 is not the issue here. I want to write to an excel template that have locked headers. One file might have 5 rows and another file might have 100 rows starting from row2 column 1.

And I want to save this file each time after updating the rows with a different name.

[Patrick]

To my knowledge you can add a sheet to an existing workbook but you can't update an existing sheet with SAS only.

Using ODS EXCEL you can (re-) create a sheet and add a lot of Excel formatting and functionality to it. Below example creating a sheet with a frozen header row.

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsug/p09n5pw9ol0897n1qe04zeur27rv.htm 

data class;
  set sashelp.class;
  format age z10.;
run;

ods excel 
  file="c:\temp\test_%sysfunc(datetime(),B8601DT15.).xlsx"
  options(
      sheet_name='MySheet' 
      FROZEN_HEADERS='ON'
    )
  ;
proc print data=class noobs;
run;
ods excel close;

If you Google you will find examples using DDE for updating an Excel sheet - but that's not only very slow and DDE depreciated, it also would only work with SAS under Windows (and create technical debt).

You will also find examples where 3rd party components like VBS get called - but this is then again Windows specific and "cumbersome" so think long and hard before going down such an avenue.

[Reeza]

Ok.
See page 5 here which has more code examples for DDE then.
https://support.sas.com/resources/papers/proceedings/pdfs/sgf2008/229-2008.pdf

Here's the reason DDE is not really a good option anymore:
https://us-cert.cisa.gov/ncas/current-activity/2017/11/09/Microsoft-Releases-Security-Advisory-Dynam...

You can probably unlock cells with DDE but haven't done it in ages, you'd have to find the guide from Microsoft.

[ballardw]

Also since DDE is no longer a high priority with Microsoft there are other products that intentionally or not seem to use the communication channels that DDE uses or breaks them entirely.

Cisco Jabber is one. In this case of this program you have to KILL the process running Jabber, not stop the program because the process used is the issue and will continue to block DDE if with the application turned off, and then restart after your DDE code runs.

[Reeza]

My approach would be:

1. Modify the template to have a new sheet that serves as the data source and either link that to your locked cells instead or use Step 4.
2. Use FCOPY to copy the template to a new location with a new name
3. Use PROC EXPORT to export data to the new sheet/source
4. As required, use VBS or VBA to update the data from the sheet to the location needed, can be triggered via X Command/SAS. 

Still pretty Windows based with the VBS but the first three steps will be fairly system independent.

Note that if you have in Excel a Table, it will automatically increase in size as needed. I think this holds if you export to it as well but you'd need to test it. 

Or recreate everything in the template using ODS EXCEL or Report Objects but significantly more work for sure. 

[Stalk]

Dear all,
Thank you for all your responses. Unfortunately I've least control of the excel template. It has two sheets. First sheet has instructions and second sheet has locked headers, it was built and shared by other department. My role is to process the data and dump the final results to this excel template to easily upload into a normalized database.
Currently I am processing the data , ordering the fields and exporting the data to excel and then copy paste this data to the actual template. I will have about 500 such spreadsheets that I have to process.
Just trying to establish the workflows to eliminate the transcript error while copy pasting from one excel to another .

[Bravez]

This new solution may help, similar and beyond DDE, it can flexibly inject values to any cell of your Excel/ Word/ PowerPoint template: One-Click Report Automation - An automated and user friendly workflow for efficient, flexible, and e...

[Patrick]

@Stalk What do you mean by "locked headers"? Frozen headers or really locked - which requires the whole sheet to be protected.

DDE is depreciated and technology you shouldn't be using anymore. Current SAS engines don't allow to update an Excel.

You could use SAS to create a separate Excel with your data and then use Python or a .vbs to copy the data into your template starting at row 2. At least for the .vbs approach SAS would need to execute under Windows and I'm not sure if Excel would need to be installed or if the required modules are already part of a "foundation" Windows OS. 

I've used the .vbs approach in the past with SAS first generating the .vbs and then calling it out of SAS (requires SAS option XCMD set - installation default is NOXCMD).

But having said all of the above: If you're the only one that needs the template then I'd go back to the template creators and propose a change that would make it easier for you. The most common way would be for SAS to create and populate an additional sheet and the template sheets then sourcing the data from there.