用MSBuild更改.xla文件 [英] Change .xla File with MSBuild

问题描述

我正在尝试为我当前的项目创建一个构建脚本，其中包含一个Excel加载项。加载项包含一个带有变量version_Number的具有文件modGlobal的VBProject。每个构建都需要更改此数字。确切的步骤：

I'm trying to create a build script for my current project, which includes an Excel Add-in. The Add-in contains a VBProject with a file modGlobal with a variable version_Number. This number needs to be changed for every build. The exact steps:

1. 使用Excel打开XLA文档。


2. 切换到VBEditor模式。 （Alt + F11）


3. 打开VBProject，输入密码


4. 打开modGlobal文件


5. 更改变量的默认值为当前日期。


6. 关闭&保存项目。

1. Open XLA document with Excel.
2. Switch to VBEditor mode. (Alt+F11)
3. Open VBProject, entering a password.
4. Open modGlobal file.
5. Change variable's default value to the current date.
6. Close & save the project.

我对如何自动化进程感到失落。最好的我可以想出的是一个excel宏或Auto-IT脚本。我也可以编写一个自定义MSBuild任务，但这可能会变得棘手。还有其他人有其他建议吗？

I'm at a loss for how to automate the process. The best I can come up with is an excel macro or Auto-IT script. I could also write a custom MSBuild task, but that might get... tricky. Does anyone else have any other suggestions?

推荐答案

处理XLA文件版本控制的另一种方法是使用自定义属性文件属性。您可以使用COM访问和操作，如下所述： http://support.microsoft.com/?kbid = 224351 。

An alternative way of handling versioning of an XLA file is to use a custom property in Document Properties. You can access and manipulate using COM as described here: http://support.microsoft.com/?kbid=224351.

其优点是：

• 您可以检查版本号而不打开XLA文件

• You can examine the version number without opening the XLA file

您的构建机器上不需要Excel - 只有DsoFile.dll组件

You don't need Excel on your build machine - only the DsoFile.dll component

另一个选择是将版本号（可能还有其他配置数据）存储在XLA文件的工作表上。 XLA的用户将看不到工作表。过去使用的一种技术是将加载项作为XLS文件存储在源代码控制中，然后作为构建过程的一部分（例如，在Post-Build事件中）运行下面的脚本将其转换为XLA输出目录。此脚本可以轻松扩展，以便在保存之前更新工作表中的版本号。在我的情况下，我这样做是因为我的Excel加载项使用VSTO，Visual Studio不直接支持XLA文件。

Another alternative would be to store the version number (possibly other configuration data too) on a worksheet in the XLA file. The worksheet would not be visible to users of the XLA. One technique I have used in the past is to store the add-in as an XLS file in source control, then as part of the build process (e.g. in a Post-Build event) run the script below to convert it to an XLA in the output directory. This script could be easily extended to update a version number in a worksheet before saving. In my case I did this because my Excel Add-in used VSTO, and Visual Studio doesn't support XLA files directly.

'
'   ConvertToXla.vbs
'
'   VBScript to convert an Excel spreadsheet (.xls) into an Excel Add-In (.xla)
'
'   The script takes two arguments:
'
'   - the name of the input XLS file.
'
'   - the name of the output XLA file.
'
Option Explicit
Dim nResult
On Error Resume Next
nResult = DoAction
If Err.Number <> 0 Then 
    Wscript.Echo Err.Description
    Wscript.Quit 1
End If
Wscript.Quit nResult

Private Function DoAction()

    Dim sInputFile, sOutputFile

    Dim argNum, argCount: argCount = Wscript.Arguments.Count

    If argCount < 2 Then
        Err.Raise 1, "ConvertToXla.vbs", "Missing argument"
    End If

    sInputFile = WScript.Arguments(0)
    sOutputFile = WScript.Arguments(1)

    Dim xlApplication

    Set xlApplication = WScript.CreateObject("Excel.Application")
    On Error Resume Next 
    ConvertFileToXla xlApplication, sInputFile, sOutputFile
    If Err.Number <> 0 Then 
        Dim nErrNumber
        Dim sErrSource
        Dim sErrDescription
        nErrNumber = Err.Number
        sErrSource = Err.Source
        sErrDescription = Err.Description
        xlApplication.Quit
        Err.Raise nErrNumber, sErrSource, sErrDescription
    Else
        xlApplication.Quit
    End If

End Function

Public Sub ConvertFileToXla(xlApplication, sInputFile, sOutputFile)

    Dim xlAddIn
    xlAddIn = 18 ' XlFileFormat.xlAddIn

    Dim w
    Set w = xlApplication.Workbooks.Open(sInputFile,,,,,,,,,True)
    w.IsAddIn = True
    w.SaveAs sOutputFile, xlAddIn
    w.Close False
End Sub

这篇关于用MSBuild更改.xla文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！