Write to Excel without DDE

[chimanbj]

I have a very specific Excel template that I need to populate, but I am running on a Unix system, so I won't be able to use DDE. Can I use ODS to put my report in specific cells?

[Cynthia_sas]

Hi:
ODS methods of creating files for Excel do not populate existing Excel files or templates. ODS creates HTML or XML or CSV files that Excel knows how to open and render. Each time that ODS creates a file -- if the file name does not exist in the specified location, then ODS writes the file; if the file name DOES exist in the specified location, then ODS overwrites the existing file with the newer results.

It may be possible for you to create a CSV (comma separated value) file with ODS and then link that CSV file to your Excel template. Or, if your template has named ranges, you may be able to use the SAS Excel LIBNAME engine or PROC EXPORT to export data to named ranges.

Depending on how complex your Excel template is, you may find that you can recreate it entirely using ODS TAGSETS.EXCELXP or ODS TAGSETS.MSOFFICE2K_X, which create Office 2003 XML and Office HTML files respectively. Without any idea of what the Excel template is or how it looks or what function/purpose it serves, it is hard to make more concrete suggestions.

cynthia

[SASJedi]

If the ODS solution is not feasible, check to see if you have licensed SAS/Access to PC File Formats for your Unix box, and if so, verify that the the SAS administrator has installed and configured the PC File server that comes with the SAS/Access product on a Windows box on your network.  With this infrastructure in place, you can read and write to Excel spreadsheets from your Unix SAS session using a LIBNAME statement.  The LIBNAME statement would look something like this:

libname xlr PCFILES path="D:\folder\MyFile.xls" server=<server name> port=<port#>;

You would replace <serve name> with the name of your server (somethin like pcff.mydomain.com) and <port#> with the port number used to access the PC File server (the default value is 8621). 

If you want to write data to specific cell ranges in the Excel spreadsheet, it is best to set up NAMED RANGES in the spreadsheet first. When you assign a libname to an Excel workbook that contains named ranges, each named range appears as a separate data set in the SAS library.  Due to limitations of the MS Jet Engine, which SAS Access uses to read and write to Excel, you can't overwrite data in the existing cells - you first have to empty the range then write in the new data.

For example, say the workbook MyFile.xls contains a named range called "PutDataHere" and you want to fill it with the data from SASHELP.CLASS.  The following code should do the trick:

libname xlr PCFILES path="D:\folder\MyFile.xls" server=pcff.mydomain.com port=8621;
proc sql;
   drop table xls.PutDataHere;
quit;
data xls.PutDataHere;
   set sashelp.class;
run;