LoginSignup
2
2

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 5 years have passed since last update.

@Q11Q

Powershell Excel 以外のVBAの定数をCSVにエクスポートする

Last updated at Posted at 2017-10-15

Excel以外のオフィス

https://qiita.com/Q11Q/items/1f6382f14f7a2530e591
Excelの定数の一覧表ができましたので
Word Outlook | PowerPoint Access Publisher Onenote
の変数一覧表を作ります。

注意

出力先の設定をお願いします

出力先は変更してください。現在はc:\hogeにしています。

既存のファイルは削ります

Application FileName
Outlook testol.csv
Word testwd.csv
PowrPoint testpp.csv
Publisher testpb.csv
Access testac.csv
OneNote testOne.csv

指定した出力先の上記の名称のファイルは作成の成否にかかわらず削除される可能性があるので、必要なものは別名で保存してください。

インストールされていないものは出ません。

プロフェッショナル、３６５以外のホーム＆ビジネスはAccess　Publisherがないのでできません。

OneNoteは怪しい

　あとこれは未実証なのですがOnenoteは2013だけかそれ以前は無理のようです。
OneNote2013はVBSやPowerShellからCreateObjectできない[魚拓]http://megalodon.jp/2017-1015-1722-49/m0t0k1x2.tumblr.com/post/134380620644/onenote2013%E3%81%AFvbs%E3%82%84powershell%E3%81%8B%E3%82%89createobject%E3%81%A7%E3%81%8D%E3%81%AA%E3%81%84)
最もOneNoteの列挙体は80くらいしかなく、1000以上あるほかのアプリに比べればないも同然ですが。
2018/08/28追記
Read and Write Content from OneNote with PowerShell
いちおうPowershellでなら New-Objectできる。
そして
テーブルの取得
第一レベルのセクションのリスト
write content from HTML-File to a new Notebook
(keeping heading levels and defining custom styles for headers, images not handled in this script)
ができます。

Onenoteは公式を見た方が確実

なので、Onenoteの場合は列挙体 (OneNote の 2013年開発者用リファレンス)をみた方が早いと思います。

MakeVBAENumWOAPP.ps1
$vbcrlf = "`r`n"
# Functions
Function Split-Strings{Param([string]$str)
if(
($str.Length -gt 0)  -and ($str.Contains("=") -eq $true) 
){$rst = $str.Substring(0,$str.IndexOf("="))
Return $rst;
}Else{
$rst = $str;
Return $rst;
}
} # End Of Function Split-Strings

# For OutLook
Try{
$olApp=New-Object -ComObject outlook.application;$olEnums = New-Object -TypeName PSObject;$olApp.GetType().Assembly.GetExportedTypes() | Where-Object {$_.IsEnum} | ForEach-Object {$enums = $_ ; $enums.GetEnumNames() | ForEach-Object {$olEnums | Add-Member -MemberType NoteProperty -Name $_ -Value $enums::($_)}}
$olEnums | gm -MemberType Properties | Export-Csv "c:\hoge\testol.csv" -Force  -NoTypeInformation
<#
$ar = ($olEnums -split ";") -replace("}","")
$ar | ForEach-Object{
Write-Host [enum]::TryParse(Split-Strings($_));
write-host [enum]::GetValues(Split-Strings($_)).GetType();
}
# >
}Finally{
# clear-variable ar
clear-variable olEnums
$olApp.quit()
$olapp,$ar,$olEnums | foreach{$_ = $null}
}


# For Word
Try{
$wdApp=New-Object -ComObject word.application;$wdEnums = New-Object -TypeName PSObject;$wdApp.GetType().Assembly.GetExportedTypes() | Where-Object {$_.IsEnum} | ForEach-Object {$enums = $_ ; $enums.GetEnumNames() | ForEach-Object {$wdEnums | Add-Member -MemberType NoteProperty -Name $_ -Value $enums::($_)}}
$wdEnums | gm -MemberType Properties | Export-Csv "c:\hoge\testwd.csv" -Force  -NoTypeInformation
<#
$ar = ($wdEnums -split ";") -replace("}","")
$ar | ForEach-Object{
Write-Host [enum]::TryParse(Split-Strings($_));
write-host [enum]::GetValues(Split-Strings($_)).GetType();
}
# >
}
Finally{
# clear-variable ar
clear-variable wdEnums
$wdApp.quit()
$wdapp,$ar,$wdEnums | foreach{$_ = $null}
}

### For Proffesional or Office 365 

# For Microsoft Access
Try{
$acApp=New-Object -ComObject access.application;$acEnums = New-Object -TypeName PSObject;$acApp.GetType().Assembly.GetExportedTypes() | Where-Object {$_.IsEnum} | ForEach-Object {$enums = $_ ; $enums.GetEnumNames() | ForEach-Object {$acEnums | Add-Member -MemberType NoteProperty -Name $_ -Value $enums::($_)}}
$acEnums | gm -MemberType Properties | Export-Csv "c:\hoge\testac.csv" -Force  -NoTypeInformation
<#
$ar = ($acEnums -split ";") -replace("}","")
$ar | ForEach-Object{
Write-Host [enum]::TryParse(Split-Strings($_));
write-host [enum]::GetValues(Split-Strings($_)).GetType();
}
# >
}
Finally{
# clear-variable ar
clear-variable acEnums
$acApp.quit()
$acApp,$ar,$acEnums | foreach{$_ = $null}
}

# For Microsoft PowerPoint
Try{
$pbApp=New-Object -ComObject Publisher.application;$pbEnums = New-Object -TypeName PSObject;$pbApp.GetType().Assembly.GetExportedTypes() | Where-Object {$_.IsEnum} | ForEach-Object {$enums = $_ ; $enums.GetEnumNames() | ForEach-Object {$pbEnums | Add-Member -MemberType NoteProperty -Name $_ -Value $enums::($_)}}
$pbEnums | gm -MemberType Properties | Export-Csv "c:\hoge\testpb.csv" -Force  -NoTypeInformation
<#
$ar = ($pbEnums -split ";") -replace("}","")
$ar | ForEach-Object{
Write-Host [enum]::TryParse(Split-Strings($_));
write-host [enum]::GetValues(Split-Strings($_)).GetType();
}
# >
}
Finally{
# clear-variable ar
clear-variable pbEnums
$pbApp.quit()
$pbApp,$ar,$pbEnums | foreach{$_ = $null}
}

Try{
$ppApp=New-Object -ComObject Powerpoint.application;$ppEnums = New-Object -TypeName PSObject;$ppApp.GetType().Assembly.GetExportedTypes() | Where-Object {$_.IsEnum} | ForEach-Object {$enums = $_ ; $enums.GetEnumNames() | ForEach-Object {$ppEnums | Add-Member -MemberType NoteProperty -Name $_ -Value $enums::($_)}}
$ppEnums | gm -MemberType Properties | Export-Csv "c:\hoge\testpp.csv" -Force  -NoTypeInformation
<#
$ar = ($ppEnums -split ";") -replace("}","")
$ar | ForEach-Object{
Write-Host [enum]::TryParse(Split-Strings($_));
write-host [enum]::GetValues(Split-Strings($_)).GetType();
}
# >
}
Finally{
# clear-variable ar
clear-variable ppEnums
$ppApp.quit()
$ppApp,$ar,$ppEnums | foreach{$_ = $null}
}

# OneNote ( Doesn't Work 2013 or 2013 earlier)
Try{
$onApp=New-Object -ComObject Onenote.application;$onEnums = New-Object -TypeName PSObject;$onApp.GetType().Assembly.GetExportedTypes() | Where-Object {$_.IsEnum} | ForEach-Object {$enums = $_ ; $enums.GetEnumNames() | ForEach-Object {$onEnums | Add-Member -MemberType NoteProperty -Name $_ -Value $enums::($_)}}
$onEnums | gm -MemberType Properties | Export-Csv "c:\hoge\testOne.csv" -Force  -NoTypeInformation
<#
$ar = ($onEnums -split ";") -replace("}","")
$ar | ForEach-Object{
Write-Host [enum]::TryParse(Split-Strings($_));
write-host [enum]::GetValues(Split-Strings($_)).GetType();
}
# >
}
Finally{
# clear-variable ar
clear-variable ppEnums
# $onApp.quit()
$onApp,$ar,$onEnums | foreach{$_ = $null}
}

今後の課題

Excelの最初のバージョンのときからそうですが
1:"TypeName","Name","MemberType","Definition"
2:"System.Management.Automation.PSCustomObject","msoAnimAccumulateAlways","NoteProperty","MsoAnimAccumulate msoAnimAccumulateAlways=msoAnimAccumulateAlways"

"msoAnimAccumulateAlways"のようにB列に確かに列挙体の文字列が抜き出されます。
しかしながら、この値は出ません。
$arに文字列としていれて、ユーザー定義関数で列挙体の文字列だけ抜き出せますが、それでも値に変換しないのです。#\$arを注釈にしているのは余計な動作で遅くならないようにするためですが、将来何らかの手法で数値化したときのために残してあります。

2
2
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
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?