{"id":4491,"date":"2024-08-29T14:04:58","date_gmt":"2024-08-29T14:04:58","guid":{"rendered":"https:\/\/quebit.com\/askquebit\/?p=4491"},"modified":"2026-01-16T15:43:09","modified_gmt":"2026-01-16T15:43:09","slug":"planning-analytics-for-microsoft-excel-api-for-macro-automation","status":"publish","type":"post","link":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/","title":{"rendered":"How Do I Use the Planning Analytics for Excel API to Automate Tasks with Macros?"},"content":{"rendered":"

Planning Analytics for Microsoft Excel API for Macro Automation<\/strong><\/p>\n

It\u2019s not uncommon to move an MS Excel workbook from Perspectives<\/strong><\/a> to Planning Analytics for Excel (PAfE)<\/strong><\/a> and find that there are buttons within the workbook that run macro(s) to perform automation tasks. If there are, you may encounter the following (or similar) error:<\/p>\n

\u201cCannot run the macro <macro name>. The macro may not be available in this workbook, or all macros may be disabled.\u201d<\/p>\n

\"\"<\/p>\n

If you verified your security settings and have enabled MACROS<\/a> and still<\/em> receive this error, you should know that unfortunately, TM1 macro functions are not supported<\/a> in Planning Analytics for Excel (PAfE). But don\u2019t worry, without much effort, you can leverage the\u00a0Planning Analytics for Microsoft Excel\u00a0API<\/a> for macro automation.<\/p>\n

The following is an overview of how to get your macros working again.<\/p>\n

Environment Setup<\/strong><\/p>\n

To leverage the Planning Analytics for Microsoft Excel\u00a0API, you need to add two files to your Excel Workbook. They are CognosOfficeAutomationExample.bas<\/strong>\u00a0and CognosOfficeMessageSuppressor.cls<\/strong>, which are really just text files containing Visual Basic for Applications<\/a> (or VBA) commands (macro code) and saved as file types of \u201c.bas\u201d and \u201c.cls\u201d so that they will be \u201crecognized\u201d by Excel and your macros will run. You may already have these files on your system, but if not, they are free to download, add to your environment and share.<\/p>\n

To download the files, you can:<\/p>\n

On an MS Windows platform, use your web browser to navigate to IBM Fix Central<\/strong><\/a>, select Download files using HTTPS<\/strong>, and then Cognos, Cognos for Microsoft Office (11.0, All platforms)<\/strong>. The file you want is coc_winx64h_11.0.1.11_ml.tar.gz\u00a0(15.52 MB)<\/a>. When the download process finishes, the file will be in your local \u201cdownloads\u201d folder where you can unpack it (to a suitable location) and then run: \\<\/strong> winx64h\\<\/strong> issetup.exe<\/strong>.<\/p>\n

When the installation completes, you should have a local \u201cC:\\Program Files\\ibm\\cognos\\IBM for Microsoft Office\u201d folder, with an \u201cAutomation<\/strong>\u201d sub folder holding these gems:<\/p>\n

\"\"<\/p>\n

Prepping The Workbook<\/p>\n

Before you can proceed any further, you need to have the \u201cDeveloper tab<\/strong>\u201d added to your MS Excel ribbon.<\/p>\n

The Developer tab in Microsoft Excel is\u00a0a feature that gives users access to advanced features, such as Visual Basic and Macro tools, ActiveX controls, and spreadsheet forms.\u00a0It is usually hidden by default, but you can enable it by following these steps:<\/p>\n

    \n
  1. Open your Excel workbook.<\/li>\n
  2. Right-click on any ribbon tab<\/li>\n
  3. Select Customize the Ribbon<\/li>\n
  4. Highlight the Customize Ribbon tab on the left side<\/li>\n
  5. On the right side, find the list of tabs in the ribbon and locate the Developer tab<\/li>\n
  6. Left click the box to check the Developer tab<\/li>\n
  7. Click OK<\/li>\n<\/ol>\n

    \"\"<\/p>\n

    Adding Files the Automation Files<\/p>\n

      \n
    1. Click the\u00a0Developer\u00a0tab, and then click\u00a0Visual Basic<\/strong>.<\/li>\n<\/ol>\n
        \n
      1. Right-click\u00a0VBAProject<\/strong>and click\u00a0Import File<\/strong>.<\/li>\n
      2. Browse to the location where the IBM Cognos Automation macro files are installed. The location we created above is\u00a0 \u201cC:\\Program Files\\ibm\\cognos\\IBM for Microsoft Office\\Automation.\u201d Click the\u00a0bas<\/strong>file and import it into the VBA project.<\/li>\n
      3. Repeat steps 2 to 3 to import the\u00a0cls<\/strong>file. You should end up with a VBAProject that now looks like this:<\/li>\n<\/ol>\n

        \"\"<\/p>\n

        Close the Visual Basic Edito<\/strong>r and make sure that you save the file, close it, and then reopen the file – you can now call the macros contained in the Cognos automation macro files from the VBA code that you write in your Excel workbook!<\/p>\n

        Let Us Test It<\/strong><\/p>\n

        Use an existing button or add a new one. I have added a new one for this exercise:<\/p>\n

        \"\"<\/p>\n

        Next, you can assign a macro to the button. I have added one to the CognosOfficeAutomationExample.bas<\/strong>\u00a0file:<\/p>\n

        \"\"<\/p>\n

        \"\"<\/p>\n

        Clicking the button shows that it is working:<\/p>\n

        \"\"<\/p>\n

        Conclusion<\/strong><\/p>\n

        You can see the following link to read more details on the steps I have outlined above: https:\/\/ibm.github.io\/paxapi<\/a>. In addition, the below documentation may be helpful when moving from Perspectives to Planning Analytics for Excel (PAfE):<\/p>\n

        https:\/\/www.ibm.com\/docs\/en\/planning-analytics\/2.0.0?topic=pame-moving-from-tm1-perspectives-planning-analytics-microsoft-excel<\/a><\/p>\n

        Last but not least, if you would like further assistance with Excel macros use within PAfE, or help in migrating to Planning Analytics for Excel, please reach out to QueBIT!<\/p>\n","protected":false},"excerpt":{"rendered":"

        Planning Analytics for Microsoft Excel API for Macro Automation It\u2019s not uncommon to move an MS Excel workbook from Perspectives to Planning Analytics for Excel (PAfE) and find that there are buttons within the workbook that run macro(s) to perform automation tasks. If there are, you may encounter the following (or similar) error: \u201cCannot run…<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[24],"tags":[108,55],"class_list":["post-4491","post","type-post","status-publish","format-standard","hentry","category-ibm","tag-ibm-planning-analytics","tag-planning-analytics-tech-tips"],"acf":[],"yoast_head":"\nHow Do I Use the Planning Analytics for Excel API to Automate Tasks with Macros? - QueBIT<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How Do I Use the Planning Analytics for Excel API to Automate Tasks with Macros? - QueBIT\" \/>\n<meta property=\"og:description\" content=\"Planning Analytics for Microsoft Excel API for Macro Automation It\u2019s not uncommon to move an MS Excel workbook from Perspectives to Planning Analytics for Excel (PAfE) and find that there are buttons within the workbook that run macro(s) to perform automation tasks. If there are, you may encounter the following (or similar) error: \u201cCannot run…\" \/>\n<meta property=\"og:url\" content=\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/\" \/>\n<meta property=\"og:site_name\" content=\"QueBIT\" \/>\n<meta property=\"article:published_time\" content=\"2024-08-29T14:04:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-01-16T15:43:09+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2024\/08\/Picture1-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"839\" \/>\n\t<meta property=\"og:image:height\" content=\"162\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Jennifer Field\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jennifer Field\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/\"},\"author\":{\"name\":\"Jennifer Field\",\"@id\":\"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/082a9c4156466b9cdad2632e3de62601\"},\"headline\":\"How Do I Use the Planning Analytics for Excel API to Automate Tasks with Macros?\",\"datePublished\":\"2024-08-29T14:04:58+00:00\",\"dateModified\":\"2026-01-16T15:43:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/\"},\"wordCount\":717,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2024\/08\/Picture1-1.png\",\"keywords\":[\"IBM Planning Analytics\",\"Planning Analytics Tech Tips\"],\"articleSection\":[\"IBM\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/\",\"url\":\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/\",\"name\":\"How Do I Use the Planning Analytics for Excel API to Automate Tasks with Macros? - QueBIT\",\"isPartOf\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2024\/08\/Picture1-1.png\",\"datePublished\":\"2024-08-29T14:04:58+00:00\",\"dateModified\":\"2026-01-16T15:43:09+00:00\",\"author\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/082a9c4156466b9cdad2632e3de62601\"},\"breadcrumb\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#primaryimage\",\"url\":\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2024\/08\/Picture1-1.png\",\"contentUrl\":\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2024\/08\/Picture1-1.png\",\"width\":839,\"height\":162},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/quebit.com\/askquebit\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How Do I Use the Planning Analytics for Excel API to Automate Tasks with Macros?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/quebit.com\/askquebit\/#website\",\"url\":\"https:\/\/quebit.com\/askquebit\/\",\"name\":\"QueBIT\",\"description\":\"QueBIT\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/quebit.com\/askquebit\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/082a9c4156466b9cdad2632e3de62601\",\"name\":\"Jennifer Field\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/02bd32c7255ad906cd2c6352f643559cfee048a9b227b5b07b6a6f48d5b4a658?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/02bd32c7255ad906cd2c6352f643559cfee048a9b227b5b07b6a6f48d5b4a658?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/02bd32c7255ad906cd2c6352f643559cfee048a9b227b5b07b6a6f48d5b4a658?s=96&d=mm&r=g\",\"caption\":\"Jennifer Field\"},\"url\":\"https:\/\/quebit.com\/askquebit\/author\/quebit\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How Do I Use the Planning Analytics for Excel API to Automate Tasks with Macros? - QueBIT","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/","og_locale":"en_US","og_type":"article","og_title":"How Do I Use the Planning Analytics for Excel API to Automate Tasks with Macros? - QueBIT","og_description":"Planning Analytics for Microsoft Excel API for Macro Automation It\u2019s not uncommon to move an MS Excel workbook from Perspectives to Planning Analytics for Excel (PAfE) and find that there are buttons within the workbook that run macro(s) to perform automation tasks. If there are, you may encounter the following (or similar) error: \u201cCannot run…","og_url":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/","og_site_name":"QueBIT","article_published_time":"2024-08-29T14:04:58+00:00","article_modified_time":"2026-01-16T15:43:09+00:00","og_image":[{"width":839,"height":162,"url":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2024\/08\/Picture1-1.png","type":"image\/png"}],"author":"Jennifer Field","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Jennifer Field","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#article","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/"},"author":{"name":"Jennifer Field","@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/082a9c4156466b9cdad2632e3de62601"},"headline":"How Do I Use the Planning Analytics for Excel API to Automate Tasks with Macros?","datePublished":"2024-08-29T14:04:58+00:00","dateModified":"2026-01-16T15:43:09+00:00","mainEntityOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/"},"wordCount":717,"commentCount":0,"image":{"@id":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2024\/08\/Picture1-1.png","keywords":["IBM Planning Analytics","Planning Analytics Tech Tips"],"articleSection":["IBM"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/","url":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/","name":"How Do I Use the Planning Analytics for Excel API to Automate Tasks with Macros? - QueBIT","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/#website"},"primaryImageOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#primaryimage"},"image":{"@id":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2024\/08\/Picture1-1.png","datePublished":"2024-08-29T14:04:58+00:00","dateModified":"2026-01-16T15:43:09+00:00","author":{"@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/082a9c4156466b9cdad2632e3de62601"},"breadcrumb":{"@id":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#primaryimage","url":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2024\/08\/Picture1-1.png","contentUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2024\/08\/Picture1-1.png","width":839,"height":162},{"@type":"BreadcrumbList","@id":"https:\/\/quebit.com\/askquebit\/planning-analytics-for-microsoft-excel-api-for-macro-automation\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/quebit.com\/askquebit\/"},{"@type":"ListItem","position":2,"name":"How Do I Use the Planning Analytics for Excel API to Automate Tasks with Macros?"}]},{"@type":"WebSite","@id":"https:\/\/quebit.com\/askquebit\/#website","url":"https:\/\/quebit.com\/askquebit\/","name":"QueBIT","description":"QueBIT","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/quebit.com\/askquebit\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/082a9c4156466b9cdad2632e3de62601","name":"Jennifer Field","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/02bd32c7255ad906cd2c6352f643559cfee048a9b227b5b07b6a6f48d5b4a658?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/02bd32c7255ad906cd2c6352f643559cfee048a9b227b5b07b6a6f48d5b4a658?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/02bd32c7255ad906cd2c6352f643559cfee048a9b227b5b07b6a6f48d5b4a658?s=96&d=mm&r=g","caption":"Jennifer Field"},"url":"https:\/\/quebit.com\/askquebit\/author\/quebit\/"}]}},"_links":{"self":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/4491","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/comments?post=4491"}],"version-history":[{"count":3,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/4491\/revisions"}],"predecessor-version":[{"id":4971,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/4491\/revisions\/4971"}],"wp:attachment":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/media?parent=4491"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/categories?post=4491"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/tags?post=4491"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}