VBA Macros

Note: This feature requires the OfficeReports Intelligo Premium version.

Tables in Excel

You can customize the Table content and layout or anything else in the workbook by specifying a VBA macro name in the Table or Chart Layout definition. Every time the table is calculated, the macro will be called automatically.

You can add your own macros to "C:\Users\Public\OfficeReports\template\ORXLSMacros.xlam"

Note: If your Layout Database is saved in a shared folder, then the template files are saved there as well!

 

The macros used in OfficeReports need to be functions (not sub!) with parameters as below in DemoMacro, because this is how OfficeReports will invoke the macro. The string returned should be an error message, or "OK" if there is no problem.

Here an example of an Excel macro that will change the text and color of the first cell in the table, the first cell in the current worksheet and the first cell of the first sheet in the workbook:

 

Function DemoMacro(curName As Excel.Name, grid As Boolean) As String

Dim curTable As Excel.Range

Dim curSheet As Excel.Worksheet

Dim curWorkbook As Excel.Workbook

 

On Error GoTo ErrHandler:

 

Set curTable = curName.RefersToRange

Set curSheet = curTable.Worksheet

Set curWorkbook = curSheet.Parent

 

curTable.Cells(1, 1).Value2 = "Demo Macro"

curTable.Cells(1, 1).Interior.Color = RGB(255, 0, 0)

 

curSheet.Cells(1, 1).Value2 = "Demo Macro"

curSheet.Cells(1, 1).Interior.Color = RGB(255, 0, 0)

 

curWorkbook.Worksheets(1).Cells(1, 1).Value2 = "Demo Macro"

curWorkbook.Worksheets(1).Cells(1, 1).Interior.Color = RGB(255, 0, 0)

 

DemoMacro = "OK"

Exit Function

 

ErrHandler:

DemoMacro = "DemoMacro Macro Failed: " & Err.Description

End Function

 

Please have a look at the macros in module "template" in the macro file. Your macros need to have the exact same parameters and return a string!

After creating your macro you have to create a Table Layout in the Table Layout settings and write the macro name in the 'Excel Macro' field:

Layout only available for: A specific chart style might be only relevant for frequencies or crosstab charts. Select in which context a type should be available.

Tables and Charts in PowerPoint

You can customize a Table or Chart content and layout or anything else in the presentation by specifying a VBA Macro in the 'Populate' definition. Every time the link is updated, OfficeReports will run the macro.

You can add your own macros to "C:\Users\Public\OfficeReports\template\PPLinkMethods.pptm". After adding your macro(s), you need to save the file as a "PowerPoint Addin": "C:\Users\Public\OfficeReports\template\PPLinkMethods.ppam".

Important: Note: If your Table Layout settings are saved in a shared folder, then the template files have to be saved there as well!

Important: Please have a look at the macros in module "template" in the macro file. Your macros need to have the exact same parameters and return a string!

 

Here is an example of a macro you can use on the Populate Definition for a PowerPoint Table:

 

Function DemoMacro(curTable As PowerPoint.Table, start_row As Integer, start_col As Integer) As String

Dim parentShape As PowerPoint.Shape

Dim curSlide As PowerPoint.Slide

Dim textBox As PowerPoint.Shape

 

On Error GoTo ErrHandler:

 

Set parentShape = curTable.Parent

Set curSlide = parentShape.Parent

 

curTable.Cell(1, 1).Shape.TextFrame2.TextRange.Text = "Demo Macro"

curTable.Cell(1, 1).Shape.Fill.ForeColor.RGB = RGB(255, 0, 0)

 

Set textBox = curSlide.Shapes.AddTextbox(msoTextOrientationHorizontal, 10, 10, 100, 40)

textBox.TextFrame2.TextRange.Text = "Demo Macro"

 

DemoMacro = "OK"

Exit Function

 

ErrHandler:

DemoMacro = "DemoMacro Macro Failed: " & Err.Description

End Function

 

To use the macro, add it in the Macro tab of the Populate Definition: