Compile Excel through VBA Code

[LeanneGodney]

Hi there,

I have an application of hundreds of Excel macro files that all reference one central excel file. This central excel file acts like an add-in.

If I make changes to this main file it could have knock on effects on the individual macros using it.

I would like to be able to run some code that opens up each of the individual macros and runs a compile on it. If it compiles, great, if not, I'd like it to flat that file to me.

I know how to do everything other than to get it to compile. Manually, from the VBA screen you click Debug --> Compile. This is all good, but I don't want to have to manually dot his for 100's of files!

Any help would be most appreciated. Thanks!

Leanne

 

[kjv1611]

If all of your macros are within the same workbook, say your "Personal Macro Workbook", then you only have to do Debug-Compile one time while pointing to your Personal Macro Workbook, and it will compile all included modules, whether that's 1 or 10,001.

--

"If to err is human, then I must be some kind of human!" -Me

 

[LeanneGodney]

Yes, I realise that. That isn't an option.

 

[MakeItSo]

How about programmatically cycling through all your Excels, saving their modules to text files, concatenating these text files into one, and compiling that ONE?

Here's some code. It was originally written to cycle through Access forms**, now slightly adapted as an Excel macro, but untested:

Public Function SaveCodeModules()
Dim xl As Excel.Workbook
Dim i As Integer, vbComp, fs, a

With Application.FileSearch
    .LookIn = InputBox("Please enter path to folder containing the Excel workbooks")
    .Filename = "*.xls"
    .FileType = msoFileTypeExcelWorkbooks
    .Execute
End With

For i = 1 To Application.FileSearch.FoundFiles.Count
    Set xl = Excel.Workbooks.Open(Application.FileSearch.FoundFiles(i), False)
    For Each vbComp In xl.VBProject.VBComponents
        With vbComp
            strCode = .CodeModule.Lines(1, .CodeModule.CountOfLines)
            Set fs = CreateObject("Scripting.FileSystemObject")
            Set a = fs.CreateTextFile("C:\Code\" & .Name & ".txt")
            a.Write strCode
            a.Close
        End With
    Next vbComp
    Set xl = Nothing
Next i

End Function

Then you could concatenate all code files with a simple dos command in that folder:

copy *.txt allcodes.txt

Paste this merged code into a VBA editor and compile.

Would that do the trick?

** credits to TT member DeanWilliams

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]

 

[LeanneGodney]

Hello,

All macros are self contained, and have their own declaratives, etc etc, so putting them into one would not work.

For anyone else who may want to do this, I've figured out how to do it:

You ahve to create a new instance of Excel, cos you can't compile code while it's running.

You can't do this if the VBA project has a password. I wrote some code that opens up the project proeprties so you can manually remove the password should there be one. Post up here if you want a copy of it.

This works a charm, and loops through all my files, compiling, and saving. It the compile breaks then the VBE opens up for the New Instance of Excel. I've put code in to check if this screen is visible so that the code can stop and wait for you to fix whatever was stopping the compile.

You need a reference to Windows script Host Model and VBA Extensibility.



Dim MyFS As New FileSystemObject
Dim MyBar As CommandBarPopup
Dim MyButton As CommandBarButton
Dim MyPath as string

Dim MyFile As File
Dim MyFolder As Folder

MyPath = "H:\Program Files\Client Assistant\Source\London\"

Set MyFolder = MyFS.GetFolder(MyPath)
Set NewExcel = New Excel.Application

For Each MyFile In MyFolder.Files

Set MyBook = NewExcel.Workbooks.Open(MyFile.Path)
Set MyBar = MyBook.VBProject.VBE.CommandBars("Menu Bar").Controls("Debug")
Set MyButton = MyBar.Controls(1)

MyButton.Execute

If MyBook.VBProject.VBE.MainWindow.Visible = True Then
Stop
End If

MyBook.Close savechanges:=True

Next MyFile