The SAS Output Delivery System and reporting techniques

Excel Tagsets: Here's a script to shrink the output file

Reply
Super Contributor
Posts: 387

Excel Tagsets: Here's a script to shrink the output file

Hi,

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):

Sub DoWork()

  On Error Resume Next

  '----

  ' Start up Microsoft Excel

  '----

  Set excel = CreateObject("Excel.Application")

  If CheckError("CreateObject") = True Then

  Exit Sub

  End If

  excel.DisplayAlerts = False

  '-----

  ' open the workbook

  '-----

  Dim collection

  Set collection = excel.Workbooks

  Set workbook = collection.Open("C:\Temp\Book1.xlsx")

  Set collection = Nothing

  If CheckError("Workbooks.Open") = True Then

  Exit Sub

  End If

    '-----

    ' wait for 10 seconds, this should allow the Add-In plenty of time to load in Office

    '-----

    Wscript.sleep 10000

  '-----

  ' get ahold of the SAS Addin COM object

  '-----

  Dim SASAddin

  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

  Exit Sub

  End If

  '-----

  ' Save the newly refreshed workbook

  '-----

  workbook.Save

  If CheckError("workbook.Save") = True Then

  Exit Sub

  End If

End Sub

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.

Regards,

Scott

l

Attachment
Attachment
Ask a Question
Discussion stats
  • 0 replies
  • 641 views
  • 0 likes
  • 1 in conversation