Copy-ExcelWorksheet does not maintain the source worksheet's format

Welcome Forums General PowerShell Q&A Copy-ExcelWorksheet does not maintain the source worksheet's format

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

 
Participant
3 months, 2 weeks ago.

  • Author
    Posts
  • #110720

    Participant
    Points: 180
    Rank: Participant

    I created a new workbook:  newbook.xlsx

    (xlWBATWorksheet constant used in .Add parameter).

    I then copied 3 existing worksheets from 3 different workbooks into newbook.xlsx.

    While the contents were faithfully copied, the formats — column width, etc. were "altered" in the sense that they took the "defaults" newbook.xlsx.  Worse yet, string fields that had "date-looking" data were converted to LongDatetime format.

    Is there a simple way to "fix" this, short of re-opening newbook.xlsx and re-setting those formats?

    Would be grateful for any advice or tips.  Thanking you in advance.

     

  • #110726

    Participant
    Points: 878
    Helping Hand
    Rank: Major Contributor

    Have you heard about ImportExcel , there are a bunch of cmdlets for managing Excel in this module.

    You can do alot on merging and formating columns. Douge has given examples for all that.

    • #110731

      Participant
      Points: 180
      Rank: Participant

      Thanks Mr KVPrasoon ... I've been using Import-Excel mostly to go back and forth with CSV.  I "manipulate" with CSV as much as I can, and then go to EXCEL when it is "nearly finished".

      Thanks for this tip.

       

The topic ‘Copy-ExcelWorksheet does not maintain the source worksheet's format’ is closed to new replies.