Automating features

 VS   VBA 

For VBA and Visual Studio, several features of MZ-Tools can be automated from an external script. Unfortunately, VB6 cannot be automated directly.

Some scenarios or features where you may want to use external automation are:

• To add line numbers (or remove them) to the whole project. For example, you may want to avoid line numbers during development (because they are not needed) and add them only to a copy of the document when you are going to deploy it to the end users.
• To get the statistics of a project.
• To get the results of the quality review feature.
• To generate XML or HTML documentation.

For VBA, to automate MZ-Tools you need:

• To create an instance of the desired Office application (such as "Excel.Application", "Access.Application", etc.) and (optionally) make it visible.
• To load the desired document (Excel file, Access database, etc.).
• To get the instance of the VBA editor and (optionally) to make it visible.
• To get the "MZTools8VBA" add-in from the collection of add-ins of the VBA editor.
• To ensure that the MZ-Tools add-in is loaded.
• To get its inner object of the add-in, which is the actual instance of MZ-Tools.
• To call the ExecuteCommand method of the MZ-Tools instance passing the name of a command of MZ-Tools (see list below) and optionally up to two command-line arguments: the first one is the full path of a log file (valid for all commands) and the second one is the full path of an output file (only required for commands that generate results). The ExecuteCommand method returns 0 if the execution was successful, and a value greater than 0 if there were errors.
• To quit the Office application.

The list of commands that can be automated is the following:

Here you have a Visual Basic Script (.vbs) that automates Microsoft Access to add line numbers to a database file:
 

Const officeProgId = "Access.Application"

Const addinProgId = "MZTools8VBA"

Const commandName = "MZTools8.AddLineNumbers"

Dim fso

Dim app

Dim addIn

Dim addInObject

Dim officeFile

Dim logFile

Dim commandLineArgs

Dim result

' Set variables

officeFile = "C:\Users\Public\DatabaseAutomation.accdb"

logFile = "C:\Users\Public\MZTools8.log"

commandLineArgs = logFile

' Delete files if they exist

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(logFile) Then

  fso.DeleteFile(logFile)

End If

' Open the Office document

Set app = CreateObject(officeProgId)

app.OpenCurrentDatabase(officeFile)

' Make Office and VBA editor visible

app.Visible = True

app.VBE.MainWindow.Visible = True

' Get the MZ-Tools add-in

Set addIn = app.VBE.AddIns.Item(addinProgId)

' Ensure it is loaded

addIn.Connect = True

' Get its inner object  

Set addInObject = addIn.Object

' Execute the command

result = addInObject.ExecuteCommand(commandName, commandLineArgs)

' Exit the Office application  

app.Quit

If result = 0 Then

 WSCript.Echo "Done."

Else

 WSCript.Echo "Errors happened, check the log file."

End If

Here you have a PowerShell script (.ps1) that automates Microsoft Excel to generate its statistics:
 

# Set constants

$officeProgId = "Excel.Application"

$addinProgId = "MZTools8VBA"

$commandName = "MZTools8.Statistics"

$officeFile = "C:\Users\Public\BookAutomation.xlsm"

$logFile = "C:\Users\Public\MZTools8.log"

$outputFile = "C:\Users\Public\MZTools8Statistics.txt"

$commandLineArgs = "$logFile $outputFile"

# Delete files if they exist

$fso = New-Object -ComObject "Scripting.FileSystemObject"

if ($fso.FileExists($logFile))

{

  $fso.DeleteFile($logFile)

}

if ($fso.FileExists($outputFile))

{

  $fso.DeleteFile($outputFile)

}

# Open the Office document

$app = New-Object -ComObject $officeProgId

$workbooks = $app.Workbooks

$workbook = $workbooks.Open($officeFile)

# Make Office and VBA editor visible

$app.Visible = $true

$vbe = $app.VBE

$mainWindow = $vbe.MainWindow

$mainWindow.Visible = $true

# Get the MZ-Tools add-in

$addIns = $vbe.AddIns

$addIn = $addIns.Item($addinProgId)

# Ensure it is loaded

$addIn.Connect = $true

# Get its inner object  

$addInObject = $addIn.Object

# Execute the command

$result = $addInObject.ExecuteCommand($commandName, $commandLineArgs)

# Exit the Office application  

$app.Quit()

# Release COM objects to free up resources

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($addInObject) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($addIn)       | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($addIns)      | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($mainWindow)  | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($vbe)         | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)    | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbooks)   | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($app)         | Out-Null

if ($result -eq 0)

{

  Write-Host "Result = $result. Done."

}

else

{

  Write-Host "Result = $result. Errors happened, check the log file."

}

Read-Host "Press Enter to continue..."

For Visual Studio, to automate MZ-Tools you need:

• To create an instance of Visual Studio (EnvDTE.DTE) and (optionally) make it visible.
• To load the desired solution.
• To call the ExecuteCommand method of the EnvDTE.DTE instance passing the name of a command of MZ-Tools (see list below), and optionally up to two command-line arguments: the first one is the full path of a log file (valid for all commands) and the second one is the full path of an output file (only required for commands that generate results).
• To quit Visual Studio.

For Visual Studio 2015 and higher the list of commands that can be automated is the following:

• "MZTools.AddLineNumbers"
• "MZTools.RemoveLineNumbers
• "MZTools.ReviewQuality"
• "MZTools.Statistics"
• "MZTools.GenerateHtmlDocumentation"
• "MZTools.GenerateXmlDocumentation"

For Visual Studio 2013 and lower the list of commands that can be automated is the following:

• "MZTools8.AddLineNumbers"
• "MZTools8.RemoveLineNumbers
• "MZTools8.ReviewQuality"
• "MZTools8.Statistics"
• "MZTools8.GenerateHtmlDocumentation"
• "MZTools8.GenerateXmlDocumentation"

Here you have a Visual Basic Script (.vbs) that automates Visual Studio 2022 to get the results of the Quality Review feature:
 

Const progId = "VisualStudio.DTE.17.0" ' VS 2022

Const commandName = "MZTools.ReviewQuality"

Dim fso

Dim dte

Dim solution

Dim logFile

Dim outputFile

Dim commandLineArgs

' Set variables

solution= "C:\Users\Public\MySolution\MySolution.sln"

logFile = "C:\Users\Public\MZTools8.log"

outputFile = "C:\Users\Public\MZTools8QualityReviewResult.txt"

commandLineArgs = logFile & " " & outputFile

' Delete files if they exist

Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(logFile) Then

  fso.DeleteFile(logFile)

End If

If fso.FileExists(outputFile) Then

  fso.DeleteFile(outputFile)

End If

' Open the solution

Set dte = CreateObject(progId)

dte.Solution.Open(solution)

' Make the IDE visible

dte.MainWindow.Visible = True

' Execute the command

Call dte.ExecuteCommand(commandName, commandLineArgs)

' Exit the IDE  

dte.Quit

WSCript.Echo "Done"