Merge and copy all data in excel

PowerShell Help
1

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:

Param(
$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)
$worksheet.activate()
$range = $WorkSheet.Range($range1).EntireColumn
$range.Copy() | out-null
$Worksheet = $Workbook.Worksheets.item($worksheet2)
$Range = $Worksheet.Range($range2)
$Worksheet.Paste($range)
$workbook.Save()
$Excel.Quit()

2

You might take a look at the great module from Doug Finke ImportExcel. That could make your task much easier I think.

3

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.

4

Thanks for the response. How to achieve this using powershell?

5

Unfortunately cannot install any untrusted modules on the system, hence looking for a way to achieve this using inbuilt powershell capabilities.

6

Any ideas on the above requirement?