Skip to content

Instantly share code, notes, and snippets.

@atifaziz
Last active August 26, 2024 05:54
Show Gist options
  • Save atifaziz/8819159 to your computer and use it in GitHub Desktop.
Save atifaziz/8819159 to your computer and use it in GitHub Desktop.
PowerShell script to export VBA project components (classes, modules, etc.) from an Excel workbook
# Copyright (c) 2014 Atif Aziz. All rights reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
function Export-ExcelProject
{
[CmdletBinding(SupportsShouldProcess = $true, ConfirmImpact = 'Low')]
param(
[Parameter(Mandatory = $true,
HelpMessage = 'Specifies the path to the Excel Workbook file')]
[string]$WorkbookPath,
[Parameter(HelpMessage = 'Specifies export directory')]
[string]$OutputPath,
[Parameter(HelpMessage = 'Regular expression pattern identifying modules to be excluded')]
[string]$Exclude,
[Parameter(HelpMessage = 'Export items that may be auto-named, like Class1, Module2, etc.')]
[switch]$IncludeAutoNamed = $false,
[switch]$Force = $false
)
function Get-MD5Hash($filePath)
{
$bytes = [IO.File]::ReadAllBytes($filePath)
$hash = [Security.Cryptography.MD5]::Create().ComputeHash($bytes)
[BitConverter]::ToString($hash).Replace('-', '').ToLowerInvariant()
}
$mo = Get-ItemProperty -Path HKCU:Software\Microsoft\Office\*\Excel\Security `
-Name AccessVBOM `
-EA SilentlyContinue | `
? { !($_.AccessVBOM -eq 0) } | `
Measure-Object
if ($mo.Count -eq 0)
{
Write-Warning 'Access to VBA project model may be denied due to security configuration.'
}
Write-Verbose 'Starting Excel'
$xl = New-Object -ComObject Excel.Application -EA Stop
Write-Verbose "Excel $($xl.Version) started"
$xl.DisplayAlerts = $false
$missing = [Type]::Missing
$extByComponentType = @{ 100 = '.cls'; 1 = '.bas'; 2 = '.cls' }
$outputPath = ($outputPath, (Get-Item .).FullName)[[String]::IsNullOrEmpty($outputPath)]
mkdir -EA Stop -Force $outputPath | Out-Null
try
{
# Open(Filename, [UpdateLinks], [ReadOnly], [Format], [Password], [WriteResPassword], [IgnoreReadOnlyRecommended], [Origin], [Delimiter], [Editable], [Notify], [Converter], [AddToMru], [Local], [CorruptLoad])
$wb = $xl.Workbooks.Open($workbookPath, $false, $true, `
$missing, $missing, $missing, $missing, $missing, $missing, $missing, $missing, $missing, $missing, $missing, `
$true)
$wb | Get-Member | Out-Null # HACK! Don't know why but next line doesn't work without this
$project = $wb.VBProject
if ($project -eq $null)
{
Write-Verbose 'No VBA project found in workbook'
}
else
{
$tempFilePath = [IO.Path]::GetTempFileName()
$vbcomps = $project.VBComponents
if (![String]::IsNullOrEmpty($exclude))
{
$verbose = ($PSCmdlet.MyInvocation.BoundParameters.ContainsKey('Verbose') -and $PSCmdlet.MyInvocation.BoundParameters['Verbose'].IsPresent)
if ($verbose)
{
$vbcomps | ? { $_.Name -match $exclude } | % { Write-Verbose "$($_.Name) will be excluded" }
}
$vbcomps = $vbcomps | ? { $_.Name -notmatch $exclude }
}
$vbcomps | % `
{
$vbcomp = $_
$name = $vbcomp.Name
$ext = $extByComponentType[$vbcomp.Type]
if ($ext -eq $null)
{
Write-Verbose "Skipped component: $($name)"
}
elseif (!$includeAutoNamed -and $name -match '^(Form|Module|Class|Sheet)[0-9]+$')
{
Write-Verbose "Skipped possibly auto-named component: $name"
}
else
{
$vbcomp.Export($tempFilePath)
$exportedFilePath = Join-Path $outputPath "$name$ext"
$exists = Test-Path $exportedFilePath -PathType Leaf
if ($exists)
{
$oldHash = Get-MD5Hash $exportedFilePath
$newHash = Get-MD5Hash $tempFilePath
$changed = !($oldHash -eq $newHash)
$status = ('Unchanged', 'Conflict', 'Unchanged', 'Changed')[[int]$changed + (2 * [int]$force.IsPresent)]
}
else
{
$status = 'New'
}
if (($status -eq 'Changed' -or $status -eq 'New') `
-and $pscmdlet.ShouldProcess($name))
{
Move-Item -Force $tempFilePath $exportedFilePath
}
New-Object PSObject -Property @{
Name = $name;
Status = $status;
File = (Get-Item $exportedFilePath -EA Stop);
}
}
}
}
$wb.Close($false, $missing, $missing)
}
finally
{
$xl.Quit()
# http://technet.microsoft.com/en-us/library/ff730962.aspx
[Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$xl) | Out-Null
[GC]::Collect()
[GC]::WaitForPendingFinalizers()
}
}
<#
Export-ExcelProject Test.xlsm -Output $env:TEMP
#>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment