UiPath is reading old value from config excel file

@Palaniyappan @prasadsatish Need your help on below:

There is a formula in config file =Text(Today(),“MM\dd\yyyy”) which should always give me today’s date in text format when the bot runs.
Note: Excel config file is stored on a shared location and not in the .nuget package.

It is working perfectly fine in Dev and Testing machines, but as soon as we made the bot live in production this issue started.

1st day of production run was smooth: before running the bot i opened the config file made few production related changes, saved the file and closed it. I ran the bot and bot picked the correct Today’s date.

2nd day of production run: I ran the bot directly without opening the excel file and was shocked to see that bot picked the wrong date as of yesterday (1st day of production run). I opened the excel file and in the excel file the formula is correct and displaying the correct value. I saved the excel file and closed and re ran the bot, this time bot ran and picked the correct value.

3rd day of production run: Same issue happened, again opened the config file date value is correct, saved the config file and closed it. Re ran the bot and it ran successfully.

Now every time I want to run the bot i have to open the config file - save it and close it for the bot pick the correct date value from the forumla.

I have checked multiple times that the bot is pointing to the correct config file and all the configuration is correct.
We have the same logic to pick the date from config file in other bots/process also and it is working fine there. It is just we are facing this issue only on this particular machine.

@kashif.ansari417

Welcome to the community

May I know why not use now.tostring("MM/dd/yyy") directly in the code instead of getting todays date from config?

That looks wierd but if you still want the excel one to work before reading try opening the excel using excel application scope and save and close that should make the formula work…

Check the settings in excel on the machine …go to excel options and check the calculation and recalculation options if they are automatic

Cheers

Hello @kashif.ansari417
There are 2 options you go with

  1. Use Excel scope to read the config file
  2. Use Now.ToString("MM\dd\yyyy") in you code instead of referring from Config file

Note: If there are multiple excel formulas in Config, which are in use. I recommend you to stick with Excel Scope option.

There can be some instances where i will need to pass the old date instead of Today’s date from the config (Business requirement).

I am looking for a solution which does not require the code change, for code change we will have to go through the whole RPA life cycle to deploy the change in production.

I will try the Calculation and Re Calculation setting. Can you please help me where can i find these settings.

Thanks

@kashif.ansari417

For that you can use an asset…and when you need a different date pass a value in asset else pass asset as NA and when it is NA use the current date else use the input date from asset…

This way you need not change anything in the code as well. You only need a if condition to check if asset is NA or not

For options go to excel → options → formula → you will find a calculation formula tab …can change the values to automatic there or as required

Cheers

Calculation wont happen (manual or automatic) until you open the excel file. So if you are already using the workbook activity in project change it to excel scope or if you using excel scope check the calculation settings in Excel app for the users. It should be automatic.
More info at here

Asset logic should work. Thanks.

But for now, I am trying to find the root cause of this issue. Haven’t faces this issue before. Any idea on this?

Checked the Excel setting and everything is Automatic.

@kashif.ansari417

These look proper…

Try using application scope for some reason the formula updates are stopped…check in advanced settings if auto update is enabled

Cheers

This is how Formula section looks like in advance:

Thanks

I will check this.

Thanks

@kashif.ansari417

Then your go would be to use excel application scope before reading ao that the formulae gets updated

Cheers

1 Like

Then what you do is put a value in the config file which tells it which to do, for example the values could be simply Today or Old Date.

Then you use an If to check which value is in that Config, and your Then and Else blocks do the calculation in your code.

1 Like

Hello @kashif.ansari417

I hope while reading the excel the formula is not getting updated for the todays date and thats why you are getting the yesterday’s date.

So you have to either refresh the excel using vb script or you can open the excel using Use Excel file activity. I hope that can solve your issue.

Thanks

1 Like

Thank you all for quick response and solutions.

1 thing that is bothering me is why the same logic is running on other machines and just having issue only on this machine and what is different in this machine that is causing the issue.
I am trying to find the root cause for this, but for the time being I have no other choice other than to apply the alternate fix in the code.

Thanks once again for the response.

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.