Compile VBA Project

[hcarvalho]

I try join two different excel file, both has vba
i add sheet´s and vba content fileB to fileA

I generate a third file FileAFileB, when open the file occors compile error, if i compile(Vba Project Compile) file works.

fileA is base file to add sheet from fileB, I try join two different excel file, both has vba
i add sheet´s and vba content fileB to fileA

I generate a third file FileAFileB, when open the file occors compile error, if i compile(Vba Project Compile) file works.

fileA is base file to add sheet from fileB, there must be a way to Compile VBA Project from a new file for errors stop.

        var stream = HelperFileStream.GetResourceFileStream("fileA.xlsm");
        var pv = new Aspose.Cells.Workbook(stream);

        var areaStream = HelperFileStream.GetResourceFileStream("fileB.xlsm");
        var area = new Aspose.Cells.Workbook(areaStream);


        var planilha1 = area.Worksheets["Planilha1"];
        pv.Worksheets.Add(planilha1.Name);
        pv.Worksheets[planilha1.Name].Copy(planilha1);

        var Tabela = area.Worksheets["Tabela"];
        pv.Worksheets.Add(Tabela.Name);
        pv.Worksheets[Tabela.Name].Copy(Tabela);

        var wksStr2DB = area.Worksheets["STR2SB"];
        pv.Worksheets.Add(wksStr2DB.Name);
        pv.Worksheets[wksStr2DB.Name].Copy(wksStr2DB);

        var Visibilidade = area.Worksheets["Visibilidade"];
        pv.Worksheets.Add(Visibilidade.Name);
        pv.Worksheets[Visibilidade.Name].Copy(Visibilidade);

        foreach (var item in area.VbaProject.Modules)
        {
            if (item.Type == VbaModuleType.Procedural || item.Type == VbaModuleType.Class)
            {
                var index = pv.VbaProject.Modules.Add(item.Type, item.Name);
                pv.VbaProject.Modules[index].Codes = item.Codes;
            }
        }

        foreach (var item in area.VbaProject.References)
        {
            if (!pv.VbaProject.References.Any(w => w.Name == item.Name))
            {
                pv.VbaProject.References.AddRegisteredReference(item.Name, item.Libid);
            }
        }

        pv.Save(@"E:\Temp\fileAfileB.xlsm", Aspose.Cells.SaveFormat.Xlsm);

        Assert.True(true);

[amjad.sahi]

@hcarvalho,

Please zip and attach both template XLSM files and output XLSM file. We will check your issue soon.

[hcarvalho]

Sample.zip (144.7 KB)
Thks, see the attach file.

[ahsaniqbalsidiqui]

@hcarvalho,
We have tried this code with your sample file but could not observe the issue while opening the output file in MS Excel. Could you please share error images, version of MS Excel, OS where this file is created and opened for our reference? We will observe the issue here and provide our feedback accordingly.

[hcarvalho]

When open FileAFileB.xlsm and click button “Ler”, occors compile error, its simple click Compile VbaProject, the project works.

Image 560.png (104.5 KB)

version: Excel 2019
SO: Windows 10

[ahsaniqbalsidiqui]

@hcarvalho,
Thank you for the feedback. We have observed this issue and logged it in our database for further investigation. You will be notified here once any update is ready for sharing.

This issue is logged as:
CELLSNET-48796 - VBA project raises error after combining the excel files

[hcarvalho]

We need this solution work´s, so can buy the component that will help us in a big project, let´s do a lot of joins excel file with Macro and VBA.
We count on your support

THKS @ahsaniqbalsidiqui

[amjad.sahi]

@hcarvalho,

Thanks for considering Aspose.

Sure, we will try to figure your issue out soon. Hopefully after fixing the issue you won’t find such an issue regarding merging macros/vba codes.

Once we have a fix or other updates on it, we will let you know.

[ahsaniqbalsidiqui]

@hcarvalho,
We have investigated the issue in detail and here are our findings:

1. Please compile the VBA project when loading files in MS Excel.
   So you have to add the following codes to VBA project and In MS Excel ,check Options ->Trust Center->Macro settings->Trust Access to the VBA Object Model, otherwise 1004 error.

    Workbook workbook = new Workbook(dir + "FileAFileB.xlsm");
    VbaProject vba = workbook.VbaProject;
    string x = "Sub Compiler()\r\n Dim objVBECommandBar As Object\r\nSet objVBECommandBar = Application.VBE.CommandBars\r\n    Set compileMe = objVBECommandBar.FindControl(Type:= msoControlButton, ID:= 578)\r\n    compileMe.Execute\r\nEnd Sub";
    vba.Modules[0].Codes = vba.Modules[0].Codes + x + "\r\nPrivate Sub Workbook_Open()\r\nCompiler\r\nEnd Sub\r\n\r\n";
    //  vba.Modules[0].Codes = vba.Modules[0].Codes + x;
    workbook.Save(dir + "dest.xlsm", Aspose.Cells.SaveFormat.Xlsm);

2. Aspose.Cells only add and modify the setting of the VBA Project. We do not support parsing and building those VBA codes.
   So there will be many issues if you simply combine those codes.
   For example: there is the same “EstaPastaDeTrabalho” VBA module , you can not simply replace codes.
   If the two VBA objects contains same name of VBA module or macro, we do not support automatically change name and update the references in codes.
   So it’s better that you can combine all VBA pojects into a template file, then operate the template file with your data in Aspose.Cells.

[hcarvalho]

@ahsaniqbalsidiqui tks, i will follow your advise and continue with my tests.
So far now is very satisfied

[amjad.sahi]

@hcarvalho,

You are welcome.