I am working on automating a set of reports that would need to be formatted including the page setup and print settings. Is there a way to export a data set to an existing spreadsheet starting the data on the second row?
I want to export the data to the existing sheet, then rename the file and email it out through a macro since there are hundreds of recipients of the report.
And just a clarification of what TAGSETS.EXCELXP is doing. It is NOT doing an "official" EXPORT to an Excel workbook/worksheet. So you could NOT, for example, add information to an existing workbook using TAGSETS.EXCELXP.
However, TAGSETS.EXCELXP will create a NEW Workbook with Multiple Worksheets (if you want multiple sheets) and TAGSETS.EXCELXP will write the correct XML tags in the output file so that Excel maintains the ODS style formatting. TAGSETS.EXCELXP also has options that allow you to specify page setup (like gridlines) and print settings (like portrait, landscape or print headers) for Excel when Excel opens the file.
You can even email the file that's created directly from SAS, if your folks have or will turn on your email server capability.(They have to define the email info in the SAS startup routines.)
But every time you run TAGSETS.EXCELXP, you would either be creating a new file or writing over an existing file. The TAGSETS.EXCELXP destination produces Microsoft-compliant Spreadsheet Markup Language XML tags around your procedure output to define how the workbook/worksheets should be rendered when Excel opens the file that ODS creates. It is not technically an EXPORT because it works in a different way than the way PROC EXPORT creates an Excel binary file. (In fact, I think TAGSETS.EXCELXP is a better method than PROC EXPORT if you want to be able to set colors, fonts and Excel settings -- because TAGSETS.EXCELXP has those capabilities and PROC EXPORT does not.)