Worksheet.Copy() is not copying the VBA code(macro's)

Aspose.Cells Product Family
1

Greetings,

I am using latest version 4.8.0.0 of Aspose.cells, I have the template xls file with 6 worksheets and each worksheet has VBA code(Macro's). All i am trying to do is to fill data in the template for each worksheet and create the new Workbook and add these filled worksheets (ie: copying the worksheets to the newly created workbook). This newly created workbook has to be rendered in browser. I was able to copy the data and its calculation formulas, but not able to copy the VBA code associated to each worksheet.

Does WorkSheet.Copy() method internally copy all the data and its associated VBA code(macro) ? or we need to do any additional coding using Aspose.cell API?

Thanks&Regards,

Pavan.

2

Hi Pavan,

Thank you for considering Aspose.

Well, I am afraid copying macros / VBA is not supported at the moment in Worksheet.Copy method. Currently, Aspose.Cells only preserve macros / VBA in a template file. It means you can create an Excel template file with macros and use Aspose.Cells to open and read it. At run time you can populate data, set styles or do other changes. After you save the file, the macros in template file will be kept.

Sorry for any inconvenience caused,

3

Hi Nausherwan,

Have this issue fixed in Aspose.cell 5.2 as i face same problem.

and also the checkbox is not allowed to check after copy to another workbook.

Thanks.

Alan

4

Hi,

Please try our latest version/fix: Please download: Aspose.Cells for .NET v7.0.1.1
If you still find any issue, give us you template files with sample code here, we will check it soon.

Thank you.

5

Hi Amjad,

I’m having the same issue, I have several workbooks that I want to combine a single one. Some of the sheets in these workbooks have vba code but it doesn’t show up in the generated workbook, I used the dll in you link to generate the excel.

I appreciate any help on this.

Thanks!

6

Hi,


Could you manually check if the vba codes remains when you manually combine workbooks in MS Excel, Aspose.Cells should follow MS Excel standards. Could you give us your template files and sample code to show the issue. We can log an issue if we found it.

Thank you.
7

Below code works fine (By Interop.Excel 12.0)

static private void Merge(string sourceFile,string targetFile)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook workbook1 = excel.Workbooks.Open(sourceFile,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Workbook workbook2 = excel.Workbooks.Open(targetFile,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook1.Sheets["TestCopyVBA1"];
Microsoft.Office.Interop.Excel.Worksheet worksheet2 = (Microsoft.Office.Interop.Excel.Worksheet)workbook2.Sheets["TestcopyVBA2"];
worksheet2.Copy(Missing.Value, worksheet1);
workbook1.Save();
workbook1.Close(false, Type.Missing, Type.Missing);
workbook2.Close(false, Type.Missing, Type.Missing);
}

While using Aspose.Cell 7.0 , the VBA in TestCopyVBA2 is missing. Test excel files attached.

string filepath = @"TestCopyVBA1.xls";
string filepath1 = @"TestCopyVBA2.xls";
string filepathoutput = @"Merged1.xls";
Workbook wb = new Workbook();
Workbook wb2 = new Workbook(filepath1);
Workbook wb1 = new Workbook(filepath);
wb.Worksheets.Clear();
wb.Copy(wb1);
wb.Worksheets.Insert(1, SheetType.Worksheet);
wb.Worksheets[1].Copy(wb2.Worksheets[0]);
wb.Save(filepathoutput);

Thank you so much

8

Hi,

After initial testing, we found this bug. We have logged this issue in our database. We will look into it and fix it asap.

This issue has been logged as CELLSNET-30754.

9

Hi,

We do not support copy VAB codes(macro) in worksheet.Copy method.

We just support it in Workbook.Copy method.

Because we just keep them and not parse them, we do not know how to copy VBA codes(macro) of a worksheet.

10

Hi,

Workbook.Combine will lost VBA either, below code snippet demonstrate:
Workbook wb = new Workbook();
Workbook wb2 = new Workbook(filepath1);
Workbook wb1 = new Workbook(filepath);
wb.Worksheets.Clear();
wb.Copy(wb1);
wb.Combine(wb2); //After combined, VBA in wb2 will be lost.

Is there any solution to solve this issue?

Thank you very much

11

Hi,


Thanks for sharing the sample code.

Yes, we noticed this issue now, we will look into it if we can figure it out. We will get back to you soon.

Thank you.
12

Hi,

We do not support copy VBA code (macro) too in Workbook.Combine method.

Because if both of the two workbooks contain VBA code(macro), we do not know how to combine them.

13

Hi,

Thanks for using Aspose.Cells.

We only plan to create, manipulate or embed VBA codes/ macros later. We however cannot support to run or execute macros. Does it fit your needs?

14

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan