• Skip to main content
  • Skip to footer
Revelwood Logo

Revelwood

Your SUPER-powered WP Engine Site

  • Who We Are
    • About Us
      • Our Company
      • Our Team
      • Partners
    • Careers
      • Join Our Team
  • What We Do
    • Solutions
      • Workday Adaptive Planning
      • IBM Planning Analytics
      • BlackLine
    • Services
      • Implementation Services
      • Customer Care
        • Help Desk
        • System Administration as a Service
      • Training
        • Adaptive Planning Training
        • IBM Planning Analytics / TM1 Training
    • Products
      • DataMaestro
      • LightSpeed
      • IBM Planning Analytics Utilities
  • How We Help
    • Workday Adaptive Planning Use Cases
    • IBM Planning Analytics Use Cases
    • BlackLine Use Cases
    • Client Success Stories
  • How We Think
    • Knowledge Center
    • Events
    • News
  • Contact Us

IBM Planning Analytics Tips & Tricks

IBM Planning Analytics Tips & Tricks: Planning Analytics for Microsoft Excel (PAx) VBA API Calls

 

Tips & Tricks

June 19, 2018

By Revelwood

This is a guest post from Revelwood’s Shane Bethea.

Did you know IBM Planning Analytics lets you make API calls through VBA in Planning Analytics for Microsoft Excel (PAx)? You probably know that action buttons can be used to refresh or rebuild reports in PAx. But what if you want to refresh the data in your report as part of a VBA routine? Maybe there are more tasks you want to perform through VBA and then you want to refresh the data as the last step. IBM offers the ability to make API calls through VBA in PAx with just a few steps.

The first step is to open the Visual Basic editor in Excel and import two files (one module and one class module) that ship with your PAx installation. Do this in the VBA editor by right clicking on your workbook and selecting Import File.

Make VBA API calls through PAx

This opens an Import File dialogue box. Navigate to your PAx install location (either Program Files or Program Files (x86) ) and then navigate to \ibm\cognos\IBM for Microsoft Office\Automation\ .

You can then import two files: ‘CognosOfficeAutomationExample.bas’ and ‘CognosOfficeMessageSuppressor.cls’.

Learn how to make VBA API calls with PAx

Once imported, you can use the public sub routines in the CognosOfficeAutomationExample module. This module includes some useful sub routines, such as:

  • ClearAllData() – Functions the same as the Clear All Data toolbar/ribbon button to clear Explorations and reports from the workbook.
  • RefreshAllData() – Functions the same as the Refresh All Data toolbar/ribbon button to refresh data for Explorations and reports in the workbook.
  • RefreshAllDataAndFormat() – Functions the same as the Refresh All Data toolbar/ribbon button to refresh data and formats for Explorations and reports in the workbook.
  • UnlinkAllData() – Functions the same as the Convert to Static toolbar/ribbon button to unlink all data in the workbook.

If you want to Refresh or Rebuild a Dynamic Report in PAx (formerly Active Forms in TM1 Perspectives), you have to get more creative. To accomplish this, follow the steps to import the two modules discussed earlier and then add the below code to the CognosOfficeAutomationExample module:

How to make VBA API calls with PAx

Now you can call the RebuildDynamicReport() sub routine from a Command Button or other VBA macro to rebuild your Dynamic Report.

IBM Planning Analytics is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Author

  • Revelwood
    Revelwood

    View all posts

    • Categories

      • Awards & Recognition
      • Financial Close & Consolidation
      • FP&A Done Right
      • IBM Planning Analytics Tips & Tricks
      • News & Events
      • Success Stories
      • Tech Bulletins
      • Workday Adaptive Planning Insights
      • Workday Adaptive Planning Tips & Tricks

    Popular Posts

    Authors

    Adam Riskin
    Adam Riskin
    Ailenette Cruz
    Ailenette Cruz
    Alex Goldman
    Alex Goldman
    Ben Alcock
    Ben Alcock
    Brian Combs
    Brian Combs
    Dave Miersch
    Dave Miersch
    Dillon Rossman
    Dillon Rossman
    Gary Leiffer
    Gary Leiffer
    John Pra Sisto
    John Pra Sisto
    Ken Wolf
    Ken Wolf
    Koury Reid
    Koury Reid
    Lee Lazarow
    Lee Lazarow
    Lisa Minneci
    Lisa Minneci
    Marc Assenza
    Marc Assenza
    Mary Luchs
    Mary Luchs
    Michelle Song
    Michelle Song
    Nina Inverso
    Nina Inverso
    Revelwood
    Revelwood
    Robert Nordhagen
    Robert Nordhagen
    Tara Byrnes
    Tara Byrnes
    Wesley DeMarco
    Wesley DeMarco

    Sign up for our newsletter

    Connect

Footer

Revelwood Overview

Revelwood helps finance organizations close, consolidate, plan, monitor and analyze business performance. As experts in solutions for the Office of Finance, we partner with best-in-breed software companies by applying best practices guidance and our pre-configured applications to help businesses achieve their full potential.

EXPERTISE

  • Workday Adaptive Planning
  • IBM Planning Analytics
  • BlackLine

ABOUT

  • Who We Are
  • What We Do
  • How We Help
  • How We Think
  • Privacy

CONNECT

Contact:

25B Vreeland Road, Suite 111 Florham Park, NJ 07932
201.984.3030
info@revelwood.com

Copyright © 2023 · Revelwood Inc. All rights reserved. Revelwood® and the Revelwood logo are registered marks of Revelwood Inc.