前回は、クエリ名を使わずに順番にクエリを更新するマクロの作り方を書きました。
そして最後に、弱点は更新したいマクロを選べない、とも。
というわけで今回は実行すると逐一更新するかどうかを選ばせるマクロの作り方をお届けしたいと思います。
Contents
この記事でやりたいこと
まずはやりたいことを確認しておきましょう。
このようにクエリ更新２に今回のマクロを割り当てましたので、これを押してみます。
するとこのように、クエリごとに確認ボックスが出てきて、更新するか（更新ボタン）しないか（Skipボタン）を選ぶ仕組みにしたいと思います。
今回作るマクロの概略と手順
まず、処理の流れを確認したいと思います。
こんな感じの流れです。
ポイントはこの３点です。
①確認ボックス（ユーザーフォーム）を繰り返し開く
②ユーザーフォームにクエリ名を表示させる
③ユーザーフォームの更新ボタンとSkipボタンで分岐点を作る。
そして、今回①②と③の２種類のマクロを作らないといけません。
それをフローチャート上で区分けするとこのような区分けになります。
ユーザーフォームを呼び出すマクロとユーザーフォームのマクロに分かれています。
では、ユーザーフォームのマクロから、順番に見ていきましょう。
ユーザーフォームマクロの作成
開発タブのVisualBasicを開き、今回マクロを登録するファイルを選択し、右クリックしてユーザーフォームを選択します。
確認ボックスの作成
このように、ボックスが出てきますので、これをツールボックスからパーツを選択して配置し、形を作っていきます。
今回作るボックスが上の図です。
そして、使うパーツはここに記載した３点です。
で、それぞれ配置して記載テキストを編集します。
プロパティの確認と編集
次に、プロパティの確認と編集です。
ここでは、フォーム自体のキャプションの編集やパーツの名称などを設定します。
プロパティの開き方は簡単で、プロパティを確認したいパーツを選択して右クリック、プロパティを選択すればOKです。
では、それぞれのプロパティを見ていきます。
まずはユーザーフォーム。
ユーザーフォームの名称の確認とキャプションを変更してみましょう。
次にテキストボックス。これは名称を確認編集すればOKです。
今回はクエリ名ということで[qName]に変更してみました。
次にボタン。更新ボタンとSkipボタンそれぞれの名前を確認修正します。
まずは更新ボタンです。これを[Botan1]としてみました。
次にSkipボタン。こちらは[Botan2]です。
これでプロパティの確認修正は終了です。その他文字などもろもろの設定はこのプロパティで変えていきますが、ここでは省略します。
ボタンを押したときの動作マクロの作成
最後に、更新ボタン・Skipボタンを押したときの動作を登録しましょう。
まず、プロジェクト一覧で先ほど作成したユーザーフォームを右クリック、「コードを表示」を選択します。
そして、ボタン１が押された時の処理・ボタン２が押された時の処理を記述します。
Private Sub Botan1_click() 'ボタン1（更新）をクリックしたとき
'テキスト型変数qN
Dim qN As String
'変数qNを、ユーザーフォームのqNameボックスに入力された値とする
qN = UserForm1.qName.Value
'開いているシートの接続（クエリ）qNを更新する
ActiveWorkbook.Connections(qN).Refresh
'ユーザーフォームを閉じる
Unload UserForm1
End Sub
Private Sub Botan2_Click() 'ボタン2（skip）をクリックしたとき
'ユーザーフォームを閉じる
Unload UserForm1
End Sub
これで、更新ボタンが押された時にクエリの更新が、Skipボタンが押された時はこのまま未処理で次のiに行く、といったことが実行されます。
ユーザーフォームを呼び出すマクロ
ここまででユーザーフォームと、ボタンが押された時の処理ができました野で、次のこの作ったユーザーフォームを開くためのマクロを作りましょう。
基本的には、ユーザーフォームを開くという作業をクエリ数だけ繰り返すマクロを作ります。
Sub クエリ更新選択制()
'変数宣言 i （数値型）
Dim i As Long
'開いてるブックにおいて
With ActiveWorkbook
'iが1から（クエリ数）まで繰り返す
For i = 1 To .Connections.Count
'
'ユーザーフォームを呼び出す
Load UserForm1
'ユーザーフォームのテキストボックスにクエリ（i)を投入
UserForm1.qName.Text = .Connections(i).Name
'ユーザーフォーム１を開く
UserForm1.Show
Next
End With
End Sub
以上でマクロの記述も完了しました。
これでマクロを実行すると、ユーザーフォームが開き、更新ボタンを押すと対応するクエリが更新され、次のクエリのユーザーフォームが開き、また更新するか否かを選択します。
ちなみに、今回も「バックグラウンドで更新する」のチェックを外しておいてください。
最後に
いかがだったでしょうか。
今回はユーザーフォームを使って、クエリ更新を簡単にするためのマクロを作ってみました。
ユーザーフォームを使った処理をはさむと少しややこしくなりますが、基本はフローチャートに書いた処理を実行するだけですので、一つ一つ紐解いていけばそこまで難しいことはないと思います。
今までマクロをやったことがないという人も、一度やってみてはいかがでしょうか。
今回も最後までお付き合いくださり、ありがとうございました。
