Write cell activity using offset

Help Studio
, ,
1

Hi there, I am trying to write value into a cell that is offset by x amount of collumns to the right of a specific variable (SKU) from a dialogue. I am struggling a bit with this write cell task - any recommendation?

image
image1265×1766 83.7 KB

2

Hi @namy77 ,

Is it possible for you provide us with an Input data representation and the Expected Output data representation either in the form of a Screenshot or excel sheets ?

This would help us better to analyse your case and provide you with the appropriate suggestions.

3

Sure - let’s say the user type in “A2” - UiPath should go into this excel file, look for A2 in range A (found it at CellA3) and then write on B3 “Bot1” - that was my thought for Offset (0,1)
image

4

@namy77 ,

In that case, maybe a combination of Find/Replace Value and Write Cell activities along with the Manipulation of the Incremental value (From A to B), it should be able to achieve what you are looking for.

  1. Using the Modern Excel Activities wit Excel Process Scope and Use Excel File, we get the reference for the Excel data in the instance variable called Excel. We can use this to indicate the sheet that we are working on and then perform the required operations with it’s set of activities.

  2. Next, we can use Find / Replace Value activity to find out where is the value (from user) stored in the Excel sheet. We will be able to get the Cell Address (A1 or B3 or C3 etc…) from this activity.
    Configuration upto now :

    image
    image875×467 24.4 KB

  3. Next, After we receive the Cell Address, we would need to extract the Cell Name and then apply the offset (1, 2, 3) to it, to get the Cell Reference as to where you would want to write the value. The manipulation can be done like below :

OffsetRef = char.ConvertFromUtf32(Asc(Regex.Match(CellAddr,"[A-Z]+").Value)+1)

Here, OffsetRef is a variable of type String, and CellAddr is the output of Find/Replace Value activity.

  1. Next, we can write the Output to the OffsetRef generate using Write Cell Activity in the below way :
    The Where to Write Expression value is below :
Excel.Sheet("Sheet1").Cell(OffsetRef+Regex.Match(CellAddr,"\d+").Value)

Rest of the Implementation is as shown in the image below :

image
image711×339 14.4 KB

Let us know if you were able to implement it.

5

Hi there thanks @supermanPunch - it seems to work until error BC30451: ‘Regex’ is not declared. It may be inaccessible due to its protection level?

6

@namy77 ,

Apologies, For this we would require you to Import the RegularExpressions namespace from the Imports Panel.
image

7

Thanks, that works!
Couple of follow ups - and you could just point me to the topics/resources.

  1. What if there are duplicates in this range? Should I design an error message?
  2. How do I generate an after-action-report or a log of these activities for the user afterwards?

Thanks much!

8

@namy77 ,

This is currently not possible with the help of the activity, but we can replace it with the Linq Expression to retrieve all possible references. Then Loop over the references retrieved and perform the same remaining operations. But this would also require us to read the excel sheet as a datatable.

Not quite sure of what is the expectation here, but maybe using Write Text file or Append Line Activities we should be able to log the required message to a text file.

9

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