A way to have only part of a link as an If activity condition

Help Studio
, ,
1

Hi all, I currently have a bunch of links in the cells of an excel sheet that look roughly like this:
=‘[Monthly Report - 2023(3rd Day)_group_Feb.xlsx]PL Old_detail’!$AR$7

I’m trying to use an If activity inside a For Each Excel Row to only change the month of the link.
I currently have this as the condition of the If activity:
“=‘[Monthly Report - " + CurrentYear + “(3rd Day)group” + PreviousMonth + ".xlsx]PL Old_detail’!$AS$7”

However I don’t want the condition to look for the cell number in the link(i.e. the “AS$7” portion). Is there a way to make it so that it only searches for the words before the cell number?

2

Hi @Lochagos ,

There are a few options in my mind:

  1. If possible could you revise the formula in your excel sheet rather than:
    =‘[Monthly Report - 2023(3rd Day)_group_Feb.xlsx]PL Old_detail’!$AR$7
    make it into:
    =‘[Monthly Report - {{Year}}(3rd Day)group{{month}}.xlsx]PL Old_detail’!$AR$7
    therefore in your For Each Row you could replace the {{year}} and {{month}} with your variable value.
  2. If there’s a specific set pattern for your formula, such as the !$AS$7 always start at row 7, you could utilize the Index variable in ‘For Each Row’ activity to increment the row number each row
  3. You may even use macro instead by using ‘Invoke VBA’ activity, then pass your UiPath Variables for year and month to the activity, then change the formula using Macro VBA.

Regards,

3

Hi, I’m not sure if option 1) could work as the file I’m working on was copied over from a previous older file, as such all the links are already set in the file, and I would need to replace probably hundreds of cells with the new formula.

I’m not sure if 2) would work either as in the excel file that I’m linking to, there are certain values that I don’t want to be transferred into the current file, along with some blank cells that I imagine would cause issues if I use an auto increment.

It seems to me that 3) might be the best looking solution? However I have never used VBA coding before, so will it still be able to do what I want it to, even with all the caveats I listed above?