Blue Prism側のコレクションデータをExcelにドロップダウンリストとして貼り付ける方法を解説します。ポイントはRun Macroへ引数を渡す方法とExcel側のマクロです。
今回は、Yahoo!路線情報にて、検索結果の「出発地/到着地候補」のリストをExcelに貼り付けることにします。
![image.png](https://qiita-user-contents.imgix.net/https%3A%2F%2Fqiita-image-store.s3.ap-northeast-1.amazonaws.com%2F0%2F186221%2Fdf0035b6-3f71-6c62-aabd-d1f6ddde1250.png?ixlib=rb-1.2.2&auto=format&gif-q=60&q=75&s=eddebb9e7743bb104b37d278f901f3dd)
方法
「出発地/到着地候補」のリストを取得する
「読み取り」ステージを使って、「出発地/到着地候補」のリストを取得します。
リストはコレクション変数に格納されます。
![image.png](https://qiita-user-contents.imgix.net/https%3A%2F%2Fqiita-image-store.s3.ap-northeast-1.amazonaws.com%2F0%2F186221%2Ff4b80f7e-7fe5-3743-0052-6e68f48e4415.png?ixlib=rb-1.2.2&auto=format&gif-q=60&q=75&s=4c6e1421125848484d394c6bae60dd54)
コレクションをカンマ区切りの文字列に変換する
コレクションをループで回し、テキスト型の変数にカンマ区切りで連結していきます。
例えば、「赤坂(東京都)駅,赤坂(福岡県)駅,赤坂(山梨県)駅,…」といった具合です。
![image.png](https://qiita-user-contents.imgix.net/https%3A%2F%2Fqiita-image-store.s3.ap-northeast-1.amazonaws.com%2F0%2F186221%2Febc83afc-c6cf-f885-5509-10684a7f6343.png?ixlib=rb-1.2.2&auto=format&gif-q=60&q=75&s=f6fe8296f350cb932d24dd179dce0ff8)
![image.png](https://qiita-user-contents.imgix.net/https%3A%2F%2Fqiita-image-store.s3.ap-northeast-1.amazonaws.com%2F0%2F186221%2F271138e4-5250-2962-7d5b-d14e03b49a89.png?ixlib=rb-1.2.2&auto=format&gif-q=60&q=75&s=4b8e16960960840d2562f7e38fb4c9f6)
末尾のカンマは除外しておきます。
![image.png](https://qiita-user-contents.imgix.net/https%3A%2F%2Fqiita-image-store.s3.ap-northeast-1.amazonaws.com%2F0%2F186221%2Fe8554a6d-2ed0-c630-cc80-6cbd5043d160.png?ixlib=rb-1.2.2&auto=format&gif-q=60&q=75&s=4cca9371dea762cf113ca97f21f4a2e2)
MS Excel VBOを拡張する
既存のオブジェクトである「MS Excel VBO」の「Run Macro」では、先ほどのプロシージャに引数を渡せないので、「Run Macro」を拡張することにします。
拡張する際は、ベースの「MS Excel VBO」をコピーして別オブジェクト「MS Excel VBO – extend」を用意します。
その後、「Run Macro」をコピーし、「マクロ実行(引数あり)」ページを作成します。
ページ名を日本語にしているのは、拡張したページであることを分かりやすくするためです。
![image.png](https://qiita-user-contents.imgix.net/https%3A%2F%2Fqiita-image-store.s3.ap-northeast-1.amazonaws.com%2F0%2F186221%2F9b428785-db0f-07c7-0080-558be5040bbe.png?ixlib=rb-1.2.2&auto=format&gif-q=60&q=75&s=b83941196d309f6ce12f75c2995997bf)
ブサイクなコードですが、今回はこれで良しとします(笑)
プロシージャに引数を最大3つまで渡せるようにします。
If arg1.Length <> 0 Then
If arg2.Length <> 0 Then
If arg3.Length <> 0 Then
GetInstance(Handle).Run(Macro_Name, arg1, arg2, arg3)
Else
GetInstance(Handle).Run(Macro_Name, arg1, arg2)
End If
Else
GetInstance(Handle).Run(Macro_Name, arg1)
End If
Else
GetInstance(Handle).Run(Macro_Name)
End If
入力パラメータを追加することを忘れずに。
![](https://qiita-user-contents.imgix.net/https%3A%2F%2Fqiita-image-store.s3.ap-northeast-1.amazonaws.com%2F0%2F186221%2Fb98ad06e-4ab0-0b5a-7ec5-9c0bcda17694.png?ixlib=rb-1.2.2&auto=format&gif-q=60&q=75&s=30961a1cbc83f7bbeac1a217aafa9637)
Excelマクロを作る
ドロップダウンを作る&消すモジュールを作っておきます。
![image.png](https://qiita-user-contents.imgix.net/https%3A%2F%2Fqiita-image-store.s3.ap-northeast-1.amazonaws.com%2F0%2F186221%2F1985f58a-f777-04cd-83ad-c276719ff732.png?ixlib=rb-1.2.2&auto=format&gif-q=60&q=75&s=0c17203c5d25aeb3f0bf271eb62d6c24)
Option Explicit
Sub CreateDropDown(ByVal rng, ByVal list)
Dim sheet As Worksheet
Set sheet = Worksheets(1)
With sheet.Range(rng).Validation
.Delete
.Add _
Type:=xlValidateList, _
Formula1:=list
End With
End Sub
Sub DeleteDropDown(ByVal rng)
Dim sheet As Worksheet
Set sheet = Worksheets(1)
With sheet.Range(rng).Validation
.Delete
End With
End Sub
マクロをキックするステージを用意する
アクションステージにて、「MS Excel VBO – extend」オブジェクトから「マクロ実行(引数あり)」を呼び出します。
入力パラメータは図のように設定します。
Macro Name:”CreateDropDown”
引数1:ドロップダウンを作るセル位置
引数2:経路候補リスト
![image.png](https://qiita-user-contents.imgix.net/https%3A%2F%2Fqiita-image-store.s3.ap-northeast-1.amazonaws.com%2F0%2F186221%2Fbf89ee88-da33-592d-983d-f9ca5962cb67.png?ixlib=rb-1.2.2&auto=format&gif-q=60&q=75&s=c5d8a850a43315424046d661e02d9083)
実行結果
指定したセル位置に、出発地/到着地候補リストのドロップボックスができます。
![image.png](https://qiita-user-contents.imgix.net/https%3A%2F%2Fqiita-image-store.s3.ap-northeast-1.amazonaws.com%2F0%2F186221%2F650f46c1-7087-79f5-0872-ac7dd3ad8f4d.png?ixlib=rb-1.2.2&auto=format&gif-q=60&q=75&s=40b3fac998678dfcf33c55254661cde8)