Excel automation help...in copy paste

Help Activities
, ,
1

i want to copy a column(no header) from sheet1 of dt1 and want to paste it on sheet1 of another excel file from range B5. for this i am using copy/paste activity so whether i have chosen correct activity? if yes then want should i write in “source” and “destination” column?

2

Are you using modern activities? If yes, then you could use the updated Excel activities to achieve this. Also, does the B5 range always constant or does it change based on the available data?

3

@Hemant_Deshmukh

-Use the “Read Range” activity to read the data from the source Excel file. (DT1)
SheetName: The name of the sheet from which you want to copy the data.
Range: Specify the range of the entire column without headers.

Use the “Write Range” activity to write the data to the destination Excel file.

SheetName : The name of the sheet in the destination Excel file where you want to paste the data
Range : Specify the starting cell where you want to paste the data (B5)
DataTable : DT1

Cheers…!

4

Step 1: Use the “Excel Application Scope” activity to open the source and destination Excel files.

Step 2: Inside the “Excel Application Scope,” use the “Read Range” activity to read the data from the source file (dt1) and store it in a DataTable variable (let’s call it dtSource).

Step 3: After reading the data, you can use the “Copy Paste Range” activity.

Step 4: In the “Source” field, specify the range of the column you want to copy from dtSource.

For example, if you want to copy data from column B (excluding the header), you can use

“dtSource.AsEnumerable.Skip(1).Select(Function(r) r(1).ToString).ToArray()”

(assuming the column index is 1).

Step 5: In the “Destination” field, the cell where you want to paste the data in the destination Excel file. you can use “B5” as the destination.

5

Hello @Hemant_Deshmukh

  1. Use Excel Application Scope (for the first Excel file)

    WorkbookPath: “PathToYourFirstFile.xlsx”

  2. Read Range (to copy the column from dt1)

    SheetName: “Sheet1”,
    Range: “B2:B100”, // Adjust the range as per your data
    DataTable: dt1

  3. Use Excel Application Scope (for the second Excel file)

    WorkbookPath: “PathToYourSecondFile.xlsx”

  4. Write Range (to paste the column into the second file)

    SheetName: “Sheet1”,
    Range: “B5”, // Starting cell for pasting
    DataTable: dt1 // Use the same DataTable as source

  5. Close Excel Application Scope (for both files)

Thanks & Cheers!!!

6

Hi @Hemant_Deshmukh

You can try like this

image
image882×1481 64.2 KB

Hope this helps!!

1 Like
7

Hi @Hemant_Deshmukh

Yes Hemant, By Using copy/paste activity you can copy a column values from sheet1 of dt1 to paste in sheet1 of another excel file from range B5.

In Source: Excel.Sheet(“Sheet1”).Range (“C:C”)
Destination: Excel.Sheet(“Sheet2”).Range(“B5”)

8

@Hemant_Deshmukh

1.Use the Read Range activity to read the data from sheet1 of dt1 into a DataTable
2.Use a Build Data Table activity to create a new DataTable
3.Use a For Each Row activity to loop through each row in dtSource, excluding the header row. You can use the Skip method to skip the first row (header). Inside the loop, use the Add Data Row activity to add each row of data to dtDestination.
4.After the loop, use the Write Range activity to write the dtDestination DataTable into the destination Excel file’s sheet1, starting at cell B5.

chees…!

9

@Hemant_Deshmukh

yes you can use

in the source indicate the column which you want to indicate
in the destination indicate the range where you need to write

image
image741×731 28.6 KB

if you doesn’t want header of that column enable

image

10

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