Merge and copy all data in excel

Welcome Forums General PowerShell Q&A Merge and copy all data in excel

This topic contains 5 replies, has 3 voices, and was last updated by

 
Participant
3 weeks ago.

  • Author
    Posts
  • #179862

    Participant
    Topics: 12
    Replies: 55
    Points: 241
    Rank: Participant

    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()

  • #179868

    Participant
    Topics: 1
    Replies: 1552
    Points: 2,700
    Helping Hand
    Rank: Community Hero

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

  • #179886

    Participant
    Topics: 8
    Replies: 1213
    Points: 756
    Helping Hand
    Rank: Major Contributor

    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.

    https://support.office.com/en-us/article/show-the-developer-tab-e1192344-5e56-4d45-931b-e5fd9bea2d45

  • #179901

    Participant
    Topics: 12
    Replies: 55
    Points: 241
    Rank: Participant

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

  • #180015

    Participant
    Topics: 12
    Replies: 55
    Points: 241
    Rank: Participant

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

  • #180363

    Participant
    Topics: 12
    Replies: 55
    Points: 241
    Rank: Participant

    Any ideas on the above requirement?

You must be logged in to reply to this topic.