Hi,
I have been using the Modano API to automate the production of PDF report from a Modano active model.
Everything works fine until I tried to have different no of column hidden in time series sheet. It seems to apply the hiding of column to all time series sheet with the same time hierarchy (i.e. hiding column J in 1 annual time series sheet will cause all column J to be hidden for all annual time series sheet in the model).
Is there a way to customize the number of columns hidden using the API for each individual time series sheet?
Thanks
Hi Alexander,
You raise an interesting point here - i.e. currently Modano always aligns all time series sheets in a workbook based on the same period titles set when you apply column grouping and/or change column visibility on any one of these sheets. Many users have noted this behaviour as being too heavy-handed, and we agree and will amend the system in an upcoming upgrade to allow the adjustment of specific columns within time series sheets. It's actually pretty fiddly stuff though, as the re-usability of modules does often rely on time series column properties remaining consistent across all related time series sheets.
Having said that, while changing the properties of time series sheet columns within modular workbook sheets using the normal Excel tools and the Modano API always aligns all sheets, you can simply use normal VBA as a workaround to hide specific columns on time series sheets and other time series sheets will not be aligned. For example, the following code can be used to hide only column J on the active time series sheet:
However, note that if the time series sheet has been set to have its columns automatically grouped and/or hidden when period titles are updated - as we've done on many of our forecast time series period titles sets in our historical & forecast modular workbooks - your visibility settings will be wiped out when the period titles are next updated.
This approach should be sufficient for your procedure to set up and PDF a sheet though. Personally, I'd set all the columns back to their prior visibility settings after running your PDF macro as this is better practice than leaving them inconsistent.
Hope this helps. M.
Hi Michael,
Thanks for your prompt response.
The workaround you proposed if perfect since I only need the columns to be hidden just seconds before the PDF is produced and can be revert back afterwards.
However, I do remember that in my previous forum post, you did say that hiding column using normal VBA have a risk of making the file corrupted as it messes with the metadata recorded by Modano. It has been almost 1 year ago so just confirming has this been addressed in the current version?
Otherwise, I was thinking to use just normal grouping VBA as below (assuming I don't have any other grouping level)
I would really appreciate your view on this Michael.
I am just thinking since the file would not be used only by myself and it is very likely that the people using the file may not be familiar with VBA, it might be good to revert to a safer method.
Thanks heaps,
Alex
Hi Alex,
There are certain things that will corrupt your modular workbook if you use VBA to do them, but changing the outline level and visibility of rows and columns will not cause any corruption.
One note on your VBA code though - You rarely need to (or should) select objects using VBA, so the code required to group column J and then hide it on Sheet1 is simply:
I hope this helps. M.