外部データの更新がうまくできません(Excel VBA)

質問

いつもお世話になってます。
以下のプログラムで外部データの更新を入れたのですがうまく作動しません。

Dim sh As Worksheet
Dim lr As Long
Dim tlr As Long

For Each sh In Worksheets
If sh.Name Like "*D" Then
sh.Select
Selection.QueryTable.Refresh
End If
Next

For Each sh In Worksheets
If sh.Name Like "*D" Then
lr = sh.Cells(Rows.Count, 5).End(xlUp).Row
sh.Rows("1:" & lr).Copy
tlr = Sheets("統合データ").Cells(Rows.Count, 5).End(xlUp).Row
Sheets("統合データ").Range("A" & tlr + 1).PasteSpecial _
Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
End If
Next

プログラムの内容としては
(1)末尾が"D "のシートのデータを更新する(これらのシートは外部データを取込んでいます)
(2)"*D"シートのデータを全て"統合データ"シートに上から順に貼り付ける
ですが、上のプログラムだともとあるデータを貼り付けてからデータを更新しています。更新後のデータを貼り付けるにはどのようにすればよいでしょうか。
よろしくお願いします。

回答No.2

KenKen_SP · ベストアンサー

こんにちは。KenKen_SP です。

QueryTable はディフォルトでは非同期ですからね...更新前にどんどん先へ
コードを実行してしまいます。同期させたいなら、BackgroundQuery プロパティー
を False にしてください。

それから、Worksheets コレクションで同一のものを２度ループさせるさせる
のは冗長なので、一回で済ませましょう。

こんな感じでどうですか？

Sub SampleProc()

　　Dim Sh　　　　　As Worksheet
　　Dim QT　　　　　As QueryTable
　　Dim lSrcLastRow As Long
　　Dim lDstLastRow As Long

　　For Each Sh In ThisWorkbook.Worksheets
　　　　If Sh.Name Like "*D" Then
　　　　　　For Each QT In Sh.QueryTables
　　　　　　　　' 更新を待機するためには BackgroundQuery = True にします
　　　　　　　　QT.BackgroundQuery = False
　　　　　　　　QT.Refresh
　　　　　　Next
　　　　　　' データコピー
　　　　　　lSrcLastRow = Sh.Cells(Rows.Count, "E").End(xlUp).Row
　　　　　　Sh.Rows("1:" & CStr(lSrcLastRow)).Copy
　　　　　　' データペースト
　　　　　　With ThisWorkbook.Sheets("統合データ")
　　　　　　　　lDstLastRow = .Cells(Rows.Count, "E").End(xlUp).Row
　　　　　　　　.Rows(lDstLastRow + 1).PasteSpecial _
　　　　　　　　Paste:=xlPasteValuesAndNumberFormats
　　　　　　End With
　　　　End If
　　Next
　　Application.CutCopyMode = False

End Sub

お礼

KenKen_SPさん、いつもありがとうございます。backgroundqueryでうまくいきました。あとシンプルなコードも参考になりました。ありがとうございます。

回答No.1

Wendy02

こんばんは。

細かい部分はみていませんが、

>sh.Select
>Selection.QueryTable.Refresh

ここに無理がありますね。

Selection は、シートのことですから、そのままでは、実行できません。
そこに、正しいRange オブジェクトが入れば動きますが、そう上手くは行きませんから、

その二行は、

sh.QueryTables(1).Refresh
(QueryTable が、そのシートの中で一つの場合)

となるはずです。

こちらは、試していないので分からないのですが、Refreshした時のインポートのタイムラグ(数秒)があるので、そのままマクロが次に実行していかないか、ちょっと不安です。Wait を入れたほうが無難かもしれません。それだけは、やってみないと分からないのです。

お礼

Wendy02さんありがとうございます。試してみたところうまくいきました。querftable.refreshの使い方参考になりました。ありがとうございます。