Execute a macro (vba script) and then remove it to Save as XLSX?

Jan 17, 2013 at 6:52 PM

Hello ppl,

My title says a lot about what I wish to achive. The library as such was very helpful for me. I was just wondering if i could execute the script i added to my file and then remove it after it executed and still save the file as a xlsx?

The VBA script just formats and groups my data and I wold like to hold on to the formatting when i remove the VBA part.

Looking for some code samples as I was not able to find any in the sample projects and I am very new to coding. Any help is appreciated. 

Thanks

Ron

Jan 18, 2013 at 2:34 PM
Edited Jan 18, 2013 at 2:39 PM

A better approach cold be to do some .NET code that uses epplus to organize, group and format the data in a spreadsheet worksheet without having to use a VBA macro. 

Otherwise -

The epplus OpenXML wrapper can do a lot but not everything that OpenXML covers.  Using my google and stackoverflow.com search IQ implant I bolted together some OpenXml code I found that can open a .xslm spreadsheet file that has macros, remove them, save the xlsm file, re-open the xlsm file and save it as a xlsx file:  You need to install the Open Office XML SDK (http://www.microsoft.com/en-us/download/details.aspx?id=30425) as the code below does not use epplus

(.NET 4.0 project)

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml; // References C:\Program Files (x86)\Open XML SDK\V2.5\lib\DocumentFormat.OpenXml.dll
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO; // References : C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\Profile\Client\WindowsBase.dll

namespace RemoveExcelMacros1
{
  class Program
  {
    static void Main(string[] args)
    {
      DocumentFormat.OpenXml.Packaging.SpreadsheetDocument document = SpreadsheetDocument.Open(@"d:\sample1.xlsm", true);
      document.WorkbookPart.DeletePart(document.WorkbookPart.VbaProjectPart); // Remove all macros
      document.Dispose(); // Save sample1.xlsm file that has no macros
      byte[] byteArray = File.ReadAllBytes(@"d:\sample1.xlsm");
      using (MemoryStream stream = new MemoryStream())
      {
        stream.Write(byteArray, 0, (int)byteArray.Length);
        using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
        {
          // Change from template .xlsm type to workbook .xlsx type
          spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
        }
        File.WriteAllBytes(@"d:\sample1.xlsx", stream.ToArray());
      }
      Console.Write("Program finished.  Press a key\n");
    }
  }
}


The original Sample1.xlsm is not deleted.

Jan 18, 2013 at 3:53 PM

 

xav2075: That worked as a charm. Thanks

I was trying to do the same thing but I tried using EPPlus's WorrkBook.VBAProject.Remove() to get rid of the VBA Part. That helped me get the code out but still kept the format as MacroEnabled even when i did the Format change as you have done in your code. 

But that was a good learning process for me. 
Thanks for your help

Ron 

PS: i tried something like this       

 

Dim fmtPck As New ExcelPackage(newFile)
fmtPck.Workbook.VbaProject.Remove()
fmtPck.Save()
fmtPck.Dispose()

 

Jan 18, 2013 at 6:44 PM
Edited Jan 18, 2013 at 7:08 PM

Ah I forgot to search the epplus help chm file for vba as well as macro.  I tried the .VbaProject.Remove and it does remove all macros but still saves the Excel xml zipped package in xlsm format.  Looking in epplus I was not able to find anything that does spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook); as in the OpenXml code I posted above.

The code below loads an existing xlsm spreadsheet and injects it into excel_package.

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using OfficeOpenXml;
 
namespace RemoveExcelMacros_using_epplus1
{
  class Program
  {
    static void Main(string[] args)
    {
      FileInfo xlsm_template = new FileInfo(@"d:\sample1.xlsm"); 
      FileInfo newFile = new FileInfo(@"d:\sample1.xlsx");
      ExcelPackage excel_package = new ExcelPackage(newFile, xlsm_template);
      excel_package.Workbook.VbaProject.Remove();
      excel_package.Save(); // Saves sample1.xlsx but Excel won't open it unless .xlsx extension is changed to .xlsm
    }
  }
}
Feb 20, 2014 at 8:48 PM
Edited Feb 21, 2014 at 1:23 PM
I see your description from a while back. I have the similar situation. I want to create a spreadsheet on the fly (from asp.net) by filling from db. Then I want to create a macro/vba in that spreadsheet by reading in text file that contains lots of formatting code. I then want to Run the vba code and then allow the user to save as .xlsx. When in Excel directly I can create a macro and run the code without having to save the file first.

I do not see anywhere in the above discussion or in the epplus samples where vba code is actually being executed, like Workbook.Module("FormatMyXls").Run . I see where I can create a Sub Workbook_Open with the code but I think that would require that I save and close the .xlsm file on the server and then reopen it. I tried creating code on the Worksheet_Activate but that wont work programatically, works okay in UI.

The reason I want the VBA code in a separate file as there are many spreadsheets and I dont want to have to recompile a .net application when it is just a formatting change. This way I can maintain all of the formatting vba code by opening up excel directly and saving the new vba to a text file and read it in from asp app when i need it.

This is a bug in epplus. There is something happening within Excel that detects when the VBA code is removed it is okay to save as xlsx. epplus needs to find out what this internal 'switch' is and implement it. I have been able to implement the techniqes in the samples provided by epplus and mentioned above, but this bug is prohibiting me from coming up with a really nice solution. As of now I will just be having the user save as xlsm and then have them manually remove the code and save as xlsx.

So Epplus here are my issues:
Implement a fix for xlmx save
Implement a Module.Run
Implement a Worksheet.Select("name") -- not select a cell or range to activate