VBA/macros for Excel Add-In

Alessio_Pagliano

As a model builder I would like VBA and macros to be supported in the Excel Add-In so that I could further automate workbooks.

 

For example :

 

1) I would like to be able to create VBA code and macros for the various Anaplan actions so that I could automate a number of tasks (eg Login and refresh data) through macros

 

2) I would like to be able to understand, write and use Anaplan functions within a worksheet to dynamically create code to retrieve & write back functions. Below is a screenshot with my assumed, partial and most probably wrong understanding on how some of it works at the moment.

I basically tried to test my understanding writing a new CHOOSE function to retrieve other values, and it didn't work...I must have completely misintepreted how it could work 🙂

 

 

The purpose of this idea is mainly to understand if & how the community could benefit from these type of enhancements. 

Personal thoughts : from my point of view the potential application could be beneficial for different types of data reconciliation. Having said that, this could all be done within Anaplan itself and I'm tempted to think it could be better to create ad-hoc developments there or maybe raise specific Ideas for new Anaplan functionality to support these type of requirements ? 

 

JaredDolich

@Alessio_Pagliano 

Awesome idea. I share the same sentiment.

I think we should set up a demo mtg with @MagaliP at some point so she can see the specific use-cases for how we would use this.

My most popular use case is to take the user stories out of the Anaplan Way App into Excel. From there, I use VBA to reformat the data. Lastly, I wrote PowerPoint VBA to generate one slide per user story. Here's a sanitized example below:

 

Miran

Status changed to: In Review

Alessio_Pagliano

Hi @JaredDolich , 

Thanks for the feedback and for sharing what you did. Your use case looks so neat and tidy : good job!

Happy to jump on a quick call/contribute, if it helps.

I was wondering if you have considered using the New UX for it ?

I'm suggesting it as I recently had a requirement that could potentially be seen as vaguely similar. I had to share a spreadsheet with Anaplan (inc images) and we couldn't find a suitable tool for it.

The New UX seems to be working quite well for it - see below. Assuming there are no licence implications perhaps something similar could work for your use case ? Chances are you already considered that 🙂 

Of course T&Cs would apply to what images could/can't include and where they are stored....

Generally speaking I'm curious to see if/how all the current apps would have their New UX apps. This could also provide helpful ideas/inspiration to the wider community.

 

Thanks,

Alessio

 

 

MagaliP

We are investigating point 1 of @Alessio_Pagliano 's idea. What do you think of being able to do the 3 things with VBA macros:

1. Login
2. Refresh all
3. Change a page selector

@JaredDolich - I would love to have a chat with you and see the templates you are using. I will direct message you.

ablack

Should include the ability to save data too.

 

Maybe a separate thread, but thinking about competitor's Excel add-ins, there is a lot of neat features like formula replication (add-in knows to copy formula down or across based on dimensions), GET() / POST() functions to send/retrieve specific intersections of data, etc. Anaplan is missing a lot of this, and these would greatly extend capabilities.

JaredDolich

Good call out @ablack - are you able to share the name of any of those add-ins. I'd love to see that.

ramonito

@MagaliP 

 

Please consider the following :

 

• Login 
• Connect to Model
• Import the Saved view Line Items and Rows to Excel

 

 

monikakocot

Hi,

I have come across exactly the same challenges. Would be highly appreciated if we could use macros, especially to change a page selector and refresh the dataset.

Thank you,

Monika Kocot

 

bleestroud

I'm not very knowledgeable in Anaplan yet, so i'll mess up all the terminology, but here goes. Our builders are replacing certain Excel files (models) with Anaplan modules, but the main 2 Excel models will continue to be used. There are iterative calculations the require pulling answers from the Anaplan modules into Excel, using those answers as factors in the Excel model, then send resulting updated data back to Anaplan, and then do that circle again until the models converge. I would like to have VBA/macros in my Excel file to submit data to Anaplan via the Add-In, retrieve new answers from Anaplan, use the new answers in formulas in Excel, then submit revised data back to Anaplan. This needs to iterate up to 20 cycles for the anwer to settle into a single correct answer.

Alessio_Pagliano

Hi @bleestroud ,

I'm not sure if I fully understand the question. Have you already tried the basic Anaplan Excel Add-in functionalities ? Or could you mock-up what you feel is the blocker for you ?

Maybe I'm missing something but you can retrieve and submit data with the out of the box features of the Add-in, without VBA/Macros enabled. Or have you got specific examples of requirements you can articulate ?

A part from this, I would use the Anaplan Excel Add-In with caution, for data audit/quality reasons, to mention one. In the setup you describe I would be concerned about back&forth data changes.

Instead, is there a chance to review the scope and process ? eg Is there not an option to either fully replace the use of Excel with Anaplan or port as much of the process as possible so that this iteration happens in one single place ? Ideally that would be Anaplan.

Thanks!

bleestroud

Thank you for the response Alessio. Moving the entire model to Anaplan is not an option for now, which admittedly is creating the challenge.

Within Excel, i have an 'interest macro' that resolves various would-be circular references by copying certain formula-driven cells and pasting them to a separate table as values, which are then referenced back into the formula stream, thus avoiding circular references. This macro is run each time I make a change (or related group of changes) in the Excel model, and iterates around 15-20 times (without my interaction) before the numbers 'settle out' or converge, (such that an additional iteration would change nothing.) Some of these would-be circular formulas involve formulas from Excel models that have been moved to Anaplan models. In order to keep the iteration going untouched, my 'interest macro' needs to send data out to the new Anaplan model and received new information back for each of the 15-20 iterations. I'm trying to avoid human interaction with each iteration which would require me to manually click buttons to move the updated data out to Anaplan and receive updated data from Anaplan.

Also, regarding your warning about using the Excel Add-in with caution for data exchanges - is that because the Add-In is simply 'buggy', or because it's an administrative challenge to get the set-up correct and avoid errors?

Adrian1006

Hello, Is there any solution available for this topic? we are facing a similar challenge and would like to automate with vba the excel add-in.

Is vba the best approach?

The need is the same, automate the following:

• Login 
• Connect to Model
• Import the Saved view Line Items and Rows to Excel

Thanks

Alessio_Pagliano

Hi @Adrian1006 .

Unfortunately this enhancement request seems to "be stuck". I'm not sure who the relevant Anaplan product owner is now.

I'm tagging @ablack and @Miran hoping they could share some light.

Meanwhile…do you need to fully automate that part ? Or running it manually is acceptable ? Have you also considered using the Transactional APIs ? As for your requirement it might be handy ? But I am making assumptions. (eg have you got a view with LIs on columns and one or more lists in rows ?)

The challenge you might find (I did) is that any script automation/data manipulation of the output will require additional scripting expertise (eg javascript, Phyton).

I hope it helps and/or gives extra options,

Alessio Pagliano

Adrian1006

Thank you @Alessio_Pagliano for the advice,

We are trying to automate it, because running manually is taking a lot of time with the Add-in, I will evaluate a script to get this, definitely is a good point to start.

Thanks,

Adrian

VeronicaP

Hi @Alessio_Pagliano , I am the Product Manager for the Anapan Extensions. I can confirm that it is not on the roadmap for the Excel add-in to support VBA Macros.