I'm trying to copy a worksheet from one workbook to another Workbook. On the worksheet that needs to be copied, it has an Oval drawing object. The background colour of the Oval is white. The Workbook that I'm copying to is an .xlsm file.
If I use Workbook.Copy it works fine, the background property has been preserved. If I use Worksheet.Copy, the Oval shape's background property is not preserved.
Why is this? Should the Copy functionality between the two Workbook and Worksheet objects be the same?
I need to use Worksheet.Copy because I need to preserve a macro in a worksheet that I have in the .xlsm that I'm copying to. Workbook.Copy will overwrite this Worksheet with the macro.
I tried using Workbook.Combine, but it does not preserve the macro either.
If your problem persist, then please provide us your source workbooks and sample codes to replicate this issue. We will look into it and update you asap.
Please also try the Workbook.Combine() method with the latest version. It should work fine and preserve macros. Let us know your feedback.
I have accidentally stumbled on the reason why the drawing objects are not preserving thier properties when using Workbook.Combine().
The new version of the dll (v7.7.1.4) works if the spreadsheet I'm copying from is an .xlsx formatted file. If it is an .xls file, the drawing objects do not get preserved.
But, this only works if I'm combining the spreadsheet file that has the drawing objects into the spreadsheet file that has the macros. If I try to switch it around, macro file into drawing file, the macros are not preserved.
Also, Worksheet.Copy still does not preserve the macros if you're copying from a .xlsm file into a .xlsx file.
Macros might not work if you are copying from source file which has different format than destination file. It might work fine if you are copying from source file which has same format than destination file.
So, source and destination formats of workbook should match so that macros could be copied or combined properly.
For your drawing object issue, please provide us you sample code and source files, we will look into it and update you asap.
I have attached a .xls file that has a few drawing objects along with an .xlsm file that has a macro that pops up a msgbox whenever you click on a cell.
Here is my code...
Aspose.Cells.Workbook wkbkFrom = new Aspose.Cells.Workbook("c:\xDrawingObjects.xls"); Aspose.Cells.Workbook wkbkTo = new Aspose.Cells.Workbook("c:\xMacroFile.xlsm");
wkbkTo.Combine(wkbkFrom);
wkbkTo.Save("c:\MacroFile.xlsm");
After you have run the code above and compare the two spreadsheets afterwards, you'll notice that the drawing objects did not keep their format (line colouor, line size, fill, etc.)
Thanks for providing us template files and sample code.
After an initial test, I observed the issue as you mentioned by combining the two workbooks using your sample code (with your template files). The source workbook has a sheet with some drawing objects whose formattings (line color, line size, fill, etc.) are not retained when we merge that workbook to the target XLSM file.
e.g
Sample code:
Aspose.Cells.Workbook wkbkFrom = new Aspose.Cells.Workbook(“e:\test2\xDrawingObjects.xls”);
Aspose.Cells.Workbook wkbkTo = new Aspose.Cells.Workbook(“e:\test2\xMacroFile.xlsm”);
wkbkTo.Combine(wkbkFrom);
wkbkTo.Save(“e:\test2\MacroFile.xlsm”);
I have logged a ticket with an id “CELLSNET-42356” for your issue. We will look into it soon.
Once we have any update on it, we will let you know here.