Jump to content

Recommended Posts

Posted (edited)

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:

 

 

131.PNG

121.PNG

Edited by CiaronJohn
Posted

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.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Posted

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 )
    ...

 

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...