Detect Excel Project Compile Errors in VBS

Question

I have the following script in VBS, This script allow us to get module or components from a excel macro file that we save to svn and add them to a "seed" input file, and create a new macro file with all the code.

It has been working correctly, but now i want to try and check the files and the result to see if it compiles correctly and if not warn who ever run this script.

Any clues and ideas how i can get this done?

Set objExcel = CreateObject("Excel.Application")
Set objShell = CreateObject("WScript.Shell")
Set objFSO   = CreateObject("Scripting.FileSystemObject")


strCurrentDirectory = objShell.CurrentDirectory
Set objCurrentDir = objFSO.GetFolder(strCurrentDirectory)
Set objRootFolder = objCurrentDir
rootDirectory = objRootFolder.Path
WScript.Echo "Working Dirrectory "& rootDirectory

strOutputDir = rootDirectory & OUTPUT_DIR
strOutputName = BUILD_FILE
strInputDir = rootDirectory

with objExcel
    .Visible = false
    .DisplayAlerts = false
    .EnableEvents  = false
End With

with objExcel.Application
    .Visible = false
    .DisplayAlerts = false
    .EnableEvents  = false
End With

WScript.Echo "Updating From SVN"
objShell.Run "svn.exe update "&rootDirectory, 0, True

Set basFolder = objFSO.GetFolder(rootDirectory & BAS_DIR)
Set frmFolder = objFSO.GetFolder(rootDirectory & FRM_DIR)
Set clsFolder = objFSO.GetFolder(rootDirectory & CLS_DIR)

inPutFile = "C:\inputFile.xlsm"
outPutFile = "C:\output.xlsm"

WScript.Echo "Opening " & inPutFile
Set objWorkbook = objExcel.Workbooks.Open(inPutFile)

objWorkbook.Activate 
objWorkbook.Windows(1).Visible = False

Set components = objWorkbook.VBProject.VBComponents
WScript.Echo "Removing Old Components " & components.count
On Error Resume Next
For Each Element In components
    components.Remove Element
Next

Set components = objWorkbook.VBProject.VBComponents
WScript.Echo "Components Left " & components.count
WScript.Echo "Adding .frm Modules " & (frmFolder.Files.count / 2)
On Error Resume Next
For Each objFrmFile in frmFolder.Files 
    Select Case objFSO.GetExtensionName(objFrmFile.Name)
        Case "frm"
            components.Import objFrmFile.Path
    End Select
Next
WScript.Echo "Components count " & components.count


WScript.Echo "Adding .cls Modules " & clsFolder.Files.count
Set components = objWorkbook.VBProject.VBComponents
On Error Resume Next
For Each objClsFile in clsFolder.Files 
    components.Import objClsFile.Path
Next
WScript.Echo "Components count " & components.count


WScript.Echo "Adding .bas Modules " & basFolder.Files.count
On Error Resume Next
For Each objBasFile in basFolder.Files
    If objFSO.GetExtensionName(objBasFile.Name) = "bas" Then
        
        If objBasFile.Name = "sharePointConstG.bas" Then
            Set objSharePointConstG = objBasFile
        ElseIf objBasFile.Name = "sharePointConstS.bas" Then
            Set objSharePointConstS = objBasFile
        ElseIf objBasFile.Name = "sharePointConst.bas" Then
            Set objSharePointConst = objBasFile
        Else
            components.Import objBasFile.Path
        End If
    End If
Next

WScript.Echo "Saving " & outPutFile
objWorkbook.SaveAs outPutFile , 52

WScript.Echo "Closing Workbook "
objWorkbook.Close True

WScript.Echo "Quiting Excel "
objExcel.Quit
Set objExcel = Nothing

WScript.Echo "Finished Build Process."
WScript.Quit

Update: To confirm what i want to be able to do is have a ops member run this script and if something fails telling us, look this is failing, please check.

Does [this](https://stackoverflow.com/a/55613796/11609403) help? — leeharvey1, May 16 '23 at 12:30

Detect Excel Project Compile Errors in VBS

0 Answers0