Excel活用

【パワークエリ×マクロ】クエリ名を指定せずに更新実行するマクロの作り方

 

前回はクエリを順番に更新するマクロの作り方をお届けしました。

【パワークエリ×マクロ】作成したクエリをボタンひとつで順番に更新する方法クエリを一括更新せず、順番に実行するマクロの作り方を書きました。クエリからExcel、Excelからクエリというような、間にテーブルをはさむクエリを簡単に更新する方法について書いてます。...

今回はその応用編です。

実は前回作ったマクロには、クエリ名を変えると使えなくなるという弱点がありました。

で、今回はその弱点を克服したという話です。

前回作ったマクロのおさらい

まずは前回作ったマクロを確認しておきましょう。

このように、実行するクエリをクエリ名で指定していることがわかると思います。

これが弱点になるわけです。

なので、クエリ名以外でクエリを指定して実行するように書くことで、この問題をクリアしていきます。

マクロの記載例

今回参考にする方法

これには参考にする方法があります。

例えば、シート名を取得するマクロ。

まずはこちらをご覧ください。

Sub シート名取得()


'変数宣言 i (数値)
Dim i As Long

'開いてるブックにおいて、
With ActiveWorkbook
    
    ' i を１からシート数までを指定して繰り返す
    For i = 1 To .Sheets.Count
    
    'シート(インデックス i )の名前をセル(i,1)に代入
     Cells(i, 1) = .Sheets( i ).Name
    
    '次の i へ
    Next

'「開いてるブックにおいて」終了
End With

'マクロ終了
End Sub

いかがですか？

シート名を取得するのにシート名で指定してないですよね。

ここではインデックスを使って、「シートナンバー１のシート名」といった指定の仕方になっています。

ちなみに、このマクロを実行したときのExcelブックと表示の関連はこちら。

シートの表示順に、シート名がセルに入力されていますね。

インデックスで指定するというのは、そういうことです。

クエリについても同じように記載することができます。

クエリをインデックスで指定して更新する場合

まず、クエリの更新をどのように書いたのかおさらいです。

ActiveWorkbook.Connections("クエリ - テーブル1").Refresh

先のシート名を取得するマクロと同じように、変数 i でクエリを指定するのであればこのように書き換えればOKですね。

ActiveWorkbook.Connections( i ).Refresh

これだけでOKです。

ではマクロ全体を確認しておきましょう。

Sub クエリ更新応用編()


'変数宣言 i (数値)
Dim i As Long

'開いてるブックにおいて、
With ActiveWorkbook
    
    ' i を１からクエリ数までを指定して繰り返す
    For i = 1 To .Connections.Count
    
    'クエリ（インデックスi）を更新
     .Connections( i ).Refresh
    
    '次の i へ
    Next

'「開いてるブックにおいて」終了
End With

'マクロ終了
End Sub

先のシート名を取得するマクロとほとんど同じですよね。

そう、同じなのです。

あとは実行したい処理を書けるかどうかだけだということです。

ちなみに、これですと表示されたクエリを上から順番に実行するので、実行したい順番に並べ替えておくことをお忘れなく。

最後に

いかがだったでしょうか。

順番にクエリを実行するマクロの応用編としてお届けしました。

ただ、このままだとすべてのクエリを順番に実行することになりますので、

例えばクエリごとにメッセージボックスを表示させて更新するかスキップするかを選べるようにする、そんなマクロを次回は作ってみたいと思います。

最後までお読みいただきありがとうございました。

Powerquery VBA マクロ
RELATED POST