How to write variable value in next available cell in an excel column?

Hi @Rammohan91,

When I replace “A1” with “A”+InvalidList.ToString I am getting the below error.

18.3.1+Branch.master.Sha.4c05f670b311e90ee097c589605b399e9bee4874

Source: Write Cell

Message: The range does not exist.

Exception Type: UiPath.Excel.ExcelException

An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
UiPath.Excel.ExcelException: The range does not exist. ----> System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A03EC
at Microsoft.Office.Interop.Excel._Worksheet.get_Range(Object Cell1, Object Cell2)
at UiPath.Excel.WorkbookApplication.WriteCell(String range, String data)
— End of inner ExceptionDetail stack trace —
at UiPath.Core.Activities.ScopeActivity.OnFaulted(NativeActivityFaultContext faultContext, Exception propagatedException, ActivityInstance propagatedFrom)
at System.Activities.Runtime.FaultCallbackWrapper.Invoke(NativeActivityFaultContext faultContext, Exception propagatedException, ActivityInstance propagatedFrom)
at System.Activities.Runtime.FaultCallbackWrapper.FaultWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

Here is a sample workflow. You may not have initialized your variable with 1.

Main.xaml (7.3 KB)

Let me know it it helps.

Thanks,
Rammohan B.

Hello,

Just reading your thorough comments in this post. How to write variable value in next available cell in an excel column?.

I am in need for the same thing, do you know where to find a video on this?

I have a spreadsheet, with a column of data in it but unable to make new entries populate the empty cells. I found your instructions detailed but not exactly what I need. I am new to UiPath (obviously).

I understand I need to convert the data to a Data Table Variable, but not sure how to identify the empty cells and enter another variable.

  1. Create Data Table Variable
  2. If cell is empty, write line.
    It seems easy but struggling.

Appreciate any help/direction.

Thank you.

Hi @Steven_McKeering

I’m sure you can find some instructional UiPath videos, however, I can’t say they are any good. (maybe I’ll make good ones in the future :thinking:
-if you connect to my linkedin, I have been known to provide additional help-

I’m a little unsure of your requirements that you are trying to solve for. I’m assuming you are trying to process line items where a specific column is empty, which is useful to do because when you start a job, most of the time, you will only want to process the items that failed previously whether that is checking if a file exists already or if a status column identifies this failure.

In the case where you would just be processing certain items and updating the entire spreadsheet, I would suggest learning some vb net linq methods to accomplish this. However, you can also consider using the Filter data table activity or just simply using some basic If activities so certain rows are skipped.

Simply put, you would just pull in the items where the status equals or contains certain words, which makes it an array of data rows and run it through a For each. Inside the Foreach, you then output or update a value to the empty column. You then either Write the Range back to the Excel file in the For each or after the For each.

Also, use the Add Data Column activity if the column has a chance to not exist yet

Here is a quick example: example.xaml (10.8 KB)

EDIT: also add .Trim next to .ToString, which was a mistake. You need the .Trim so it can remove the spaces if a cell has a space but nothing else.

1 Like

Hi @Rammohan91

Thanks for sharing a sample workflow. In your sample workflow the “StringArray” is a Object. But, in my case “ChildOutput” is IEnumerable. I am using “Find Children” activity to get drop down list and store the values into a variable (“ChildOutput”). And when I am trying to looping the ChildOutput I am using TypeArugument as IEnumberable. If I try to change TypeArugument as Object or any other type I am getting workflow error.

Let me explain you what I am trying to do.

  1. I am going to a website.
  2. Getting the drop down values and storing them into a variable (ChildOutput) using Find Children activity.
  3. Using For Each activity I am trying to store all the drop down list values into a excel sheet.

I see.

So is your Get Attribute working then?

If so, it looks like you just need to know how to output the data to the spreadsheet.

I would recommend building a data table, instead of using Write Cell. However, I understand if you want to take that approach so let me know if you prefer the Write Cell.

To build the data table, either Read Range of an existing file, or use the Build Data Table activity. Store the output to a DataTable variable. Then, all you need to do is use the Add Data Row activity to add the items. You then can use the Write Range activity and output the entire table overwriting the existing data, so it updates it with the new rows.

When you use the Add Data Row, change the ArrayRow Property. So each item in the table is like an array. For example if you only have 1 column, then you do {invalidList}

Regards.

Hi @ClaytonM,

Yes my Get Attribute is working and even I am able to loop through “ChildOutput” successfully. But, when I am trying to write the output values to excel sheet, I am facing problem. Every time the loop is writing the output value into the first cell of excel its not increasing or moving to next cell and writing the second value into the second cell and so on…

Hi @ClaytonM

I am trying to use Build Data Table activity but I want to know what is the input for Build Data Table activity. I should provide Get Attribute output as input to Build Data Table activity?

Hi @Bhaskar_Mukka
Use the Build Data Table to initialize an empty Table to add all the items to, so place that activity somewhere at the beginning outside the For each.

To add the item to that table, use the Add Data Row activity, and change the ArrayRow property to {invalidList.ToString}, with the data table variable which was used in the Build Data Table in the DataTable property of the Add Data Row activity.

Hope I am clear.

Regards.

1 Like

@ClaytonM, Thanks for the support. It worked :slight_smile:

@Rammohan91 Thanks for your support :slight_smile:

Hi @ClaytonM

I am able to store all the dropdown list values (including “Select”) into my excel sheet but how can I ignore or skip the “Select” from the list. Please look into the screenshot for better understanding.

Drop Down List Screenshot:

image

My Excel Sheet Screenshot:

image

Hi.

You can probably use the .Skip() in the For each
Like:

ForEach item in ChildOutput.Skip(1)

The above will just skip the first item.

You can also just place in an If activity so it skips any “aaname” that equals “Select”
For example:

Get Attribute "aaname"
If activity: invalidList.Trim.ToUpper <> "SELECT"
    Add Data Row

That will skip anything that equals Select

Regard.

1 Like

Thanks @ClaytonM it worked :slight_smile:

Hi,

I Have to read two file names from web page and write to excel sheet.but every time it is overwriting previous one.please,help me to solve that.

Hi @Srilatha,
Hope this could help you.
Have you tried to use append range activity?
https://activities.uipath.com/docs/append-range
Best Regards,
Susana

1 Like

Hi,

In Append range Activity,it is showing Data table .but iam using write cell after get text from web page.I dont know how to use it? sorry,iam new to UI path.Please,help me with it.

Hello @Srilatha,

If you know in which cells the fileNames have to be written then you can indicate the specific cell in the range field of the write cell activity.

  • Range - The Excel cell or range that you want to write to. If a value already exists at the specified coordinates, it is overwritten. Only string variables and strings are supported.

image

Other possible solution could be built a dataTable and insert all the fileNames you have to write in the excel file. Then you only need to use a write range activity to fill the file.test_10585.xlsx (7.6 KB)

Do you know UiPath Academy? It Is the best way to start with UiPath :slight_smile:

Best Regards,
Susana

1 Like

Thanks @Susana,You helped me a lot.

I have other Query.

How to compare two columns values in two different sheets in excel and update the status whether they are equal or not in first sheet.please,help me .

Refer the link , it will help you

Next time make sure , you create new topic for the new issue as it is not relevant to the discussion going on here in this topic category