EPPlus Add Worksheet with Latest Version of Office Losses All Macros

[Dave13s]

Note: Clone of stackoverflow ticket: Link

When you run the following code on a file that has any VBA macro's whatsoever(C#):

using (ExcelPackage xlPackage = new ExcelPackage(new FileInfo("Test.xlsm"))) { xlPackage.Workbook.Worksheets.Add("TestTab"); xlPackage.Save(); }

You get the following issue(upon opening the edited file): We found a problem with some content in 'Test.xlsm'. Do you want us to recover as much as we can? If you trust the source of this workbook, click Yes. enter image description here

(if you click yes)

Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA)) enter image description here

Then your VBA Marco is gone... I also have received:

The Visual Basic for Applications (VBA) macros in the workbook are corrupted and have been deleted. The macro corruption most likely exists in the current file. To recover the macros, open a backup copy of this file if you have one. enter image description here

I only see this issue on the latest update(Office 365 Excel 1708 - 8431.2079). When I test on older versions of excel or on versions that have not been upgraded to the latest version I do not experience this issue.

It appears the latest office update has introduced this issue into the opening of files that have been modified(specifically when a new worksheet in inserted) by EPPlus(Latest 4.5.0).

Has anybody else experienced this issue? Maybe someone has a work around of some sort. I was hoping not to have to dig into the source code :(

Test.zip

[JanKallman]

Please attach your xlsm file

[MattOG]

I too have been experiencing this issue since the office update on the 18th. You can recreate it very simply:

1. Create a new document with .xlsm
2. Add a vbaProject
3. Save it

The newest version of office considers the vba Project to be corrupted and forces it to be removed.

Test.zip

EDIT: I'm guessing it could have something to do with the recent change announced here: https://msdn.microsoft.com/en-us/library/office/cc313094(v=office.12).aspx

[JanKallman]

This works fine when I try it in my version of Excel, 1707 (build 8326.2107).

            var excelFile = new FileInfo(@"c:\temp\bug\test.xlsm");
            using (var package = new ExcelPackage(excelFile))
            {
                var ws = package.Workbook.Worksheets.Add("NewWorksheet");
                ws.CodeModule.Code = "Private Sub Worksheet_SelectionChange(ByVal Target As Range)\r\n\r\nEnd Sub";
                package.SaveAs(new FileInfo(@"c:\temp\bug\vbafailSaved.xlsm"));
            }

Are you using a newer version?

[JanKallman]

Sorry, I see you use (1708 - 8431.2079). I'll guess this is an Office insider update? I'll see if I can get a hand on this release.

[MattOG]

Jan, possibly yes. I'm on what Microsoft now call their "Monthly Channel" (it used to be called Current Channel) which is the default for their Office 365 Business plans.

[shackgithub]

I had this too and downgraded Office to resolve it. Works fine on:

[JanKallman]

Ok, so I installed 1708 - 8431.2079, and I can confirm that VBA does not work for me either. Obviously MS has changed something. I'll have a look too see if I can fix it, but I guess someone should report this to MS as well, as older generated workbooks will fail with this Excel version.

[JohnDoranNY]

I can confirm this issue as well. VBA projects simply will not work with the latest version of MS Office 365. I receive the same message: "The Visual Basic for Applications (VBA) macros in the workbook are corrupted and have been deleted. The macro corruption most likely exists in the current file. To recover the macros, open a backup copy of this file if you have one." I stripped down the VBA to just an open and closing statements, and still the error message. As soon as you call CreateVBAProject() method and Save(), the file you open, once accepting "Enable Macros" will always generate the same error message.

[JanKallman]

FYI, I have reported this to Microsoft. I will have a closer look why this fails myself as well.

[davidgeewhiz]

This is a disaster. I am waiting for customers to start calling me about this horrible message. Has anyone confirmed if this is specific to EPPLUS? I already rolled back Excel before thinking to test. Is there any hope that this might fixed? It looks like Microsoft made some major changes to the VBA file format structure.

[Dave13s]

A disaster it most certainly is, I just had my first client report issues today. I think we'll have to strip all VBA out of affected production environments, at least I have that luxury as my affected files contain logic that is rarely utilized. I would imagine for other organizations this is going to be quite a severe issue.

[davidgeewhiz]

I take back the "It looks like Microsoft made some major changes to the VBA file format structure." I was thinking issue might be related to this updated doc:

https://msdn.microsoft.com/en-us/library/office/cc313094(v=office.12).aspx

Also the change list for the build 8431.2079 doesn't point to anything that might affect VBA...?

https://technet.microsoft.com/en-us/office/mt465751

Anyway I'm going to shut up now and let the experts look at this. Of course Microsoft panned Jan's report:

https://social.msdn.microsoft.com/Forums/en-US/943a7bc7-a4b7-43aa-8f02-6cd65807351b/vba-fails-on-to-load-after-update-to-1708-84312079-with-epplus-generated-excel-workbooks?forum=exceldev

[JanKallman]

MS told me they would get back on Monday or Today, but I have not heard anything yet. I have looked into it myself and for all I can see there is no change to the VBA version in the dir stream. Possibly MS has added some extra validation on the load of the VbaProject.bin, but without knowing what it is, it's quite tricky to fix it. I hope I'll get some answers later today.

[shackgithub]

In case it helps, if you open the workbook in an older version of Excel and then save it, it will open successfully in the latest version and macros will run.

[RudyBuys]

I'm sitting with the same problem as a developer... and my client is also using Excel 2016. I just hope Microsoft acts quickly, or I will have a very irate client. I tested the xlsm file in Excel 2013 and it works fine there, but I cannot expect my client to downgrade their Office.

[JanKallman]

So I thought I had a case at Microsoft, but apparently it got stuck in the Microsoft support bureaucracy. I hope they can transfer it to the right department, but no luck so far. I think it would be great if anyone with this problem reported it to Microsoft, to get a little attention. Feel free to referens my incident number, 117092916418499.

[MattOG]

I've submitted 2 "frowns" and put a submission on a forum somewhere. I'm not aware of any "official" support forms that I have access to though. Please post if you know of a place to do so.

[jwbqv]

We're having the same issue as discussed in this thread. At present only a few of our Excel installs have upgraded but once upgraded they are unable to work.

[ODA-AGarcia]

There seems to be a newer version of Excel, maybe it is fixed in that version? Version 1709 (Build 8528.2084)

[MattOG]

No, the issue remains. I don't even think it's a bug in excel either, so it's unlikely to get "fixed".

[jwbqv]

See https://answers.microsoft.com/en-us/msoffice/forum/msoffice_officeinsider-mso_win10-msoinsider_excel/build-1705-on-excel-2016-save-as-corrupts-vba/27955309-119d-47b7-a1f3-5e0981692f35

[JanKallman]

Ok, still no contact with Microsoft,but I think I have a solution for this (it took me quite a few of hours of my spare time to find this out :) .). It turns out the problem is the ProjectWM stream inside the vbaproject.bin, now shouldn't be compressed. I have no idea why this behavior has been added. If anyone want to test. replace last row of ExcelVbaProject.CreateProjectwmStream

            //return VBACompression.CompressPart(((MemoryStream)bw.BaseStream).ToArray());
            return ((MemoryStream)bw.BaseStream).ToArray();

I have tested this on version 1708 (8431.2079) only and only the sample project. If this solves the problem, I will try to create an updated 4.1 this evening when I get home. Workbooks already generated with EPPlus will of course still be effected by this, so it would be great if we could get some answers if this is a correct behavior. If anyone wants to test this please give me some feedback if it works.

[Freshsurf]

So I did get in contact with Microsoft and they said it is a bug that has been lodged but no one is working urgently to fix it. They couldn't even guarantee it would be fixed in the next update.

They instructed us to roll back to the previous office release and turn off automatic updates.

For our organisation there are only 5 or so people affected so we could make this change individually without too much effort.

Step 1 Open CMD.exe as administrator

Step 2 Enter cd %programfiles%\Common Files\Microsoft Shared\ClickToRun

And then officec2rclient.exe /update user updatetoversion=16.0.8326.2107

Step 3 Disable Auto Updates in Excel > Account > Updates

So if you are only fixing people in your organisation this will work and it may be a good fix for some key clients until the code is debugged.

Best of luck

[MattOG]

The issue linked by @jwbqv seems to be a different issue to this. Although the end result is the same, i.e. a corrupt file, they appear to have completely separate steps to reproduce. On top of the fact that the issue appears in a different version of excel, this leads me to think they're not the same thing at all, and we shouldn't confuse the response to the issues with each other.

[JanKallman]

Fix to be tested here EPPlus.4.1.1.VBA.fix.zip Let me know if it works.

[MattOG]

@JanKallman I can confirm the above change prevents the error from occurring in all the sheets I tested it on. The generated file also opens in earlier versions of excel (tested back to Office 2010) without issue.

In case it matters, I tested by modifying the code myself, rather than using the version you just posted. (Because you hadn't posted that before I started).

[GertVen]

@JanKallman I downloaded the GIT repository and applied the fix (as given in your previous post) directly in the source code. So far we have tested this at two of our clients and all seems to be working correctly. We're currently busy testing it at some of our other clients, I'll let you know if we encounter any errors. But thanks for the quick reponses, it's highly appreciated!

EDIT: Tested in Office 2016

[shackgithub]

Brilliant! Works for me too. Thanks every so much for all the work on this.

[RudyBuys]

It worked for me too, thanks :)

[GertVen]

@JanKallman Just wondering... won't this break all the macros for the excel documents prior to the update?

EDIT: Just tested in Excel 2007 without any issues

[JanKallman]

@GertVen Yes, all macroenabled spreadsheets genereted in EPPlus prior to this version will fail to open. Microsoft should fix this, as I can't see the point in this change. I'll see if I can get in contact with MS on the insider site provided by @jwbqv

[GertVen]

@JanKallman Yes, that is to be expected. I'm just wondering what will happen if I use your new version of EPPlus on any version of Office that hasn't been updated. For example, use your new version of EPPlus on a version of Excel that hasn't been updated since last year and therefore wouldn't have the update that creates the error. But I've just tested it on Office 2007 without an error so I'm guessing its handled somewhere :)

[JanKallman]

@GertVen I assume that it should work as this is the way Excel writes it in 1708+, but I have not tested it.

[MattOG]

@GertVen I tested excel versions back to office 2010 and all worked with the excel file generated with the "fixed" version of EPPlus

[jchonc]

@JanKallman, thank you so much for figure this one out, we are days away from the new release and cannot imaging what will happen without it. (QA has already reported this to us) On a different note, any ETA on when it will make into the official NuGet package?

[JanKallman]

I want to make sure this works first. But if everything looks ok we could have a new package out this weekend. That is 4.1 with this fix --> 4.1.1

[davidgeewhiz]

I am testing the pre-release 4.1.1 dll in a C++ project and am getting the build error below (worked fine with 4.1.0. What am I doing wrong? I have no ego, please tell me if I am being stupid.

1>c:\path...\epplus.dll : error C3252: 'CellStore::Finalize' : cannot reduce accessibility of a virtual method in a managed type 1> This diagnostic occurred while importing type 'CellStore ' from assembly 'EPPlus, Version=4.1.1.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1'. 1> This diagnostic occurred while importing type 'OfficeOpenXml::ExcelWorksheet ' from assembly 'EPPlus, Version=4.1.1.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1'. 1>....\excel.cpp(265): error C3252: 'CellStore::Finalize' : cannot reduce accessibility of a virtual method in a managed type 1> with 1> [ 1> T=OfficeOpenXml::ExcelCoreValue 1> ] 1> ....\excel.cpp(265) : see reference to class generic instantiation 'CellStore' being compiled 1> with 1> [ 1> T=OfficeOpenXml::ExcelCoreValue 1> ] 1> This diagnostic occurred while importing type 'OfficeOpenXml::ExcelWorksheet ' from assembly 'EPPlus, Version=4.1.1.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1'.

The line in question is testing a reference to ExcelWorksheet with nullptr:

bool CreateWorksheet (ExcelWorksheet^ %ws) { if (ws != nullptr) { <---- Error C3252 do stuff to ws... } }

CORRECTION: The last version of EPPlus I was using in this VS C++ project was 4.0.4.0 I see this compile issue in 4.1.0.0 (it didn't seem this was related to the VBA issue). No one had this problem with 4.1.0.0? Am I the only one still using C++? I need to use 4.1.1.0 for the VBA fix. Should I post this as a new issue?

UPDATE: The compile error goes away if I change CellStore from internal class to public. But is this correct to do?

[swmal]

Jan and I discussed this briefly today and our combined knowledge on C++ in combination with .NET is very limited. Nothing has changed related to the CellStore since version 4.0, so your problem is most likely not depending on the VBA issue. I guess your problem will be solved if you change from internal to public and recompile the EPPlus code- that would not break any functionality. We have a few classes defined as internal, this is just because we dont think they make sense to the "outside world".

But we don't want to cause any problem for our C++ friends using EPPlus in the community, so we should probably consider to change this if you can confirm that it is the internal declarations that causes this issue.

[davidgeewhiz]

I can confirm that this change:

internal class CellStore : IDisposable// : IEnumerable, IEnumerator to: public class CellStore : IDisposable// : IEnumerable, IEnumerator

compiles clean in C++ and my project functions as expected, except for...

I am getting an exception when I set the last two columns of a spreadsheet to Hidden=true (this is after auto fit columns call)

ws->Column(10)->Hidden = true; // Second to last column ws->Column(11)->Hidden = true; // throws exception

thrown in set property for ColumnMax:

throw new Exception(string.Format("ColumnMax can not span over existing column {0}.",c.ColumnMin));

when I comment out exception I get the spreadsheet with both columns hidden, but I don't think that is the ideal solution! I can submit as new issue, funny no C# folks have reported? Worked fine in 4.0.4.0

[swmal]

@davidgeewhiz since you already have access to EPPlus code, would it be possible for you to try and replicate this behaviour with a failing c# unit test?

[JanKallman]

I have asked the question to Microsoft on the insider site here. Attaching failing and working sample 15-3. FailingSamplePackage.zip

[davidgeewhiz]

Here is a small test that will throw the span exception (sorry swmal I am not a C# person). It seems that the ExcelVerticalAlignment statement causes the exception if it is set after the AutoFitColumns call. I'd be happy to submit this as a new issue since unrelated to the original VBA problem.

FileInfo ^file = gcnew FileInfo("C:\Temp\Test.xlsx");

ExcelPackage ^pck = gcnew ExcelPackage(file); ExcelWorksheet ^ws = pck->Workbook->Worksheets->Add("Worksheet");

if (ws != nullptr) { ws->Cells["A1"]->Value = "Cell value 1"; ws->Cells["B1"]->Value = "Cell value 2"; ws->Cells["C1"]->Value = "Cell value 3"; ws->Cells["D1"]->Value = "Cell value 4"; ws->Cells["E1"]->Value = "Cell value 5"; }

//ws->Cells->Style->VerticalAlignment = ExcelVerticalAlignment::Top; // Columns 4 and greater hidden ws->Cells->AutoFitColumns(0); ws->Cells->Style->VerticalAlignment = ExcelVerticalAlignment::Top; // 4.5.0.0 - exception, 4.0.4.0 - columns 4 and 5 hidden

ws->Column(4)->Hidden = true; ws->Column(5)->Hidden = true; // span exception

pck->Save();

[swmal]

Thanks @davidgeewhiz , this is useful and very simple to translate to C#";) @JanKallman can probably look into this, but would be great if you could submit this as a separate issue to avoid further posts in this thread.

[davidgeewhiz]

Done, issue "ColumnMax exception when AutoFitColumns call followed by setting VerticalAlignment"

[jwbqv]

We downloaded & tested the Nuget 4.1.1 pre-release and confirm that it fixes the issue for us. Thank you very much.

[SigmasSolutions]

Hi,

when save the file (xlsm) with the library and recover the generated file, show these information:

Removed Part: /xl/pivotTables/pivotTable3.xml part. (PivotTable view) Removed Part: /xl/pivotTables/pivotTable2.xml part. (PivotTable view) Removed Part: /xl/pivotTables/pivotTable1.xml part. (PivotTable view) Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache) Removed Records: Workbook properties from /xl/workbook.xml part (Workbook) Repaired Records: Cell information from /xl/worksheets/sheet6.xml part

Has anybody else experienced this issue?

Thanks.

[kmai00]

@SigmasSolutions I have the same issue as well.

Basically, I get this error when I make a new xlsm file using EPPlus based off of an older excel file (content says it was made back in 2002). After repairs, i notice that merged cells aren't merged anymore.

I also wrote a little test that basically check every worksheet's cell. For a 2006 file I found on the internet (King James Bible from spreadsheetpage.com), I saw that some of the formulas needed to be restored and then the two sheets look the same.

This test with excel files that I made manually using Excel Version 1710 (Build 8625.2139) and a 2008 file (Animated Color Scales from spreadsheetpage.com)

I also tried to open the 2006 file, resave it, make a copy with EPPlus, and run tests. Says that it fails.

        var sourceInfo = new FileInfo(sourcePath);
        var copyInfo = new FileInfo(copyPath);

        var sourceExcel = new ExcelPackage(sourceInfo);
        //sourceExcel.SaveAs(copyInfo);
        File.WriteAllBytes(copyPath, sourceExcel.GetAsByteArray());
        sourceExcel = new ExcelPackage(sourceInfo); //ReOpen
        var copyExcel = new ExcelPackage(copyInfo);

        foreach (var sourceWorksheet in sourceExcel.Workbook.Worksheets)
        {
            var copyWorksheet = copyExcel.Workbook.Worksheets[sourceWorksheet.Name];
            foreach (var sourceCell in sourceWorksheet.Cells)
            {
                var copyCell = copyWorksheet.Cells[sourceCell.Address];
                Assert.AreEqual(sourceCell.Value, copyCell.Value);
            }
        }

[davidgeewhiz]

Is there any update on when the VBA fix release is going to be official? Did Microsoft ever confirm that this new behavior was intended? Things have been sort of hanging for awhile...

[msob]

I also need a stable solution for this. Are there any news about this topic? Or can I help you resolving this issue?

[yingnierxiao]

i create xlsm in window is ok.but create maxosx ,excel version 15.13.3,when open in macosx ,is bad file.