[[20190404093455]]　『VBAでWEBクエリを取得し、30秒ごとに情報を更新し』（TemTem）　ページの最後に飛ぶ

『VBAでWEBクエリを取得し、30秒ごとに情報を更新したい』（TemTem）

はじめて投稿いたします。

 -VBAでWEBクエリを使ってサイト情報を定期的に（30秒間隔で）取得したい
 -構文エラー、実行エラーなどが続き、実行できない
 -記述に関して、間違っている箇所の指摘をお願いします。

 ※下記を一般モジュール内に記述して実行しました。
 ※「web_capture」プロシージャでデータを取得し、
 「Refresh」プロシージャでその処理を定期的に繰り返すつもりで
 記述しています。
 ※「web_capture」プロシージャ単体では意図どおりに動作しますが、 「Refresh」プロシージャをどう組み合わせたらいいのかがわかりません。
 ※数箇所変更をしながら試したところ、エラーは出ないものの、
 情報の更新ではなく、同じ内容を横に複製し続ける結果が出ました。
 ※URLはダミーです。

●実行したい内容

 ・30秒ごとに指定のWEBサイトから情報を取得
 ・取得した情報（Tableタグで記述されたHTMLファイル）をwebという名前のエクセルシートに記述

Sub refresh()
'Refresh Web query
'Refresh every 30 seconds

    Sheets("web").QueryTables("ABC").Refresh

    Application.OnTime _
        Now() + TimeValue("00:00:30"), "refresh"

End Sub

Sub web_capture()

        With Sheets("web").QueryTables.Add(Connection:="URL;http://example.com", _
        Destination:=Range("$A$1"))
        .Name = "ABC"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .refresh BackgroundQuery:=False
    End With
End Sub

ご確認よろしくお願いいたします。

＜使用 Excel：Office365、使用 OS：Windows7 ＞

 OnTimeを使って強制的にRefreshしてますが、

 .RefreshPeriod = 0
 を
 .RefreshPeriod = 30
 に変更したら、自動更新されませんか？

 あと、
 .RefreshStyle = xlInsertDeleteCells
 になってますが、
 .RefreshStyle = xlOverwriteCells
 にしたらどうなりますか？
（でれすけ） 2019/04/04(木) 11:06

＞でれすけさん
返信ありがとうございます！

早速試したところ、

OnTimeを使って強制的にRefreshしてますが、 .RefreshPeriod = 30 に変更したら、自動更新されませんか？こちら、ご指摘のとおり再ロードされるようになりました！
（シート下部にネットに接続しているメッセージが定期的に出ます）

が、情報の更新はされていないようです。
もし他に変更した方がいい箇所があればご指摘お願いします。

（TemTem） 2019/04/04(木) 11:27

すみません、改めて確認したところ

 .RefreshPeriod　の単位は「分」のようでした。

 そこで

 .RefreshPeriod = 1にしたらVBA自体は希望どおり動き、上書きされました。
　 （が、0.5分=30秒と設定したらやっぱりエラーになりました。）

 もしこちらの指定方法（秒単位の指定）が分かりましたら教えてください。
（TemTem） 2019/04/04(木) 11:43

 ははぁ、更新時間の単位は、分ですね。失礼しました。
 0.5で指定してエラーになったのなら、どうしようも無いんじゃないでしょうか。

 最初の方針に返って、OnTimeで自前で更新するしかないでしょう。

 で、OnTime つかうとどんなエラーになるんですか？
 私のところで試しても問題ないですが。

 もし、標準モジュールではなく、シートのモジュールに書いてるなら、
 プロシジャ名の記述方法に工夫がいりますよ。

 Application.OnTime Now() + TimeValue("00:00:30"), "Sheet1!QRefresh"

 とシートのオブジェクト名を頭につける必要があります。
（でれすけ） 2019/04/04(木) 12:41

 返信ありがとうございます。
 プロシジャは標準モジュールに書いています。
 記述の順番やモジュール内の記載方法に誤りがあるのでしょうか。

 １）「Refresh」プロシージャを実行したとき
 ”Subscript out of range"のエラーが出ます。

 ２「web_capture」プロシージャを実行したとき
 "Application-defined or object-defined error"のエラーがでます

記述している内容（標準モジュール内に記述）

Sub refresh()
'Refresh Web query
'Refresh every 30 seconds

    Sheets("web").QueryTables("ABC").refresh
    Application.OnTime _
        Now() + TimeValue("00:00:30"), "refresh"
End Sub

Sub web_capture()

        With Sheets("web").QueryTables.Add(Connection:="http://example.com", _
        Destination:=Range("$A$1"))
        .Name = "ABC"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 1
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .refresh BackgroundQuery:=False
    End With
End Sub

（TemTem） 2019/04/04(木) 13:18

 1)のエラーは　web_capture　が失敗しているので、
   "ABC"という名前のQueryTableがないためだと思われます。

 2)のエラーは、
   With Sheets("web").QueryTables.Add(Connection:="http://example.com", _
  を 
   With Sheets("web").QueryTables.Add(Connection:="URL;http://example.com", _
  に変更してみてください。

 以下、一応確認
 ・30秒毎の更新はサーバー側に結構な負担がかかると思いますが、他者の迷惑になりませんね？
 ・Refreshプロシジャの連続実行を止める手段が用意されてませんが、大丈夫ですね？
（でれすけ） 2019/04/04(木) 13:45

 ＞でれすけ様
 返信が遅くなりましたが、ご指摘のとおりに修正したところ無事に動作しました！
 ありがとうございます！

 >・30秒毎の更新はサーバー側に結構な負担がかかると思いますが、他者の迷惑になりませんね？
 はい、実際の稼動は会社内の限られたアクセスのイントラサイトで、システム
 担当者と確認しながら行っていますので大丈夫です。

 >・Refreshプロシジャの連続実行を止める手段が用意されてませんが、大丈夫ですね？
 これは考えていませんでした。まずは自分で調べてみます。

 でれすけさん、丁寧にありがとうございました。
（TemTem） 2019/04/05(金) 07:11

コメント返信：

[ 一覧(最新更新順) ]

[[20190404093455]] 『VBAでWEBクエリを取得し、30秒ごとに情報を更新し』（TemTem） ページの最後に飛ぶ

[[20190404093455]]　『VBAでWEBクエリを取得し、30秒ごとに情報を更新し』（TemTem）　ページの最後に飛ぶ