Thanks for providing us some details and sample code.
Well, vba codes/ macros are stored at Workbook level, so when you copy a worksheet to another workbook, vba codes/macros won’t be copied. Regarding PivotTable issue, we need your template file to evaluate your issue on our end. Please provide your template Excel file (containing the PivotTable), we will check it soon.
Please provide us your excel file so that we could test your issue. It seems, the above post by Amjad is still valid because macros are stored at Workbook level not at Worksheet level.
For your reference, i am attaching the file. I need to copy (Enterprise Report) sheet to other workbook.You can observe that, we have pivot tables in that sheet. I will do some click events in that sheet.
I need to copy the single worksheet with Macros and pivottables.
I have looked into this issue with a Microsoft Excel perspective and found Microsoft Excel does not support it as a standalone, but it supports it with external links dependency i.e. the generated excel file will depend on external links.
For example, when pivot table or macro is copied to another workbook, they refer to external links which do not work if these links are changed or renamed.
I have attached two excel files for your reference as well as screenshots. First contains pivot table and macro button. The second is generated by copying the sheet from the first one. As you can see in the second workbook “Book1.xlsx”, although pivot table and button are copied, but they do not work because they are referring to external workbook i.e “sample.xlsm” which I have renamed as “renamed-sample.xlsm”.
Let us know do you want Aspose.Cells to support this feature like Microsoft Excel does i.e with external links dependency.
actually aspose.cells doesn’t support click events … I have the logic to perform the click evens using Microsoft.interops …
Bu the challenge is Microsoft.interop doesn’t handle big files as it throws outofmemory exception since it uses huge % of RAM.
So I have the below approach like I will remove the rest of the sheets except the sheet I want to work.
though I am able to aceive this using aspose but unfortunately all the macros were disabled. its copying the content as a plain text.
the area I need your help is… could you please provide me a suggestion that can we copy single sheet into another excel with its dependency.( so that file size will be reduced and my dependent date will be in the new excel)…
Please see the following code. It copies source workbook to target workbook. This way all the things from source including pivot tables and macros are copied to target workbook. However, target workbook’s existing worksheets are removed. Then the code re-inserts the existing worksheets by worksheet copy method.
Please check all the participant files inside the code
Source.xlsm
Target.xlsm
TargetOut.xlsm
which I have attached as a zip file with this post.
C#
Workbook src = new Workbook(“Source.xlsm”); Workbook tar = new Workbook(“Target.xlsm”); Workbook tar1 = new Workbook(“Target.xlsm”);
//It will copy everything from source to target //but the target sheets will be removed tar.Copy(src);
//Get the target sheets and re-insert them for (int i = 0; i < tar1.Worksheets.Count; i++) { Worksheet tar1Sht = tar1.Worksheets[i]; Worksheet tarSht = tar.Worksheets.Add(tar1Sht.Name); tarSht.Copy(tar1Sht); }
//You can now remove any unwanted sheet here //…
What version of Aspose.Cells for .NET API are you currently using? Please note, we have shared the code snippet after testing it against the latest release of Aspose.Cells for .NET (Latest Version). Please give a try to this release and let us know your feedback.
I have attached a sample console application project created in Visual Studio 2013. It uses the 7.5.2.1 version. Please download it and run at your end and let us know your feedback. You can also update its version to 7.5.2.2.
Please see license setting code always comes first before you create a workbook object as I have shown in the sample project.
I was wondering if Aspose now supports this feature (ie changes references to another sheet be an external link) when copying a single worksheet to another workbook?
We are trying to replace Excel with Aspose so it needs to behave in the same way.
We are currently using the 17.6.0.0 version of Aspose.Cells for .NET and it still appears that the resultant workbook has #REF wherever excel would have the external reference.
Could you create a simple console demo application, zip it and post us here to show the issue, we will check it soon. Also provide your template file(s) and output file by Aspose.Cells APIs. Moreover, please provide your expected file which you can create by manually performing the copying steps in MS Excel, this will help us really to evaluate your issue precisely and consequently figure it out soon.
''Use file stream to open workbook so it can be opened as ReadOnly
Dim loStrm As New System.IO.FileStream("C:\Temp\Original.xlsx", IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.ReadWrite)
Dim loOrigWB As New Aspose.Cells.Workbook(loStrm)
loStrm.Close()
Dim loNewWB As New Aspose.Cells.Workbook(Aspose.Cells.FileFormatType.Xlsx)
loNewWB.Worksheets("Sheet1").Copy(loOrigWB.Worksheets("Sheet1"))
loNewWB.Save("C:\Temp\AsposeCopy.xlsx")
Attached are 3 xlsx files. The original, the copy using excel and the copy using above code.
Thanks for the template file, sample code and screenshot.
After an initial test, I observed the issue as you mentioned by using his sample code with your template file. I found invalid reference to original file issue when copying a sheet from one workbook to other.
I have logged a ticket with an id “CELLSNET-45702” for your issue. We will look into it soon.
Once we have an update on it, we will let you know here.
Thanks. That works when you are saving into the same folder.
If you save into a different folder it puts the path you are saving into as the path rather than the original WB path so even though it now has correct file name for the reference it has the incorrect path.