使用Open XML从模板xlsm文件将vba代码复制到另一个xlsm文件 [英] Copy vba code to another xlsm file using Open XML from a template xlsm file

问题描述

我使用此代码将模板xlsm文件的VbaPart添加到另一个文件中. 当我打开vb部分时，将为每个工作表以及"ThisWorkBook"添加重复的条目.

I use this code to add VbaPart of the template xlsm file into another. When I open the vb section duplicate entries get added for each sheet as well as the "ThisWorkBook".

下面是其在开发人员标签中的外观的屏幕截图

Below is the screen shot of how it looks in the developer tab

下面是我使用的代码:

using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(convertDocumentFile, true))
{
    VbaProjectPart extendedPart = FindPart(myDoc);

    if (extendedPart != null)
      myDoc.DeletePart(extendedPart);
    if (vbaPart != null)
      myDoc.WorkbookPart.AddPart<VbaProjectPart>(vbaPart);
}

private static VbaProjectPart FindPart(SpreadsheetDocument mainPart)
{
    if (mainPart != null)
    {
        foreach (IdPartPair partPair in mainPart.WorkbookPart.Parts)
        {
            if (partPair.OpenXmlPart.RelationshipType == _wnsRelationShip.NamespaceName)
            {
               return partPair.OpenXmlPart as VbaProjectPart;
            }
        }
    }
    return null;
}

变量"convertDocumentFile"是需要在其中添加vbaProject部件的文件，而"vbaPart"是模板中的vbaProjectPart.

Where the variable "convertDocumentFile" is the file where the vbaProject part needs to be added and the "vbaPart" is the vbaProjectPart from the template.

推荐答案

我的代码未执行的原因是，当我转换为xlsm格式时缺少"CodeName"属性，因此我需要添加以下代码:

The reason my code did not execute was that the "CodeName" attribute was missing when I converted to xlsm format hence I need to add the following code:

myDoc.WorkbookPart.Workbook.WorkbookProperties.CodeName = "ThisWorkbook";

指向正确的工作簿后，vb代码正确执行.

After pointing to the correct workbook, the vb code executed correctly.

这篇关于使用Open XML从模板xlsm文件将vba代码复制到另一个xlsm文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！