Copy excel data

(1) Share

Report

Posted on by T24k

Hi All,

I have Excel files on my desktop folder. I need to copy the data from column B in file1 to column D in file2. There is already some data in file2, and I need to overwrite it with the data from column B of file1. Kindly guide me to how to achieve this using PAD. Thanks in advance!

Categories:

Power Automate Desktop

All responses (4)

Answers (0)

Sort by

Suggested answer

VishnuReddy1997 2,666 Super User 2026 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
Hey Hi,

You can achieve this in two methods.Please find the solutions.

Method1:

If you want to write the Data row by row you can follow the method1.

Please find the solution.

Code:

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Desktop\\Power Automate Desktop\\Projects\\BOM Costing\\Archieve\\ExcelFile1.xlsx''' Visible: True ReadOnly: False Instance=> File1 Excel.ReadFromExcel.ReadAllCells Instance: File1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData1 Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Desktop\\Power Automate Desktop\\Projects\\BOM Costing\\Archieve\\ExcelFile2.xlsx''' Visible: True ReadOnly: False Instance=> File2 SET RowValue TO 2 LOOP FOREACH CurrentItem IN ExcelData1 Excel.ClearCellsFromExcel.ClearSingleCell Instance: File2 SingleCellColumn: $'''D''' SingleCellRow: RowValue Excel.WriteToExcel.WriteCell Instance: File2 Value: CurrentItem[0] Column: $'''D''' Row: RowValue END

Method 2;

If you want to write all the Data at once to the D column of file 2 you can follow the method2.

Code:

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Desktop\\Power Automate Desktop\\Projects\\BOM Costing\\Archieve\\ExcelFile1.xlsx''' Visible: True ReadOnly: False Instance=> File1 Excel.ReadFromExcel.ReadAllCells Instance: File1 ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData1 Variables.RetrieveDataTableColumnIntoList DataTable: ExcelData1 ColumnNameOrIndex: 1 ColumnAsList=> ColumnAsList Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Desktop\\Power Automate Desktop\\Projects\\BOM Costing\\Archieve\\ExcelFile2.xlsx''' Visible: True ReadOnly: False Instance=> File2 Excel.GetFirstFreeRowOnColumn Instance: File2 Column: $'''D''' FirstFreeRowOnColumn=> FirstFreeRowOnColumn Excel.ClearCellsFromExcel.ClearCells Instance: File2 StartColumn: $'''D''' StartRow: 2 EndColumn: $'''D''' EndRow: FirstFreeRowOnColumn - 1 Excel.WriteToExcel.WriteCell Instance: File2 Value: ColumnAsList Column: $'''D''' Row: 2

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy
Suggested answer

NathanAlvares24 1,714 Moderator on at

Like
a
(2)

Report
Copy link

Link copied!
Hi T24k !

You can also achieve using VB Scripting.

Example:

Let's say I have this data in my excel files.

Excel File 1:

Excel File 2:

Now I want to copy the data from file 1 of column B to file 2 of column D. I am assuming that the first row is a header row. So use this script:

Vb script code:

' Define variables for file paths Dim objExcel, objWorkbook1, objWorkbook2 Dim sourceRange, destinationRange Dim file1, file2 file1 = "%filepath1%" file2 = "%filepath2%" ' Create Excel application object Set objExcel = CreateObject("Excel.Application") ' Open the first workbook (file1) Set objWorkbook1 = objExcel.Workbooks.Open(file1) ' Open the second workbook (file2) Set objWorkbook2 = objExcel.Workbooks.Open(file2) ' Define the source range from column B in file1 Set sourceRange = objWorkbook1.Sheets(1).Range("B2:B" & objWorkbook1.Sheets(1).UsedRange.Rows.Count) ' Define the destination range starting from column D in file2 Set destinationRange = objWorkbook2.Sheets(1).Range("D2") ' Copy data from source to destination destinationRange.Resize(sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value ' Save the changes in the second workbook (file2) objWorkbook2.Save ' Close both workbooks and quit Excel objWorkbook1.Close False objWorkbook2.Close True objExcel.Quit ' Clean up Set objExcel = Nothing Set objWorkbook1 = Nothing Set objWorkbook2 = Nothing Set sourceRange = Nothing Set destinationRange = Nothing

Output:

Full code of the flow:

SET filepath1 TO $'''C:\\Users\\Username\\Desktop\\Excel copy data\\Book1.xlsx''' SET filepath2 TO $'''C:\\Users\\Username\\Desktop\\Excel copy data\\Book2.xlsx''' @@copilotGeneratedAction: 'False' Scripting.RunVBScript.RunVBScript VBScriptCode: $'''\' Define variables for file paths Dim objExcel, objWorkbook1, objWorkbook2 Dim sourceRange, destinationRange Dim file1, file2 file1 = \"%filepath1%\" file2 = \"%filepath2%\" \' Create Excel application object Set objExcel = CreateObject(\"Excel.Application\") \' Open the first workbook (file1) Set objWorkbook1 = objExcel.Workbooks.Open(file1) \' Open the second workbook (file2) Set objWorkbook2 = objExcel.Workbooks.Open(file2) \' Define the source range from column B in file1 Set sourceRange = objWorkbook1.Sheets(1).Range(\"B2:B\" & objWorkbook1.Sheets(1).UsedRange.Rows.Count) \' Define the destination range starting from column D in file2 Set destinationRange = objWorkbook2.Sheets(1).Range(\"D2\") \' Copy data from source to destination destinationRange.Resize(sourceRange.Rows.Count, sourceRange.Columns.Count).Value = sourceRange.Value \' Save the changes in the second workbook (file2) objWorkbook2.Save \' Close both workbooks and quit Excel objWorkbook1.Close False objWorkbook2.Close True objExcel.Quit \' Clean up Set objExcel = Nothing Set objWorkbook1 = Nothing Set objWorkbook2 = Nothing Set sourceRange = Nothing Set destinationRange = Nothing''' ScriptOutput=> VBScriptOutput ScriptError=> ScriptError

I hope this helps.

Regards,

Nathan Alvares

Junior RPA/AI Developer

-------------------------------------------------------------------------------------------------------------------------------------------------------

If you found this helpful, check out my LinkedIn and YouTube for more cool stuff:

🔗 LinkedIn
📺 YouTube

And if this solved your problem, don't forget to smash that 👍 "Thumbs up" and give a hearty ✅ "Accept my solution." Your future self and others will thank you! 😉✨
T24k 32 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Vishnu & Nathan, Thanks for your reply. I created this post. but why i am not able to to see "Accept as solution" button after they updated this MS forum?
VishnuReddy1997 2,666 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hey hi,

Just try after some time it may show you.

Regards,

Vishnu Reddy