Cell & Value formatting with Excel Advanced

  • 19 October 2023
  • 2 replies
  • 271 views

Badge +1

Good afternoon!

Problem 1:

With the Excel advanced package, there doesn’t seem to be any option to format cells without Macros.

As my organization is expected to disable all macros across the business due to security policy, this is not a suitable (or sustainable) workaround.

I can see that we can get cell colour… but not set cell colour (or any other formatting options) without running a macro.

Although this isn’t a huge issue, it does leave me with plain header cells when dynamically creating them, it effects the aesthetic of the resulting excel workbook.

Problem 2:

As the Set Cell function requires a string to be entered, this causes the value entered on the spreadsheet to always be text, even if that is a number. There is no option to convert the number stored as text to number, without simulating keystrokes (double clicking in each cell).

This seems very strange as a lot of the time most of the data on Excel Workbooks are number values!

The issue this causes is that the end product cannot be analysed with formula, as the values are not recognised as numbers. My current workaround for this is to Set Cell Formula: =value($variable$).

 

I have seen that most responses to people asking about problem 2 suggest switching off error checking within excel… But this just hides the error… The error still exists and needs to be resolved!

 

The excel 365 package is not suitable for this project which is why I am not using the options on this package.

 

Is there any plans to add formatting options to Excel Advanced package? Or anyone aware of other workarounds?


2 replies

Userlevel 3
Badge +8

Hi @THOLLAND,

Please review if this action helps, this is for online version of excel. 

https://docs.automationanywhere.com/bundle/enterprise-v2019/page/enterprise-cloud/topics/aae-client/bot-creator/commands/using-format-cell-action.html

 

Badge +1

Hi @ravi.pothana, unfortunately it does not help for this project, as the bot is not built on Office 365 Excel package…

The customer’s IT Security policy prevents Bot Agent from connecting to an online excel session, it’s just not possible in this case. Also it appears I would not be able to access desktop files if using Office 365 Excel, but please correct me if i’m wrong here.

Are there any plans to add cell formatting options to Excel Advanced package? It seems strange having no cell formatting options in an “Advanced” package 😔

Problem 2 is the bigger issue, it is simply not possible to enter number values in to cells (as far as i’m aware) with Excel Advanced - which forces janky workarounds such as setting the cell formula to “=value($variable$)”, which does not produce an ideal result.

It seems like the Excel Advanced package is unfinished/incomplete… It’s missing some key features that everyone uses on Excel, and not everyone is able to utilize Office 365 for their developments, for various reasons, but this results in the inability to replicate and automate certain processes if the customer is unable to utilize Office 365 online packages.

Reply