topic Re: Populating Formatted Excel Spreadsheet in SAS Programming

Populating Formatted Excel Spreadsheet

TracyH17 — Mon, 24 Oct 2016 18:45:39 GMT

Hello,

I need to automate population of a an excel workbook. The spreadsheets have very specific formatting and layout requirements. I need to generate one workbook per study group, for a total of 44 study groups. Each workbook has two worksheets. The worksheets will need to contain formulas.

A colleague of mine recommended that I look into using the SAS Libname engine approach. I've also read a little about ODS using the Excel tagsets, and have read a little about DDE.

This colleague sent me the following paper: http://www2.sas.com/proceedings/sugi31/024-31.pdf . On pages 3-4 of the paper, it states:

"The SAS libname engine does not have the capacity to...write a formula into a cell." and "Note that DDE may be used to create, delete, or rename spreadsheets, as well as to format worksheets and enter and delete formulas."

Questions:

(1) for those of you with experience using both the SAS libname and DDE approaches to populating formatted excel workbooks, can I combine these approaches so that I can use the latter to overcome the limitations of the former (specifically with regard to creating formulas)?

(2) are there other approaches I can use to populate excel workbooks that have very specific formatting and layout requirements? My colleague looked at the workbook templates and thought I'd have difficulty trying to use ODS to match the formatting/layout.

Thank you,

Tracy

Re: Populating Formatted Excel Spreadsheet

Reeza — Mon, 24 Oct 2016 19:33:23 GMT

See this macro which should allow you more flexibility in exporting your data.

http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

Otherwise DDE or executing an external VBS script is an option. You can use SAS to generate the VBS script if necessary.

Re: Populating Formatted Excel Spreadsheet

TracyH17 — Mon, 24 Oct 2016 19:58:35 GMT

I am not familiar with VB. Is there another way to populate formatted excel spreadsheets without having to use VB?

Re: Populating Formatted Excel Spreadsheet

Reeza — Mon, 24 Oct 2016 20:29:12 GMT

Did you read the paper or try it?

You don't need to know VB for it.

In my experience it's your best bet given your stated requirements. Otherwise DDE is your second choice.