topic Export tables to an existing macro-enabled Excel file in SAS Programming

Export tables to an existing macro-enabled Excel file

yaniv_daaata — Tue, 26 Dec 2023 14:16:05 GMT

Hi,

I have an Excel file, xlsm, that has formulas to fill up reports.

I am trying to find a way, of running SAS programs, and then all the tables will be exported to that Excel. Ideally, the data will be sent to the cells that are linked already to the formulas.

Is it doable ?

If not, can I export to the file to another sheet ? of course without taking the risk of ruining anything in the file.

Thank you.

Re: Export tables to an existing macro-enabled Excel file

Tom — Tue, 26 Dec 2023 20:07:17 GMT

Why not try it and see if it causes any trouble? Make a copy of the file to work with so you don't lose your original file.

You cannot write to named ranges of individual cells.

You can create a new worksheet in the workbook.

If by "macro enabled" you mean you have an XLSM file instead of an XLSX file then make sure to use the right extension when specifying the filename.

You might need to make the macros in the Excel file smart enough to find the new sheet if you want them to use the data.

Re: Export tables to an existing macro-enabled Excel file

yaniv_daaata — Tue, 26 Dec 2023 22:36:16 GMT

Which method would you use ? Proc export ? ODS ?

Re: Export tables to an existing macro-enabled Excel file

SASKiwi — Tue, 26 Dec 2023 22:47:10 GMT

You can't use ODS EXCEL to update an existing Excel workbook, it has to be created from scratch each time.

PROC EXPORT enables you to replace or add a complete sheet in a workbook, or a range within a sheet. If you have formulas, they should be in a permanent sheet that is not overwritten by SAS.

Re: Export tables to an existing macro-enabled Excel file

Patrick — Wed, 27 Dec 2023 01:39:01 GMT

Afaik SAS does not directly support .xlsm Proc Export: DBMS=data-source-identifier

You could use Python package openpyxl for writing to this .xlsm. From a design perspective: I'd keep the "raw" data in separate sheets.

For SAS to interface with Python:

If you've got SAS Viya then use Proc Python, if you've got SAS9.4 then call the Python script via call system() or the like (needs option XCMD set).