Populating Formatted Excel Spreadsheet

Reply
New Contributor
Posts: 2

Populating Formatted Excel Spreadsheet

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

 

 

Super User
Posts: 18,997

Re: Populating Formatted Excel Spreadsheet

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.

New Contributor
Posts: 2

Re: Populating Formatted Excel Spreadsheet

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

Super User
Posts: 18,997

Re: Populating Formatted Excel Spreadsheet

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.

Ask a Question
Discussion stats
  • 3 replies
  • 250 views
  • 0 likes
  • 2 in conversation