Go to Qiita Advent Calendar Top
LoginSignup
6
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@ShortArrow(さぼったろう)

セル操作をするPowershellスクリプトを生成実行するVBAモジュールをPowershellで書き込む

Last updated at Posted at 2021-11-09

きっかけ

もう VBA を書きたくない。Powershell が好きだから、Powershell で何とかしたい。
:small_red_triangle_down:
でもファイル増えるのはイヤ。ユーザーに配るのは Excel ファイル１つにしたい。
:small_red_triangle_down:
Powershell ファイル生成実行する VBA にしたら？
:small_red_triangle_down:
いや VBA 触りたくないんだよ。
:small_red_triangle_down:
それなら、Powershell で、そのVBAコードを書き込めれば、全部 Powershell として扱えるよね。
:small_red_triangle_down:
いまここ

VBAでVBAを書き込む

Powershell で動作させる前に、まずは VBA で同じことができるか試してみます。

プロジェクト構成

image.png

Module1にはCreateModuleサブプロシージャが書かれています。この、CreateModuleサブプロシージャを使って、MyModuleの中に、myfuncサブプロシージャを書き込んでみます。

image.png
image.png

Excel の マクロボタンから、CreateModuleを実行してみます。
実行後に、もう一度 Excel の マクロボタンを押してみましょう。

image.png

すると、一覧にmyfuncが増えています。成功ですね:smile:
そのまま、myfuncを実行してみましょう。

image.png

へい:smile:

コード

さて、肝心のコードです。

Module1
Sub CreateModule()
    Dim Code As String
    Dim ModuleName As String: ModuleName = "myModule"
    Dim existModuleName As Boolean: existModuleName = False
    Code = _
        "sub myfunc()" + vbNewLine + _
        vbTab + "msgbox ""Hey!""" + vbNewLine + _
        "end sub"
    Dim VBComponentItem As VBComponent
    With ThisWorkbook.VBProject
        For Each VBComponentItem In .VBComponents
            If ModuleName = VBComponentItem.Name Then existModuleName = True
        Next
        With .VBComponents
            If existModuleName Then .Remove .Item(ModuleName)
            With .Add(vbext_ct_StdModule)
                .Name = ModuleName
                .CodeModule.AddFromString Code
            End With
        End With
    End With
End Sub

上記のコードに含まれる、

    Code = _
        "sub myfunc()" + vbNewLine + _
        vbTab + "msgbox ""Hey!""" + vbNewLine + _
        "end sub"

という部分が、書き込む VBA コードです。

実際に書き込まれた後、VBA コードはこうなります。

myModule
Sub myfunc()
    MsgBox "Hey!"
End Sub

VBA を VBA で操作するには、VBProjectとかVBComponentあたりを使う必要があります。これを利用可能にしてくれる参照設定が必要です。

image.png

ちょっとめんどくさいので、RubberDuckを使うと簡単に追加することができます。

image.png

PowershellでVBAを書き込む

上記の VBA を Powershell に落とし込んでみました。

OfficeをCOM Object経由でPowershellから扱うときの面倒を少しマシにするを参考にしています。

write-vba.ps1
using namespace System.Management.Automation
using namespace System.Collections.Generic
using namespace System.Runtime.InteropServices
using namespace Microsoft.Office.Interop.Excel

# VBAのEnumを使うためにアセンブリをロード

# VBAのEnumを使うためにアセンブリをロード

@(
    "C:\Windows\assembly\GAC_MSIL\Microsoft.VisualBasic\*\Microsoft.VisualBasic.dll"
    "C:\Windows\assembly\GAC_MSIL\office\*\OFFICE.DLL"
    "C:\Windows\assembly\GAC_MSIL\Microsoft.Vbe.Interop\*\Microsoft.Vbe.Interop.dll"
    "C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\*\Microsoft.Office.Interop.Excel.dll"
).ForEach{
    Add-Type -path $_
    Write-Host $_
}

# COMObjectに拡張メソッドを追加

Update-TypeData -TypeName System.__ComObject -MemberType ScriptMethod -MemberName Tee -Value {
    param(
        [Stack[WeakReference]]
        $stack
    )
    $stack.Push([WeakReference]::new($this))

    Write-Output $this
}

# Comを解放するために使う弱参照のスタック

$refs = [Stack[WeakReference]]::new()

function CreateModule {
    param(
        [string]$fileName
    )
    $filePath = Join-Path $pwd $fileName
    $excel = New-Object -ComObject Excel.Application
    [string]$ModuleName = "myModule"
    [bool]$existModuleName = $false
    [string]$Code = "sub myfunc()`n`tmsgbox`"Hey!`"`nend sub"

    [ExcelSecurityRegistry]$excelRegistry = [ExcelSecurityRegistry]::new()
    $excelRegistry.SetWritable()

    $workbook = $excel.Workbooks.Open($filePath)
    foreach ($item in $workbook.VBProject.VBComponents) {
        if ($item.Name -eq $ModuleName) {
            $existModuleName = $true
        }
    }
    if ($existModuleName) {
        $workbook.VBProject.VBComponents.Remove($workbook.VBProject.VBComponents.Item($ModuleName))
    }
    $VBComponent = $workbook.VBProject.VBComponents.Add([Microsoft.Vbe.Interop.vbext_ComponentType]::vbext_ct_StdModule)
    $VBComponent.Name = $ModuleName
    $VBComponent.CodeModule.AddFromString($Code)
    $workbook.Save()
    $excel.Quit()

    $excelRegistry.SetToBefore()
}

class ExcelSecurityRegistry {
    [int]$defaultAccessVBOM
    [int]$defaultVBAWarnings
    [string]$excelRegistryPath = "HKCU:\Software\Microsoft\Office\15.0\excel\Security"
    [void]SetWritable() {
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            AccessVBOM -Value 1 -Force | Out-Null
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            VBAWarnings -Value 1 -Force | Out-Null
    }
    [void]SetToBefore() {
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            AccessVBOM -Value $this.defaultAccessVBOM -Force | Out-Null
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            VBAWarnings -Value $this.defaultVBAWarnings -Force | Out-Null
    }
    ExcelSecurityRegistry() {
        $this.defaultAccessVBOM = (Get-ItemProperty -Path $this.excelRegistryPath).AccessVBOM
        $this.defaultVBAWarnings = (Get-ItemProperty -Path $this.excelRegistryPath).VBAWarnings
    }
}

CreateModule -fileName .\ExecutePwsh.xlsm

While ($refs.Count) {
    # スタックから弱参照を取得

    $comRef = $refs.Pop()

    # 解放するCOMを参照してる変数を全て取得

    $comVar = (Get-Variable).where{ [object]::ReferenceEquals($comRef.Target, $_.Value) }

    # Applicationオブジェクトであるかの判定

    $isApp = $comRef.Target -is [Microsoft.Office.Interop.Excel.Application]

    # アプリケーションの終了前にガベージ コレクトを強制

    if ($isApp) {
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        [System.GC]::Collect()
        $comRef.Target.Quit()
    }

    # COMObjectの解放

    # COMObjectの解放

    while ([Marshal]::ReleaseComObject($comRef.Target)) { }
    $comRef.Target = $null

    # 変数を削除

    $comVar | Remove-Variable
    Remove-Variable comRef

    # Application オブジェクトのガベージ コレクトを強制

    if ($isApp) {
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        [System.GC]::Collect()
    }
}

動作は、上記の「 VBA で VBA を書き込む」ものと同じです。しかし、急に長くなりましたね:sob:

それと、"HKCU:\Software\Microsoft\Office\15.0\excel\Security"という部分は、動作環境にある Excel のバージョンによって異なります。改善ポイントですね。

Add-Type -Path $item
`~~~~~~~~~~~~~~~~~~~~`
Assembly with same name is already loaded

Add-Typeにてエラーが出ます。しかし、ユーザー配布時には出ないエラーなので、とりあえず無視します。

VBAでPowershellを生成実行する

それでは次に、VBA で Powershellを書いて実行してみましょう。

まずは、Powershellを生成するCreatePayloadサブプロシージャを書きます。

Module1
Private Sub CreatePayload()
    Dim s
    Dim n
    s = Environ("TEMP") + "\temp.ps1"
    n = FreeFile
    Open s For Output As #n

    Print #n

    Print #n

    Print #n

    Print #n

    Print #n

    Close #n

End Sub

これが、$env:TEMPフォルダにtemp.ps1を生成してくれます。
中身はこんな感じになります。

%temp%/temp.ps1
Write-Host (Get-Location).Path
Write-Host this

次に、上記のtemp.ps1を起動するためのバッチファイルを生成する、CreateLauncherサブプロシージャを作成します。

Module1
Private Sub CreateLauncher()
    Dim s
    Dim n
    s = Environ("TEMP") + "\temp.cmd"
    n = FreeFile
    Open s For Output As #n

    Print #n

    Print #n

    Print #n

    Print #n

    Print #n

    Close #n

End Sub

これで、$env:TEMPフォルダにtemp.cmdが生成されます。

%temp%/temp.cmd
@echo off
cd %temp%
pwsh -NoProfile -ExecutionPolicy Unrestricted ./temp.ps1
pause > nul
exit

最後に、上記２つのサブプロシージャで生成したファイルを実行するStartModuleサブプロシージャを書きます。

Module1
Sub StartModule()
    Call CreateLauncher
    Call CreatePayload

    Dim WshObject As WshShell
    Dim sPath

    sPath = "%temp%/temp.cmd"

    Set WshObject = New WshShell

    Call WshObject.Run(sPath, 1, WaitOnReturn:=True)
End Sub

StartModuleを実行すると結果はこんな感じです。

image.png

ステキですね:blush:

かけ合わせる①

「 VBA で Powershell を生成実行」
「 Powershell で VBA を書く」

はできたので、

次は、「 Powershell で VBA （ VBA で Powershell を生成実行）を書く」ですね。

先ほどの Powershell の中で、

write-vba.ps1
[string]$Code = "sub myfunc()`n`tmsgbox`"Hey!`"`nend sub"

この部分を書き換えます。

write-vba.ps1
    [string]$Code = ""
    $codeList = @(
        "Sub CreatePayload()"
        "Dim s"
        "Dim n"
        "s = Environ(`"TEMP`") + `"\temp.ps1`""
        "n = FreeFile"
        "Open s For Output As #n"

        "Print #n"

        "Print #n"

        "Print #n"

        "Print #n"

        "Print #n"

        "Close #n"

        "End Sub"
    )
    foreach ($item in $codelist) {
        $Code += $item + "`n"
    }

Powershellを実行してみると、

image.png

myfuncが上書きされて、CreatePayloadサブプロシージャが書き込めていることが分かります。

いいですね:smile:

かけ合わせる②

「 VBA で Powershell を生成実行」
「 Powershell で VBA を書く」
「 Powershell で VBA （ VBA で Powershell を生成実行）を書く」

はできたので、

次は、「 Powershell で VBA （ VBA で Powershell （ Powershell で Excel を操作）を生成実行）を書く」ですね。

「 Powershell で Excel を操作」できる Powershell を書いて、
そのファイルを逐一 VBA に反映できるようにしていきましょう。

まず、先ほどのCreateModuleは目障りなので間違えて実行しないように、Privateを付与しておきます。

Module1
Private Sub CreateModule()

Module1のCreatePayloadもコメントアウトするか消しておきます。

Module1
'Private Sub CreatePayload()
'    Dim s
'    Dim n
'    s = Environ("TEMP") + "\temp.ps1"
'    n = FreeFile
'    Open s For Output As #n

'    Print #n

'    Print #n

'    Print #n

'    Print #n

'    Print #n

'    Close #n

'End Sub

ここまで来たら、write-vba.ps1を書き換えます。

write-vba.ps1
using namespace System.Management.Automation
using namespace System.Collections.Generic
using namespace System.Runtime.InteropServices
using namespace Microsoft.Office.Interop.Excel

# VBAのEnumを使うためにアセンブリをロード

# VBAのEnumを使うためにアセンブリをロード

foreach ($item in @(
        "C:\Windows\assembly\GAC_MSIL\Microsoft.VisualBasic\*\Microsoft.VisualBasic.dll"
        "C:\Windows\assembly\GAC_MSIL\office\*\OFFICE.DLL"
        "C:\Windows\assembly\GAC_MSIL\Microsoft.Vbe.Interop\*\Microsoft.Vbe.Interop.dll"
        "C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\*\Microsoft.Office.Interop.Excel.dll"
    )) {
    Add-Type -Path $item
}

# COMObjectに拡張メソッドを追加

Update-TypeData -TypeName System.__ComObject -MemberType ScriptMethod -MemberName Tee -Value {
    param(
        [Stack[WeakReference]]
        $stack
    )
    $stack.Push([WeakReference]::new($this))

    Write-Output $this
}

# Comを解放するために使う弱参照のスタック

$refs = [Stack[WeakReference]]::new()

function CreateModule {
    param(
        [string]$fileName
    )
    $filePath = Join-Path $pwd $fileName
    $excel = New-Object -ComObject Excel.Application
    [string]$ModuleName = "PayloadCreater"
    [bool]$existModuleName = $false
    [string[]]$codeList = Get-Content "./main.ps1"
    [string]$Code = ""
    $codelistBeforePrint = @(
        "Static Sub CreatePayload()"
        "Dim s"
        "Dim n"
        "s = Environ(`"TEMP`") + `"\temp.ps1`""
        "n = FreeFile"
        "Open s For Output As #n"

    )
    $codelistAfterPrint = @(
        "Close #n"

        "End Sub"
    )

    for ($i = 0; $i -lt $codeList.Count; $i++) {
        $codeList[$i] = ("Print #n, `"" + ($codeList[$i] -replace "`"","`"`"") + "`"")

    }

    $codeList = $codelistBeforePrint + $codeList + $codelistAfterPrint

    foreach ($item in $codelist) {
        $Code += $item + "`n"
    }

    [ExcelSecurityRegistry]$excelRegistry = [ExcelSecurityRegistry]::new()
    $excelRegistry.SetWritable()

    $workbook = $excel.Workbooks.Open($filePath)
    foreach ($item in $workbook.VBProject.VBComponents) {
        if ($item.Name -eq $ModuleName) {
            $existModuleName = $true
        }
    }
    if ($existModuleName) {
        $workbook.VBProject.VBComponents.Remove($workbook.VBProject.VBComponents.Item($ModuleName))
    }
    $VBComponent = $workbook.VBProject.VBComponents.Add([Microsoft.Vbe.Interop.vbext_ComponentType]::vbext_ct_ClassModule)
    $VBComponent.Name = $ModuleName
    $VBComponent.CodeModule.AddFromString($Code)
    $workbook.Save()
    $excel.Quit()

    $excelRegistry.SetToBefore()
}

class ExcelSecurityRegistry {
    [int]$defaultAccessVBOM
    [int]$defaultVBAWarnings
    [string]$excelRegistryPath = "HKCU:\Software\Microsoft\Office\15.0\excel\Security"
    [void]SetWritable() {
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            AccessVBOM -Value 1 -Force | Out-Null
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            VBAWarnings -Value 1 -Force | Out-Null
    }
    [void]SetToBefore() {
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            AccessVBOM -Value $this.defaultAccessVBOM -Force | Out-Null
        New-ItemProperty -Path $this.excelRegistryPath -Name `
            VBAWarnings -Value $this.defaultVBAWarnings -Force | Out-Null
    }
    ExcelSecurityRegistry() {
        $this.defaultAccessVBOM = (Get-ItemProperty -Path $this.excelRegistryPath).AccessVBOM
        $this.defaultVBAWarnings = (Get-ItemProperty -Path $this.excelRegistryPath).VBAWarnings
    }
}


CreateModule -fileName .\ExecutePwsh.xlsm

While ($refs.Count) {
    # スタックから弱参照を取得

    $comRef = $refs.Pop()

    # 解放するCOMを参照してる変数を全て取得

    $comVar = (Get-Variable).where{ [object]::ReferenceEquals($comRef.Target, $_.Value) }

    # Applicationオブジェクトであるかの判定

    $isApp = $comRef.Target -is [Microsoft.Office.Interop.Excel.Application]

    # アプリケーションの終了前にガベージ コレクトを強制

    if ($isApp) {
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        [System.GC]::Collect()
        $comRef.Target.Quit()
    }

    # COMObjectの解放

    # COMObjectの解放

    while ([Marshal]::ReleaseComObject($comRef.Target)) { }
    $comRef.Target = $null

    # 変数を削除

    $comVar | Remove-Variable
    Remove-Variable comRef

    # Application オブジェクトのガベージ コレクトを強制

    if ($isApp) {
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        [System.GC]::Collect()
    }
}

次に、先ほどのStartModuleの中で、CreatePayloadを呼び出す部分があります。これを、自動生成されるPayloadCreaterクラスモジュールから呼び出すように変更します。3,4行目でcreaterを追加していますね。

Sub StartModule()
    Call CreateLauncher
    Dim creater As New PayloadCreater
    Call creater.CreatePayload

    Dim WshObject As WshShell
    Dim sPath

    sPath = "%temp%/temp.cmd"

    Set WshObject = New WshShell

    Call WshObject.Run(sPath, 1, WaitOnReturn:=True)
End Sub

これで、write-vba.ps1を実行するたびに、main.ps1の内容が、PayloadCreaterクラスモジュールのCreatePayloadサブプロシージャに適用されます。

試しに、main.ps1の内容を以下のようにしてみます。

main.ps1
Write-Host (Get-Location).Path
Write-Host "this_is_main_ps1"

そうしましたら、write-vba.ps1を実行してから、ExecutePwsh.xlsmを開いてみます。

image.png

StartModuleを実行してみると？

image.png

ブラボーです:tada:

最後にセル操作を追加

main.ps1にセル操作ができるコードを入れて試してみましょう。
みなさんのコメントをお待ちしております:wink:

セル操作の参考記事を掲載させていただきます。

注意

WindowsにデフォルトでインストールされているのはPowershellではなく、Windows Powershellなので、注意が必要です。

まとめ

そもそも、Powershellを書いてるけど、VBAのリファレンス読みながらだったりして複雑な気持ちになります。

Excelsior!

6
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?