Change .xla file with MSBuild

Question

I am trying to create a build script for my current project that includes an Excel add-in. The add-in contains a VBProject with a modGlobal file with a variable version_Number number. This number needs to be changed for each assembly. Exact steps:

• Open an XLA document using Excel.
• Switch to VBEditor mode. (Alt + F11)
• Open VBProject by entering the password.
• Open the modGlobal file.
• Change the default variable value to the current date.
• Close and save the project.

I do not understand how to automate the process. The best I can come up with is an excel macro or an Auto-IT script. I could also write a custom MSBuild task, but it can become ... complicated. Does anyone have any other suggestions?

Answer 1

An alternative way to handle XLA file versions is to use a custom property in the document properties. You can access and manage using COM as described here: http://support.microsoft.com/?kbid=224351 .

The advantages of this:

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

• You do not need Excel on your build machine - only the DsoFile.dll component

(, ) XLA. XLA. , , - XLS , (, Post-Build) script , XLA . script , . , Excel VSTO Visual Studio XLA .

'
'   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

Answer 2

100%, , . , , , .

1) ( ) Globals , .XLA. , . , XLA.

2) , ( : XLA-) . , XLA? XLA. 1.1 7.1 - 8.9 XLA.

3) XLA, ( , ), , , .

, , XLA.

Answer 3

You can modify the code in xla programmatically from Excel. You will need a link to the Microsoft Visual Basic component for extending applications .. '.

Examples on the Chip Pearson excellent site should get started.