I have a pivot table in a macro-enabled excel file (.xlsm), what happen is that this pivot table rely on all the data in another spreadsheet call "Data".
"Data" spreadsheet gets updated every quarter manually by copying and pasting job out of a text file spit out by SAS. Usually contains 200,000 rows of data. So every time when my analyst has to go tru the copy and pasting procedure is really time consuming.
what we are hoping is that using SAS to spits out the raw data nicely into this .xlsm file sheet!Data so all we have to do is everytime we can just open the excel file right click on the pivot table > refresh and from there no more tedious updating work!
Question: Is SAS able to spit out that data into a .xlsm file? If yes, i really need help to assign the data to designated cells, column and row into sheet!data.
The only SAS methods that I know of to write to an Excel file are:
1) PROC EXPORT and the SAS LIBNAME engine for Excel which write to .XLS or .XLSX (if you have SAS 9.2) file types
2) ODS CSV or ODS CSVALL -- which creates a comma-separated value file that Excel can open
3) HTML-based methods -- ODS HTML or ODS MSOFFICE2K which create an HTML file that Excel can open
4) XML-based methods -- ODS TAGSETS.EXCELXP which creates an XML (Spreadsheet Markup Language XML) file that Excel can open
You may be able to write to an XLSM file with DDE, ODBC or OLEDB methods.
The way I do this is to use the SAS Component Language (SCL which is part of SAS/AF) to get a direct object reference to Excel then have Excel make an OLEDB connection back to SAS using the sas.IOMProvider. From there I write the SAS dataset directly into Excel's pivotcache object which eliminates the need for an Excel "data" sheet. After that I open up an Excel macro file to handle all the formatting that was saved using Excel's macro recorder.
Here is the initial SAS code to get an object reference to Excel using the sashelp.fsp.hauto class...