topic Re: Excel 2007: push button to automate WRS report download. in Microsoft Integration with SAS

Excel 2007: push button to automate WRS report download.

Costas — Thu, 28 Oct 2010 15:10:01 GMT

Hi,
I was wondering if it's possibile to build a push button in Excel 2007 to automate (after proper login to metadata server) the download from a WRS report.
The report has several prompts.
I tried to record and assign a macro-button for that but does not seems to work; the macro doesn't work too.
Any tips about?
I'm using WRS and Addin for Office 4.2 on SAS 9.2 M2.

Thanks
Costas

Re: Excel 2007: push button to automate WRS report download.

CaseySmith — Thu, 28 Oct 2010 16:28:36 GMT

It is possible with the SAS Add-in to Microsoft Office (AMO) 4.3 (due to quite a few scripting improvements), but not in AMO 4.2. With AMO 4.3, you could associate VBA code similar to the following with your push button:

Sub OpenReport()

Dim SAS As SASExcelAddIn
Dim report As SASReport
Dim pathToReport As String
Dim destination As Range

Set pathToReport = "/BIP Tree/ReportStudio/Shared/Reports/Sonny/SimpleSuite/TablewithTotals.srx"
Set destination = ActiveCell
Set SAS = Application.COMAddIns("SAS.ExcelAddIn").Object
Set report = SAS.InsertReportFromSasFolder(pathToReport, destination)

End Sub

In addition to inserting reports, in AMO 4.3 you can also programmatically open data, run stored processes, create Pivot Tables, modify and refresh content, among other things.

Note: Using Excel's Record Macro feature does not capture AMO operations.

Re: Excel 2007: push button to automate WRS report download.

Costas — Fri, 29 Oct 2010 10:34:29 GMT

Thank you very much.
Ther's an hotfix to upgrade Addin Office AMO 4.2 => 4.3 or I nedd a new installation of SAS 9.2 M3?

best regards.
Costas

Re: Excel 2007: push button to automate WRS report download.

CaseySmith — Fri, 29 Oct 2010 13:07:07 GMT

You would need new installation media, yes SAS 9.2 M3.

Re: Excel 2007: push button to automate WRS report download.

Costas — Wed, 03 Nov 2010 09:13:40 GMT

Thank you very much.

Costas