Macros & Merging


#1

Hi,

We have bought the Professional version of Aspose.Excel product. We have tried merging two excel workbooks (using combine) but it does not seem to work if one workbook has macros in it. If we were to upgrade to Enterprise version - will it work? Or is macros not supported in merges at all? Please let me know at the earliest.

Thanks,

Andy


#2

Hi Andy,

You cannot merge macros in two files to one file. Aspose.Excel can only keep macros in the first file. And that need the Enterprise edition.

For example,

Excel excel1 = new Excel();
excel1.Open(“c:\book1.xls”);

Excel excel2 = new Excel();
excel2.Open(“c:\book2.xls”);

excel1.Combine(excel2);

Only macros in book1.xls will be kept.


#3

Hi Laurence,

Thanks for your prompt response. Here is the scenario.

I have a file A with one sheet with no macros and a file B with multiple sheets and some macros. I want to merge File B with File A so that I have File A as the master file with merged tabs.

From your response it seems that I will not be able to retain the macros of File B but - if I was trying to merge so that File B ends up as the master file then it should be fine - is that right? And for that I need Enterprise edition.

Could you please confirm that? If yes, then is there any way for me to shuffle the tabs when I do have a master file (File B) - basically we want to end up with tab of the File A as the first tab.

Thanks,

Andy


#4

Hi Andy,

Yes. You can do it.

Sample code:

Excel excel1 = new Excel();
excel1.Open(“c:\fileb.xls”);

Excel excel2 = new Excel();
excel2.Open(“c:\filea.xls”);

excel1.Combine(excel2);

//Move sheet tab in filea to the first tab
Worksheet sheet = excel1.Worksheets[excel1.Worksheets.Count - 1];
sheet.Move(0);

By the way, you can test this sample code using evaluation copy. It’s Enterprise edition.


#5

Cool - thanks for that - I shall try it out.


#6

Hi Laurence,

I tried the aspose Enterprise version for this issue and it merges fine and retains macros. That is the good part. The bad part is that it loses some of the styles of File A after the combine. In other words, the master file that I end up with does not retain ALL of the styles - some are retained and some are not.

Is that an issue that you are aware of?

Andy


#7

Hi Andy,

Which version are you using? Is it the latest v3.0.4?

Could you upload your two files here? If you don’t want to make it public, please send them to excel@aspose.com. I will check them ASAP.


#8

Hi Laurence,

I just sent you an email on excel@aspose.com with my two files.

Thanks,

Andy


#9

Hi,

Just wondering about the status of this issue. I hope you received my email with sample files…

Thanks,

Andy


#10

Hi Andy,

I have already send you an email for this issue.

The following is the content of that email.


This problem is caused by the color palette. FileB use defaut Excel color palette while FileA has a custom color in the palette. Please check http://www.aspose.com/Products/Aspose.Excel/Api/Aspose.Excel.Excel.ChangePalette.html for reference.

I use the following code to solve the problem.

Excel excel1 = new Excel();
excel1.Open(“c:\fileb.xls”);
Excel excel2 = new Excel();
excel2.Open(“c:\filea.xls”);

Color customColor = excel2.Worksheets[1].Cells[“a10”].Style.ForegroundColor;

excel1.ChangePalette(customColor, 55);

excel1.Combine(excel2);


#11

Hi Laurence,

For some reason, I did not receive any email?? Oh well, thanks for the solution - I shall try this. Appreciate your assistance in this respect.

Thanks,

Andy