用C++管理Excel宏启用工作簿的VBA代码

在C++中添加VBA模块

Aspose.Cells允许通过Aspose.Cells添加新的VBA模块和宏代码。请使用Workbook.VbaProject.Modules.Add()方法在工作簿中添加新的VBA模块。

以下示例代码创建一个新工作簿，添加一个VBA模块和宏代码，并以XLSM格式保存。打开输出的XLSM文件在Microsoft Excel中，点击开发人员 > Visual Basic菜单命令，你会看到名为"TestModule"的模块，里面有以下宏代码。

 Sub ShowMessage()

    MsgBox "Welcome to Aspose!"

End Sub

以下是生成带有VBA模块和宏代码的输出XLSM文件的示例代码。

#include <iostream>
#include "Aspose.Cells.h"

using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    // Source directory path
    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");

    // Output directory path
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    // Create new workbook
    Workbook workbook;

    // Access first worksheet
    Worksheet worksheet = workbook.GetWorksheets().Get(0);

    // Add VBA Module
    int32_t idx = workbook.GetVbaProject().GetModules().Add(worksheet);

    // Access the VBA Module, set its name and codes
    VbaModule module = workbook.GetVbaProject().GetModules().Get(idx);
    module.SetName(u"TestModule");

    U16String codes = u"Sub ShowMessage()\r\n"
                      u"    MsgBox \"Welcome to Aspose!\"\r\n"
                      u"End Sub";
    module.SetCodes(codes);

    // Save the workbook
    U16String outputPath = outDir + u"output_out.xlsm";
    workbook.Save(outputPath, SaveFormat::Xlsm);

    std::cout << "VBA module added and workbook saved successfully!" << std::endl;

    Aspose::Cells::Cleanup();
    return 0;
}

在C++中修改VBA或宏

您可以使用Aspose.Cells修改VBA或宏代码。Aspose.Cells已添加以下名称空间和类来读取和修改Excel文件中的VBA项目。

Aspose::Cells::Vba
VbaProject
VbaModuleCollection
VbaModule

本文将向您展示如何使用Aspose.Cells更改源Excel文件中的VBA或宏代码。

以下示例代码加载含有VBA或宏代码的源Excel文件：

 Sub Button1_Click()

    MsgBox "This is test message."

End Sub

经过Aspose.Cells示例代码执行后，VBA或宏代码将被修改成这样：

 Sub Button1_Click()

    MsgBox "This is Aspose.Cells message."

End Sub

您可以从以下链接下载源Excel文件和输出Excel文件。

#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    U16String inputFilePath = srcDir + u"sample.xlsm";
    U16String outputFilePath = outDir + u"output_out.xlsm";

    Workbook workbook(inputFilePath);

    VbaProject vbaProject = workbook.GetVbaProject();
    VbaModuleCollection modules = vbaProject.GetModules();

    for (int i = 0; i < modules.GetCount(); ++i)
    {
        VbaModule module = modules.Get(i);
        U16String code = module.GetCodes();
        U16String searchStr = u"This is test message.";
        U16String replaceStr = u"This is Aspose.Cells message.";

        if (code.IndexOf(searchStr) != -1)
        {
            U16String newCode;
            const char16_t* codeData = code.GetData();
            const char16_t* searchData = searchStr.GetData();
            int codeLen = code.GetLength();
            int searchLen = searchStr.GetLength();

            int pos = 0;
            int searchPos;

            while ((searchPos = code.IndexOf(searchStr)) != -1)
            {
                for (int j = pos; j < searchPos; j++)
                {
                    newCode += codeData[j];
                }

                newCode += replaceStr;

                pos = searchPos + searchLen;
            }

            for (int j = pos; j < codeLen; j++)
            {
                newCode += codeData[j];
            }

            module.SetCodes(newCode);
        }
    }

    workbook.Save(outputFilePath);

    std::cout << "VBA module codes updated successfully." << std::endl;

    Aspose::Cells::Cleanup();
    return 0;
}

高级主题

使用Smart标记智能导入和放置数据C++ 用C++将XML导入Excel工作簿