Copying formatting from one workbook to another?

Help Activities
, ,
1

Hi everyone,

I’m moving data from multiple Excel documents into one master workbook. The workflow overview is:

  1. Read data from an input workbook,
  2. Match data from the input workbook with a worksheet in the master workbook,
  3. Copy the data into the master workbook, on the correct worksheet in the correct cell range,
  4. Repeat this hundreds of times

I’ve been asked whether the process could copy the source formatting from the input workbook, when copying into the master workbook. This would be a big win in terms of time-savings.

What’s the easiest way to accomplish this? I currently extract raw values from the input workbooks.

Thanks

2

@rpa_jay

Use copy paste range qctivity that would copy the formatting as well

Cheers

3

You can use the Excel Application Scope, within the scope you can try using the Copy and Paste methods to copy the values and formatting from input workbook to master workbook.

4

Hope the below steps would help you resolve this

  1. Use a assign activity like this

arr_files = Directory.GetFiles(“your folderpath”)

Where u get array of filepath in variable arr_files which is of type array of string

Then Use a FOR EACH activity and pass the above array as input and change the type argument as string

Inside the loop use “Excel Application Scope” or “Use Excel” activity to work with both the input workbook and the master workbook.

  1. Within the scope, use the “Read Range” activity to read data from the input workbook and get the output

  2. Store this data in variables.named dt

  3. Use the “Write Range” activity to write the data to the master workbook. Make sure to enable the “Add headers” option.

  4. To retain the source formatting, you can use the “Copy Paste Range” activity to copy the entire range (including the format) from the input workbook and to paste it into the corresponding location in the master workbook.

  5. This loop will do this process for all your input workbooks.

By using the “Copy Paste Range”, you can copy both the values and the source formatting from the input workbooks to the master workbook. L

Cheers @rpa_jay

5

Hello @rpa_jay

For Each inputWorkbook in ListOfInputWorkbooks
Excel Application Scope (for the input workbook)
Read Range (to extract data and formatting)

        Output: dtInputData
    
        Get Workbook Sheets
    
        Output: inputWorksheets
    
Implement matching logic to find the correct target worksheet and cell range in the master workbook.

Excel Application Scope (for the master workbook)

    Write Range (to paste data and formatting)
    
        Input: dtInputData
        WorksheetName: targetWorksheet
        CellRange: targetCellRange
        Preserve format: True

Thanks & Cheers!!!