Skip to content
This repository has been archived by the owner on Mar 9, 2020. It is now read-only.

EPPlus Add Worksheet with Latest Version of Office Losses All Macros #5

Closed
Dave13s opened this issue Sep 21, 2017 · 59 comments
Closed

EPPlus Add Worksheet with Latest Version of Office Losses All Macros #5

Dave13s opened this issue Sep 21, 2017 · 59 comments

Comments

@Dave13s
Copy link
Contributor

Dave13s commented Sep 21, 2017 •
edited
Loading

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
Copy link
Owner

Please attach your xlsm file

@MattOG
Copy link

MattOG commented Sep 22, 2017 •
edited
Loading

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

Dave13s added a commit to Dave13s/EPPlus that referenced this issue Sep 22, 2017
@Dave13s
I fixed a rather small issue that prevented EPPlus from saving files …
da50233 
…that contain "null DataStreams". This does not reolve the issue outlined in "JanKallman#5", however it is likely a contributing factor
@Dave13s Dave13s mentioned this issue Sep 22, 2017
Merged
@JanKallman
Copy link
Owner

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
Copy link
Owner

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
Copy link

MattOG commented Sep 25, 2017

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
Copy link

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

@JanKallman
Copy link
Owner

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
Copy link

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
Copy link
Owner

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

@davidgeewhiz
Copy link

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
Copy link
Contributor Author

Dave13s commented Oct 3, 2017

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
Copy link

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
Copy link
Owner

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
Copy link

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
Copy link

RudyBuys commented Oct 3, 2017

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
Copy link
Owner

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
Copy link

MattOG commented Oct 4, 2017

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
Copy link

jwbqv commented Oct 4, 2017

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
Copy link

ODA-AGarcia commented Oct 4, 2017 •
edited
Loading

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

@MattOG
Copy link

MattOG commented Oct 4, 2017

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

@jwbqv
Copy link

jwbqv commented Oct 4, 2017

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
Copy link
Owner

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
Copy link

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
Copy link

MattOG commented Oct 5, 2017

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
Copy link
Owner

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

@MattOG
Copy link

MattOG commented Oct 5, 2017 •
edited
Loading

@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
Copy link

GertVen commented Oct 5, 2017 •
edited
Loading

@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
Copy link

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

@GertVen
Copy link

GertVen commented Oct 5, 2017 •
edited
Loading

@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
Copy link
Owner

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

@MattOG
Copy link

MattOG commented Oct 5, 2017 •
edited
Loading

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

@jchonc
Copy link

jchonc commented Oct 5, 2017

@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
Copy link
Owner

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
Copy link

davidgeewhiz commented Oct 8, 2017 •
edited
Loading

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
Copy link
Collaborator

swmal commented Oct 8, 2017

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
Copy link

davidgeewhiz commented Oct 8, 2017 •
edited
Loading

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
Copy link
Collaborator

swmal commented Oct 9, 2017

@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
Copy link
Owner

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

@davidgeewhiz
Copy link

davidgeewhiz commented Oct 9, 2017 •
edited
Loading

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
Copy link
Collaborator

swmal commented Oct 9, 2017

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
Copy link

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

@jwbqv
Copy link

jwbqv commented Oct 9, 2017

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

JanKallman pushed a commit that referenced this issue Oct 11, 2017
Fixed issue #5,#25,#27
945a476
@SigmasSolutions
Copy link

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
Copy link

kmai00 commented Dec 14, 2017 •
edited
Loading

@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
Copy link

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
Copy link

msob commented Jan 5, 2018 •
edited
Loading

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

@yingnierxiao
Copy link

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

@jaulestiadefaria
Copy link

jaulestiadefaria commented Feb 28, 2018 •
edited
Loading

@JanKallman

Hoping for some help here. I am getting these same errors when opening the file produced with EPPlus.

I have tried to add vba code to an export that has been working fine generating an xlsx file. The code I am now testing with is:

using (ExcelPackage pck = new ExcelPackage())
    {

..........................
pck.Workbook.CreateVBAProject();
var sb = new StringBuilder();
sb.AppendLine(" Private Sub Workbook_Open() ");
sb.AppendLine(" MsgBox "This is fun" ");
sb.AppendLine(" End Sub ");
pck.Workbook.CodeModule.Code = sb.ToString();

I then proceed to generate the download like this: (previous set up commented for xlsx)
//Export
Response.ContentType = "application/vnd.ms-excel.sheet.macroEnabled.12";// "application /vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=StandardPayRequestImportForm.xlsm"); //was .xlsx
Response.BinaryWrite(pck.GetAsByteArray());

         Response.End();
    }

Office 365 Excel version is 1708 (Build 8431.2215), and EPPLUS version is 4.1.1

Thanks for any help you can provide.

@JanKallman
Copy link
Owner

No answer from Microsoft, so I'm not sure if they are going to fix this. Version 4.5.1 (and 4.1.1) solves the issue from EPPlus, so I'll close this issue

@LaurieNash
Copy link

LaurieNash commented Apr 26, 2018 •
edited
Loading

where can I get the latest version of EPPlus? I am currently using 3.1.3.0 and getting message saying I my VBA macros are corrupted and have been deleted. I'm testing Office 365 and getting this message

@jaulestiadefaria
Copy link

jaulestiadefaria commented Apr 26, 2018 via email

@LaurieNash
Copy link

LaurieNash commented Apr 26, 2018 via email

@jaulestiadefaria
Copy link

jaulestiadefaria commented Apr 26, 2018 via email

@LaurieNash
Copy link

LaurieNash commented May 1, 2018 via email

@swmal
Copy link
Collaborator

swmal commented May 2, 2018 via email

@LaurieNash
Copy link

LaurieNash commented May 8, 2018 via email

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests