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

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.

 

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.

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.