Building an XLA from Source Programmatically

Question

I have a (version controlled) folder of exported components of a VBA addin (i.e. a lot of cls, frm, frx, bas and txt files). Can someone give me a build script (can be a VB script, or something more complicated) that takes the name of this folder and produces a (working) XLA?

I know you can programatically modify XLA files (e.g. this question) so it shouldn't be too hard to write a script that creates an empty XLA, and then loops over all the files in the folder to add them to it...?

Thanks,

Nick

(edit: Excel 2003 if it makes any difference)

Answer 1

I use a very simple VBA macro within an Excel workbook to do essentially the same thing, only I don't have any forms, and I have some unit test information in formatted text files. Since VBA is pretty similar to VBScript and the code below uses file system and Excel object model stuff that should be available to VBScript, perhaps it will be useful to you.

This is an excerpt from a .bas module. I drop it in a blank workbook, and then drop my text files in whatever directory that workbook is in, and then run the import. It should be pretty easy to repurpose it for something more formal, whether an add-in that targets a different, newly created, workbook or a script that does the whole build outside of Excel:

'...

Private fs_ As FileSystemObject

'...

Private Sub import_()
    On Error GoTo EH

    Set fs_ = New FileSystemObject

    Call importTests_(Application.ThisWorkbook, Application.ThisWorkbook.path)
    Call importCode_(Application.ThisWorkbook, Application.ThisWorkbook.path)

    Exit Sub

EH:
    Debug.Print "Error in import_: " & Err.Number
End Sub

'...
'stuff about importing tests ignored...
'...

Private Sub importCode_(wbk As Workbook, folderName As String)
    Dim folderObj As Folder
    Set folderObj = fs_.GetFolder(folderName)

    Dim fileExt As String

    Dim fileObj As File
    For Each fileObj In folderObj.Files
        fileExt = fs_.GetExtensionName(fileObj.name)

        If fileExt = "bas" Or fileExt = "cls" Then
            Call wbk.VBProject.VBComponents.Import(fileObj.path)
        End If
    Next fileObj
End Sub

'...

I don't know what the issues are with importing forms since I never use them in Excel.