Error in _Excel_SheetCopyMove function

[CiaronJohn]

Code below successfully copy worksheet to another workbook. The only problem is color format became different from the original. See snippet and reference below.

Func SeparateSheet( $fTestDesign,$fNewSheet ,$sheetindex1,$sheetindex2,$sheetindex3,$sheetindex4,$sheetindex5)

    ; Local Variables

    Local $oExcel=_Excel_Open(False)

    $oBook = _Excel_BookNew( $oExcel, 1 )
    $fTestDesign = _Excel_BookOpen ( $oExcel, $fTestDesign )
    ; Create another workbook instance
    $fNewSheet = $fNewSheet
    _Excel_BookSaveAs( $oBook, $fNewSheet, $xlWorkbookDefault, True )
    $fNewSheet = _Excel_BookOpen ( $oExcel, $fNewSheet )
    ; Move target function into another workbook
    _Excel_SheetCopyMove ( $fTestDesign, $sheetindex1, $fNewSheet, 1, True )
    _Excel_SheetCopyMove ( $fTestDesign, $sheetindex2, $fNewSheet, 1, True )
    _Excel_SheetCopyMove ( $fTestDesign, $sheetindex3, $fNewSheet, 1, True )
    _Excel_SheetCopyMove ( $fTestDesign, $sheetindex4, $fNewSheet, 1, True )
    _Excel_SheetCopyMove ( $fTestDesign, $sheetindex5, $fNewSheet, 1, True )

    _Excel_SheetDelete ( $fNewSheet, "Sheet1" )
    _Excel_BookClose ( $fTestDesign )
    _Excel_BookClose ( $fNewSheet )
EndFunc

Original color:

Resulting Color:

 

 

Edited January 20, 2020 by CiaronJohn

[water]

I have modified your script and noticed that the column formatting (width) does not get copied for my sheet.
The function uses Excel COM as described here.
I did not find a way to copy the formatting as well - seems you just can copy/move the sheet content.

[Subz]

Can you attach an example Excel file?  It worked fine for me on Office 2016, including column widths, text alignment.

[CiaronJohn]

Here is the example Excel File

Samplefile.xlsx

[Subz]

You just need to apply the "Office 2007 - 2010" theme for example in Office 2016:

...
    _Excel_SheetDelete ( $fNewSheet, "Sheet1" )
    $fNewSheet.Theme.ThemeColorScheme.Load ("C:\Program Files (x86)\Microsoft Office\Document Themes 16\Theme Colors\Office 2007 - 2010.xml")
    _Excel_BookClose ( $fTestDesign )
    _Excel_BookClose ( $fNewSheet )
    ...

 

[CiaronJohn]

Thanks @Subz it worked. Thank you @water as well.