September 26, 2019 at 12:42 pm #179862
I am trying to copy and merge cells in the same excel workbook from one sheet to another. The requirement is as below:
Sheet1 has the following columns=A,B,C,D,E,F,G,H,I,J,K,L
Copy Columns F,G,D,E,K,B from Sheet1 to Sheet 2 in columns A,B,C,D,E,F
Then merge the cells A,B,C,D,E,F in Sheet 2 to column G in sheet2
Since I have to do this same on many sheets, I am looking to do it through powershell.
I have the below code so far, however it is only copying the columns in the same order as Sheet1:
$path = "C:\Users\Book2.xlsx",
$worksheet1 = “Sheet1”,
$range1 = “B1:A1”,
$worksheet2 = 2,
$range2 = “A1”
) #end param
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$Workbook = $excel.Workbooks.open($path)
$Worksheet = $Workbook.WorkSheets.item($worksheet1)
$range = $WorkSheet.Range($range1).EntireColumn
$range.Copy() | out-null
$Worksheet = $Workbook.Worksheets.item($worksheet2)
$Range = $Worksheet.Range($range2)
September 26, 2019 at 12:54 pm #179868
September 26, 2019 at 1:22 pm #179886ParticipantTopics: 10Replies: 1385Points: 1,527Rank: Community Hero
If you are going to be running the process from an administrative workstation or something where you fully control the environment, there is a Excel accelerator module called ImportExcel. Excel scripting can be a bit painful because it's using an old COM object, but normally I would start using the Macros to show what Excel is doing step-by-step, but this will be in VBA, not Powershell. Then you just need to convert the steps and it should get you fairly close to what you need, enable Developer tools and, start a Macro and do the steps manually to give a scripting guide.
September 26, 2019 at 1:35 pm #179901
Thanks for the response. How to achieve this using powershell?
September 26, 2019 at 10:54 pm #180015
Unfortunately cannot install any untrusted modules on the system, hence looking for a way to achieve this using inbuilt powershell capabilities.
September 29, 2019 at 11:58 pm #180363
Any ideas on the above requirement?
- The topic ‘Merge and copy all data in excel’ is closed to new replies.