transpose excel copied data

Welcome Forums General PowerShell Q&A transpose excel copied data

Viewing 1 reply thread
  • Author
    Posts
    • #174751
      Participant
      Topics: 1
      Replies: 0
      Points: 12
      Rank: Member

      Hello,

      I use this code to copy, paste data from excel to export in a csv file.

      The probleme i have is the data I copy is a range of cells in one line.

      So when I paste and export to a csv, the result in the csv is one one line

      Here is what I have in the csv:

      user1, user2, user3, user4

      But I would like to have:

      user1

      user2

      user3

      user4

       

      here is the code I use.

      thanks for Your help

      $Range_GG="D1:AK1"

      $xlCSV=23

      $Excel = New-Object -comobject Excel.Application
      $Excel.Visible = $False
      $Excel.displayalerts=$False

      $Workbook = $Excel.Workbooks.Open($ExcelFileName)
      $WorkSheet = $WorkBook.Worksheets.Item(1)
      $WorkSheet.activate()
      $Range2 = $WorkSheet.Range($Range_GG)
      $Range2.Copy()
      $Worksheet = $Workbook.Worksheets.item(2)
      $Range2 = $Worksheet.Range($Range_GG)
      $Worksheet.Paste($range2)
      $Worksheet.SaveAs($CSVfilename2,$xlCSV)
      Get-Content -Path "$CSVfilename2" | Out-File -FilePath "$CSVUnifilename2" -Encoding Unicode
      $Excel.Quit()
      If(ps excel){kill -name excel}

    • #174805
      Senior Moderator
      Topics: 8
      Replies: 1141
      Points: 3,927
      Helping Hand
      Rank: Community Hero

      you should definitely checkout ImportExcel module. I believe It can help you here.
      https://www.powershellgallery.com/packages/ImportExcel/6.5.1

Viewing 1 reply thread
  • The topic ‘transpose excel copied data’ is closed to new replies.