概要
いよいよOffice ScriptsとしてOfficeでTypeScript(Javascript)が使えるようになったのですが、残念ながらWeb版限定でした…。
Web版ExcelでTypeScriptベースの「Office Scripts」提供開始。ユーザー操作を自動で記録可能
というわけで、アプリ版ExcelでもTypeScriptが使えるように試行錯誤してみました。
単純にTypeScript(Javascript)側からExcelを動かすのはActiveXさえ扱えれば簡単で、ActiveXからのExcelの操作はググれば使えそうなサンプルがゴロゴロ出てきてそれ参考にすれば十分な状態なので、
逆に最小限の労力でExcel(VBA)側からTypeScriptのコードを動かせるようにもしてみたい。
新しい仕様のJSを使えるようにする
今回の構想においてJavascript実行環境はActiveXが簡単に使えてユーザー側で追加のインストールが不要なWindowsScriptHost(WSH)を使うのですが、
何もしなければ相当に古いJavascript仕様相当であるJScript環境で動いてしまうのでさすがに現実的ではない。
ただ、WSHはオプションとしてスクリプトエンジンをProgIDやCLSIDで指定することができるようになっており、
Windows10ならEdge(Chromiumじゃないほう)のES6(2015)相当のJavascriptも使えるようになっています。
WSH JScript Chakra を使用した ES2015(ES6) 対応 （ スクリプトエンジン まとめ ）
いろいろ試してみましたが、今のところsubプロシージャや関数が呼ばれた都度wscript.exeを実行してJavascriptコードを動かすのが確実そうです。
他にVBAからJavascriptを使う方法としてScriptControlを使う方法やHTMLDocumentを使う方法なんかがありますが、いずれもEdge相当のJavascriptは使えないようです。
WSH側からExcel側(起動済み)のオブジェクトを取得
WSH(wscript.exe)を実行する方式にする場合、そのままではExcel関係のActiveXオブジェクトをWSH側に渡す方法が無いのでどうにかする必要があるのですが、
GetObject(,"Excel.Application") では既に別のExcelが動いていたとしても新しいExcelウインドウが開いてしまってうまくいきません。
しかし、GetObjectにはActiveXのクラス名の他にファイルのフルパスも指定できるのですが、既に開いているエクセルファイルの場合は新しく開かずに既に開いているファイルのWorkbookオブジェクトが取得できる仕組みになっているようです。
参考: GetObject(, "Excel~")だけで無く、GetObject(ファイル名)も活用しよう
つまり、呼び出すExcel側のExcelファイルのフルパスさえわかればGetObjectで新しく(二重に)Excelを起動することなくExcel(VBA)側のWorkbookオブジェクトが取れるということです。
VBAから自身のExcelファイルのフルパスは ThisWorkbook.FullName で簡単に取得できるので、
これをコマンドラインオプションとして渡せば実質Excel(VBA)側のWorkbookオブジェクトをWSH側に渡すことができるようになります。
ちなみに (Workbookオブジェクト).Application で親となるApplicationオブジェクトが取得できます。
対象のExcelファイルが開いていない場合は新しく開いたうえでそのWorkbookオブジェクトを取得できるので、工夫すれば同じコードでExcelを開いていない状態からWSH側からExcelを開いてスクリプトを動かすということも可能になります。
データ保存領域としてカスタムドキュメントプロパティを使う
WSHを都度起動する方式にした以上は終了するとWSH側の変数はすべて消えてしまうので、プロシージャや関数をまたがってデータをやり取りしたい場合はどこかにデータを保存しておく必要があります。
こういう時は専用のシートでも作ってセルに書き込んでおくのが王道だとは思いますが、もう一つの選択肢として、カスタムドキュメントプロパティも使えるようです。
以下のようにVBA側でヘルパー関数を定義しておけばWSH側から (Applicationオブジェクト).Run(Excelファイル名 + "!関数名", 引数...) で呼び出すことができて便利です。
Sub SetCDP(Pname As String, PValue As String)
If Not IsCDPExist(Pname) Then
ThisWorkbook.CustomDocumentProperties.Add Pname, False, msoPropertyTypeString, PValue
Else
ThisWorkbook.CustomDocumentProperties(Pname).Value = PValue
End If
End Sub
Function GetCDP(Pname As String) As String
GetCDP = ""
If IsCDPExist(Pname) Then
GetCDP = ThisWorkbook.CustomDocumentProperties(Pname).Value
End If
End Function
Sub DelCDP(Pname As String)
If IsCDPExist(Pname) Then
ThisWorkbook.CustomDocumentProperties(Pname).Delete
End If
End Sub
Function IsCDPExist(Name As String) As Boolean
Dim prop As Object
IsCDPExist = False
For Each prop In ThisWorkbook.CustomDocumentProperties
If prop.Name = Name Then
IsCDPExist = True
Exit For
End If
Next prop
End Function
WSHからExcelのユーザーフォームを開く
WSHというかExcel関係のActiveXオブジェクトからユーザーフォームを開くのは無理なようなので、VBA側でユーザーフォーム名からフォームオブジェクトを取得するヘルパー関数を作ってみました。WSH側から (Applicationオブジェクト).Run(Excelファイル名 + "!GetFormObject", "フォーム名") でフォームオブジェクトを取得したら (フォームオブジェクト).Show(1) で開いたりできます。存在しないフォーム名を指定するとVBA側ではNothingを返していますが、WSH(JavaScript)側ではnullとなるようです。
また、このままではWSH側からUnload出来ない(hideで隠すことはできる)ので、同様にヘルパー関数が必要のようです。
参考: Show Any Form
Function GetFormObject(FormName As String) As Object
Dim Obj As Object
For Each Obj In VBA.UserForms
If Obj.Name = FormName Then
Set GetFormObject = Obj
Exit Function
End If
Next Obj
On Error Resume Next
Err.Clear
Set Obj = VBA.UserForms.Add(FormName)
If Err.Number <> 0 Then
Set GetFormObject = Nothing
Exit Function
End If
Set GetFormObject = Obj
End Function
Sub UnloadForm(formname As String)
Dim obj As Object
Set obj = GetFormObject(formname)
If Not obj Is Nothing Then
Unload obj
End If
End Sub
VBAからWSH(wscript.exe)を起動
VBA内蔵のRunは実行したら即制御が戻る非同期実行となり非常に都合が悪いのでWScript.ShellのRunで同期実行するようにしています。
Function CallJScript(funcname As String, Optional formname As String = "", Optional script As String = "", Optional book As String = "") As Boolean
Static fso As Object
Static wsh As Object
If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject")
If wsh Is Nothing Then Set wsh = CreateObject("WScript.Shell")
If book = "" Then
'bookパラメータ(対象のExcelファイル)を指定しなかった場合は自身のExcelファイルを設定
book = ThisWorkbook.FullName
ElseIf Left(book, 1) = "." Then
'1文字目が.なら相対パスと判断して絶対パスに変換
book = fso.GetAbsolutePathName(fso.BuildPath(ThisWorkbook.Path, book))
End If
If script = "" Then
'scriptパラメータを指定しなかった場合はbookパラメータと同じフォルダにある同じファイル名のjsファイルを設定
script = fso.GetParentFolderName(book) & "\" & fso.GetBaseName(book) & ".js"
ElseIf Left(script, 1) = "." Then
script = fso.GetAbsolutePathName(fso.BuildPath(ThisWorkbook.Path, script))
End If
If fso.FileExists(book) = False Then
Err.Raise Number:=53, Description:="ファイル" & book & "が見つかりません"
Exit Function
End If
If fso.FileExists(script) = False Then
Err.Raise Number:=53, Description:="ファイル" & script & "が見つかりません"
Exit Function
End If
'edgeのJScriptエンジン(Chakra)を指定してスクリプトを実行
wsh.Run "wscript //E:{1B7CD997-E5FF-4932-A7A6-2A9E636DA385} " & script & " " & book & " " & funcname & " " & formname, 0, True
'イベントキャンセル用のカスタムプロパティを取得して戻り値に設定しカスタムプロパティを削除
CallJScript = False
If LCase(GetCDP("_JScriptIsCancel")) = "true" Then
CallJScript = True
End If
If IsCDPExist("_JScriptIsCancel") Then
DelCDP "_JScriptIsCancel"
End If
End Function
WSHでスクリプトを書く
今のところこんな感じ。
var sh = WScript.CreateObject("WScript.Shell")
var xlBook = WScript.GetObject(WScript.Arguments(0))
var xlApp = xlBook.Application
sh.popup(xlBook.Sheets("Sheet1").Range("A3").Value)
xlBook.Sheets("Sheet1").Range("A1").Value = "test"
var formtest = xlApp.Run(xlBook.Name + "!GetFormObject", "UserForm1")
if(formtest == null){
sh.popup("Nothing!")
}else{
formtest.Show(1)
}
xlApp.Run(xlBook.Name + "!SetCDP", "test", "Test Prop2")
sh.popup(xlApp.Run(xlBook.Name + "!IsCDPExist","test"))
sh.popup(xlApp.Run(xlBook.Name + "!GetCDP", "test"))
都度wscript.exeを起動しなくてもいいのでは…
をみてよくよく考えたら都度都度wscript.exeを起動しなくてもお互いにカスタムドキュメントプロパティの内容を監視して協調動作すればいいのではと考え中…(リンクのほうはInternetExplorerオブジェクトでやっている模様)