CiaronJohn Posted January 20, 2020 Share Posted January 20, 2020 (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: Edited January 20, 2020 by CiaronJohn Link to comment Share on other sites More sharing options...
water Posted January 20, 2020 Share Posted January 20, 2020 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. CiaronJohn 1 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Subz Posted January 20, 2020 Share Posted January 20, 2020 Can you attach an example Excel file? It worked fine for me on Office 2016, including column widths, text alignment. Link to comment Share on other sites More sharing options...
CiaronJohn Posted January 21, 2020 Author Share Posted January 21, 2020 Here is the example Excel File Samplefile.xlsx Link to comment Share on other sites More sharing options...
Subz Posted January 21, 2020 Share Posted January 21, 2020 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 1 Link to comment Share on other sites More sharing options...
CiaronJohn Posted January 21, 2020 Author Share Posted January 21, 2020 Thanks @Subz it worked. Thank you @water as well. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now