Write merged cell to another excel in Sharepoint

Help Studio
, ,
1

Hello,

I need to take an Excel cell that is generated from a VBA macro (in a local excel file) and write it to a sharepoint excel file. The cell looks like this in the local file :

image
image727×266 3.55 KB

I can get value and the color of the cell but not the “Merged Info”, is it possible to manage this using Microsoft 365 Activites ?

2

What do you mean by “Merged Info”?

If you want to write a value in the merged cell, you can write it in the first address like A1 here

image
image218×124 1.73 KB

3

I want to keep the merged format in the final destination
@ashokkarale

4

Hi @simon.l

If the merged cell is created from a macro, then perhaps you can name the cell at the same time?
Then you dont have to know the exact coordinate of the cell.

Read Cell can reference a named cell.

image
image332×192 4.77 KB

image
image477×278 12.4 KB

Regards
Soren

5

Hello Soren,
The problem is not retreiving the cell, but to keep the merged format from the Excel source to the destination Excel in sharepoint.

6

Hmm I dont think you can.

Perhaps an alternative approach could be to make a dummy template Excel-file with the formatting you require, and then copy/paste this document to you destination after changing it.

I have previously had a similar requirement, where I would include the Excel template/file in the solution and use this as a basis for the end result.

7

@simon.l

got it. In that case we can’t do it with O365 activities.

It’s best to create the file on local drive using macro or other activities and then upload to sharepoint.

8

@simon.l Please follow the below steps:

  1. Use Excel Process Scope

    • Add Use Excel File

    • Select your local Excel file.

  2. Read the merged cell value

    • Use Read Cell

    • Cell: A1

    • Store output in variable, example: mergedCellValue

  3. Get color if required

    • Use Get Cell Color

    • Cell: A1

    • Store in variable, example: cellColor

  4. Open SharePoint Excel

    • Use Microsoft 365 Scope

    • Add Use Excel File

    • Select the SharePoint Excel file.

  5. Write value

    • Use Write Cell

    • Cell: A1

    • Value: mergedCellValue

  6. Merge the same range

    • Microsoft 365 Excel activities may not directly support merge cells.

    • Best option: use Run Script activity with Office Script.

  7. Office Script example
    In SharePoint Excel, create this script:

function main(workbook: ExcelScript.Workbook, textValue: string) {
  let sheet = workbook.getWorksheet("Sheet1");

  let range = sheet.getRange("A1:C1");
  range.merge(false);
  range.setValue(textValue);

  range.getFormat().getFill().setColor("#87CEEB");
  range.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
  range.getFormat().setVerticalAlignment(ExcelScript.VerticalAlignment.center);
}
  1. Call Office Script from UiPath
    Use Run Script activity inside Microsoft 365 Excel.
    Select this script.
    Pass mergedCellValue as input parameter.

Please let me know if it works