How to write one value from the array of string per excel sheet?

Help StudioX
,
1

Hi community!
Can somebody help me to resolve my trouble?

  • I have the array of string with three values: [“Julia Jones”," Jane Doe"," Joe Smith"]

  • I have three Sheets inside excel file: Invoice Best Therapy, Invoice Therapist Provider, Invoice therapy practice

  • I want to have each one value from the array in the each sheet at the same cell, like this:

Untitled design
Untitled design800×375 59.9 KB

Every time I use “For Each” or/and “For Each Excel Sheet” activities, data in cell is rewriting and the third value from array saves only.

I will be grateful for any advice

2

Hi @vakabanov1989

Try this:
sheetNames = {"Invoice Best Therapy", "Invoice Therapist Provider", "Invoice therapy practice"}
namesArray = {"Julia Jones", "Jane Doe", "Joe Smith"}

  1. For Each namesArray:

  2. Inside the For Each activity:
    currentIndex = Array.IndexOf( namesArray, currentItem)
    currentSheet = sheetNames(currentIndex)

  3. Use write cell

3

Can you please tell me, how I can define Index correctly?
I cannot assign this

err

4

@vakabanov1989

Try this:

image
image568×697 24.2 KB

image
image776×172 5.68 KB

currentIndex = Array.IndexOf( namesArray, currentItem)
currentSheet = sheetNames(currentIndex)

Pass this values to write cell activity.

5

Hi @vakabanov1989

Please find the below xaml

ExcelProcess.zip (218.6 KB)

O/P Excel:
Input Excel.xlsx (10.0 KB)

I hope it helps!!

6

Hey @vakabanov1989,

Welcome to the UiPath Community,

You can try making the sheet name and array to a dictionary where the sheet name is the key and the name array is the value

Inside a for each loop, use the write cell activity and paste the current item key in sheet name, current item value as value and cell as A1

Please find the XAML and Output sheets attache below
XAML :-
Write_ArrayValues_To_MultipleSheets.xaml (8.0 KB)

Output Sheet :-
MultipleSheet.xlsx (8.8 KB)

7

@vakabanov1989

Have you tried it?

8

@vakabanov1989

If you found the solution for your question. Please mark it as solution to close the thread.

Regards,

9

Hi @vakabanov1989

For Each sheetName in SheetNames
    Excel Application Scope
        Use Excel File: "path/to/your/excel/file.xlsx"
        Worksheet: sheetName

        For Each name in NameArray
            Write Cell
                Worksheet: sheetName
                Value: name
                Cell: "A1"  // Change this cell reference as per your requirement

        End For Each

    End Excel Application Scope
End For Each
10

Hi @vakabanov1989

Follow the process steps to write the each value in the each sheet in excel
=> Take Use excel file activity and provide the Excel path.
=> Inside Use excel file activity insert the Assign activity and crate a count variable which is integer datatype
- Assign → Count (Integer datatype) = 1
=> After assign activity Use for each excel sheet activity to iterate the sheets in the excel file
=> Inside For each Insert If condition to check the Sheet name and count is correct.
- If → CurrentIndex.ToString.Equals(Count.ToString)
=> Inside the If condition Use write cell activity to write the array value in to the specific cell in the current sheet.
- Write cell activity - What to write Field → Arr_var(Count-1).ToString (which give first index value of array)
- Where to write Field → CurrentSheet.Cell(“A1”)
=> End the If after the If place an assign activity to increment the count value.
- Assign → Count = Count+1.

Check the below workflow for better understanding →
Browser_Practice.xaml (16.1 KB)

Browser_Practice
Browser_Practice880×1613 113 KB

Note - In the Excel File change the Path of your excel file.

11

Thank you! It works and thank you for explanation how to define index!

12

This method works! I’ve never had to use counters in UiPath before this moment. Thank you for your example!

1 Like
13

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