How to get the sheets and values from different sheets dynamically

How to get the sheets and values from different sheets dynamically.
1.I have 6 sheets , in that I should get 4 sheet names and write them in 5th sheet
2.I should get few column values from the 4 sheets and put them in 5th sheet

To dynamically handle multiple sheets in Excel and retrieve specific data to consolidate into a fifth sheet using UiPath, you can follow these steps:

  1. *Use ‘Excel Application Scope’**: To open the Excel file and perform operations within it.
  2. Get Sheet Names*: Use the ‘Get Workbook Sheets’ activity to retrieve all sheet names into a list or array of strings.
  3. Filter Sheet Names*: If needed, filter the list to get only the 4 specific sheets you want. Assuming you want the first 4 sheets:

Dim requiredSheets As List(Of String) = allSheets.Take(4).ToList()

  1. Write Sheet Names to the 5th Sheet*: Use ‘Write Cell’ or ‘Write Range’ inside a ‘For Each’ loop to write these sheet names into the 5th sheet.
  2. Retrieve Values from Sheets*: Use a ‘For Each’ loop to iterate through the 4 sheet names.
  • Inside the loop, use ‘Read Range’ to get the required column values from each sheet.
  1. Consolidate Data*: As you retrieve data from each sheet, use ‘Add Data Row’ or directly write the data into the 5th sheet using ‘Write Range’.
  2. Repeat for Each Sheet*: Repeat steps 5 and 6 for each of the 4 sheets to consolidate the data into the 5th sheet.

Example code:
Excel Application Scope (ExcelFilePath)
Get Workbook Sheets → Output: allSheets

For Each sheet in requiredSheets
    If sheet != "FifthSheetName"
        Read Range from sheet -> Output: dataTable
        For Each row in dataTable
            // Extract the needed values from each row
            Write Range/Write Cell to "FifthSheetName"
        Next row
    End If
Next sheet

Note: Replace "FifthSheetName" with the actual name of your 5th sheet. You will need to define the ranges for ‘Read Range’ and ‘Write Range’ activities based on where you want to pull the data from and where you want to write it.

I have to get specific values from specific columns
how to do that

To dynamically get specific values from specific sheets and write them into a 5th sheet in UiPath:

  1. Open Excel Workbook*: Use ‘Excel Application Scope’ to open the workbook.
  2. Get Sheet Names*: Use ‘Get Workbook Sheets’ activity to retrieve all sheet names into an array or list.
  3. Identify Required Sheets*: If you know the names or indices of the 4 specific sheets, identify them in your list of sheet names.
  4. Read Specific Values*: Use ‘Read Cell’ or ‘Read Range’ activities within a loop to read the required values from the identified sheets.
  5. Write Sheet Names*: Use ‘Write Cell’ or ‘Write Range’ to write the 4 sheet names into the 5th sheet.
  6. Write Specific Values*: Similarly, write the specific values from the 4 sheets into the desired location in the 5th sheet…

Excel Application Scope
Get Workbook Sheets → Output: allSheets
For Each sheet in allSheets
If condition to identify the 4 specific sheets
Read Cell/Read Range → From the specific sheet
Write Cell/Write Range → Values into the 5th sheet
End If
Next sheet

note: Make sure to replace placeholder text with actual sheet names, cell addresses, or ranges according to your Excel file’s structure. Use the appropriate logic to identify the 4 specific sheets (e.g., by name or index).

the sheets changes ,so sheets names should be dynamic
after getting the sheets, how to get the values from that sheets? dynamically

lets assume i have sheet1,sheet2,sheet3,sheet4,sheet5,sheet6
I should get sheet3,4,5,6 names and write them as a row items in sheet2
after that ,corresponding to the sheet names which was written in sheet2, I should get some values from the 3,4,5,6 sheets but I have a doubt here like without knowing the sheet names how to get the values dynamically from that sheets , but columns are same but sheet names are different

the sheets changes ,so sheets names should be dynamic
after getting the sheets, how to get the values from that sheets? dynamically

lets assume i have sheet1,sheet2,sheet3,sheet4,sheet5,sheet6
I should get sheet3,4,5,6 names and write them as a row items in sheet2
after that ,corresponding to the sheet names which was written in sheet2, I should get some values from the 3,4,5,6 sheets but I have a doubt here like without knowing the sheet names how to get the values dynamically from that sheets , but columns are same but sheet names are different

  1. Open Excel Application Scope*:
  • Open your Excel workbook.
  1. Get All Sheet Names*:
  • Use Get Workbook Sheets activity to get all sheet names into a list.
  1. Filter Specific Sheets*:
  • Use a condition to filter out the sheet names you don’t need (Sheet1 and Sheet2).
  1. Write Sheet Names to Sheet2*:
  • Use Write Range or Write Cell inside a loop to write the names (Sheet3, Sheet4, Sheet5, Sheet6) in Sheet2.
  1. Read Values from Sheets 3-6*:
  • Use a For Each loop to iterate through the filtered sheet names.
  • Inside the loop, use Read Range or Read Cell to get the specific values from each sheet. Since columns are the same, you can reference them by index or name.
  1. Write Values to Sheet2*:
  • Still inside the loop, use Write Cell or Write Range to write the values to Sheet2.

Example code:
’ Open Excel Application Scope with your Excel file ’

’ Get all sheet names using “Get Workbook Sheets” ’

’ Define a list to hold the sheet names to process ’
Dim sheetNamesToProcess As New List(Of String)

’ Use a For Each loop to iterate through all sheet names ’
For Each sheet In allSheets
’ Skip Sheet1 and Sheet2 and add the rest to the list ’
If sheet <> “Sheet1” AndAlso sheet <> “Sheet2” Then
sheetNamesToProcess.Add(sheet)
End If
Next

’ Write sheet names to Sheet2 ’
Dim rowIndex As Integer = 0 ’ Starting row index for Sheet2 ’

For Each sheetName In sheetNamesToProcess
’ Write the sheetName to Sheet2 ’
Write Cell or Write Range to Sheet2 (e.g., “A” & rowIndex.ToString, sheetName)

' Now read the values from the sheetName '
Dim valuesToWrite As DataTable = Read Range or Read Cell from sheetName

' Assuming you want to write these values to a specific column in Sheet2 '
Write Range to Sheet2 with valuesToWrite at the corresponding position

' Increment the rowIndex for the next sheet name and values '
rowIndex += 1

Next

’ Close the Excel Application Scope ’

Book1.xlsx (37.3 KB)
input is the sheet and output is required one
can you share xaml for this

can you help me with the below’s excel solution…

HI @anjani_priya

Please check the below zip file.

BlankProcess15.zip (102.5 KB)

Bot Output sheet is the output file.

Regards

and I want hyperlinks for the sheets like if we press on sheet3 then sheet3 should open like that all sheets hyperlinks should be created…
can you help with that

Hi @anjani_priya

Hope you got the solution for your query. If yes please mark the solution to close the loop and if you have any other new query then please create new query.

Happy Automation!!

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