Merge and copy all data in excel

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

Viewing 5 reply threads
  • Author
    Posts
    • #179862
      Participant
      Topics: 13
      Replies: 58
      Points: 268
      Rank: Contributor

      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: 2
      Replies: 1707
      Points: 3,428
      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: 10
      Replies: 1385
      Points: 1,527
      Helping Hand
      Rank: 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.

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

    • #179901
      Participant
      Topics: 13
      Replies: 58
      Points: 268
      Rank: Contributor

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

    • #180015
      Participant
      Topics: 13
      Replies: 58
      Points: 268
      Rank: Contributor

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

    • #180363
      Participant
      Topics: 13
      Replies: 58
      Points: 268
      Rank: Contributor

      Any ideas on the above requirement?

Viewing 5 reply threads
  • The topic ‘Merge and copy all data in excel’ is closed to new replies.