08-22-2013 07:59 PM
Excel Tagsets are pretty powerful, letting you control much of the formatting of the output file. However, I've always loathed the bloated XML files it creates, especially if the file needs to be downloaded or is an email attachment.
(Note: AFAIK, this would be a Microsoft issue. I suspect Microsoft just document the Excel XML standard, and SAS complies with that requirement in the Excel tagsets output. But this is just conjecture.)
I was playing with AMO --> Tools --> Create schedule, which creates a VBS file similar to below (excerpt):
On Error Resume Next
' Start up Microsoft Excel
Set excel = CreateObject("Excel.Application")
If CheckError("CreateObject") = True Then
excel.DisplayAlerts = False
' open the workbook
Set collection = excel.Workbooks
Set workbook = collection.Open("C:\Temp\Book1.xlsx")
Set collection = Nothing
If CheckError("Workbooks.Open") = True Then
' wait for 10 seconds, this should allow the Add-In plenty of time to load in Office
' get ahold of the SAS Addin COM object
Set SASAddin = excel.COMAddIns("SAS.ExcelAddIn")
Set AddinObj = SASAddin.Object
Set SASAddin = Nothing
' refresh the workbook
AddinObj.Refresh( workbook )
If CheckError("Addin.Refresh") = True Then
' Save the newly refreshed workbook
If CheckError("workbook.Save") = True Then
This got me thinking that Excel could be run "in batch", so to speak, to shrink the bloated XML files. As an example, an Excel XML file I created was 16MB. After saving it as an XLSB file, it was 214KB, which is a massive shrinkage in size.
I converted the VBS to Powershell, and have attached two files: the Powershell script, and a batch file (optional), which reduces the quoting required to call this code from SAS. The Powershell script could certainly be augmented, but the guts are there.
The one caveat is I'm currently having problems running this code:
1) From a stored process running as sassrv
2) From EG running with an Integrated Windows Authentication profile
3) From EG running with a local machine account profile, such as <servername>\sasdemo
It works fine if I use my domain account login.
I'll post these issues in a separate post, probably in the deployment group.
Regardless, I hope this helps if you use Excel tagsets a lot and want to shrink your output files.