あるあるエラー：Webクエリ

WebクエリをVBAで作成するときのことです。

名前をつけたクエリを試行しながら
削除したり作成したりしたあとで、
名前でクエリを指定して更新させようとしたときに
次のようなエラーが出たことがありました。

なぜこのエラーになったのでしょうか?
原因はクエリを作成しているときに発生しています。

既存のクエリと同じ名前のクエリを
VBAで作成しても、
作成時にはエラーになりません。

なぜなら指定した名前に「_1」が自動で付加され
違う名前になるからです。
もう一回同じ名前のクエリを作成すると、
「_2」が自動で付加されます。

実はこのような現象は、
同一シート内で
同じ名前の作成済みのクエリを削除してから
同じ名前のクエリを作成しても起きます。

この状態、つまり
「名前」という名前の付いたWebクエリが削除されていて
「名前_1」という名前の付いたWebクエリがある状態で、
「_1」などが付加される前の「名前」で指定しようとすると
上のエラーが発生します。

ですからこのエラーが起きたときは
まずはクエリのプロパティで
手動で名前を確かめてください。
名前が変更されている可能性があります。

変更されていたら、
名前を手動で意図するものに変更してください。

こういった作業が嫌なら
同じ名前のWebクエリを何回も作成したりするときは
その度に新しいシートを挿入して
Webクエリを作成したほうが
確実です。

ActiveSheetのすべてのShapeを選択

ActiveSheetのすべてのShapeを指定することは
以外に難しいです。

前記事を作っている途中のことですが、
作成したたくさんのShapeを削除したいとき
1つ1つ手で選択して削除するのも面倒なので、
シートごと削除してました。

それで ActiveSheetのすべてのShapeを指定すれば
全削除できると考えたのですが、
Shapes.Deleteみたいに簡単にはできませんでした。

Shapesの名前の入れたVariant型の配列を作成して、
これをすべてのShapeを指定するのに使います。

コードはこちら

Sub macro110625a()
'ActiveSheetのシェイプをすべて削除

    Dim i As Integer
    Dim strName() As Variant
    ReDim strName(1 To ActiveSheet.Shapes.Count)
   
    For i = 1 To ActiveSheet.Shapes.Count
        strName(i) = ActiveSheet.Shapes(i).Name
    Next i
   
    'ActiveSheet.Shapes.Range(strName).Select
    ActiveSheet.Shapes.Range(strName).Delete
   
End Sub

全部を選択したいときは
コメントにしてあるSelectのほうを使ってください。

PresetTextEffectのビジュアル一覧

ワードアート オブジェクトを作成します。
テキストが押し出されて立体的になっていたりするヤツのことです。

どのようなものがあるか、
PresetTextEffectのビジュアル一覧を作成します。
ワードアートを作成するには、 AddTextEffectメソッドを使います。

PresetTextEffectは
このメソッドの文字の見栄えの効果を指定する引数です。

構文は次の通りです。

expression.AddTextEffect( _
    PresetTextEffect, _
    Text, _
    FontName, FontSize, _
    FontBold, FontItalic, _
    Left, Top)

適宜改行してあります。
下のコードもこのような改行になっています。

引数が何を指定しているかは
引数の名前を見れば大体わかると思います。
詳細はVBAヘルプで。

PresetTextEffect引数には、

msoTextEffect1
msoTextEffect2
…
msoTextEffect30

このような定数を使ってもいいのですが、
For文を使用するので
下のコードでは0から29の整数(変数i)で指定します。

コードはこちら

Sub macro110619a()
'PresetTextEffectのビジュアル一覧作成

    Sheets.Add
    Dim i As Integer
    Dim strTextEffect As String
   
    For i = 0 To 29
        strTextEffect = "msoTextEffect" & i + 1
        ActiveSheet.Shapes.AddTextEffect( _
            i, _
            strTextEffect, _
            "ＭＳ Ｐゴシック", 28#, _
            msoFalse, msoFalse, _
            100#, i * 60 _
            ).Select
    Next i
   
End Sub

実行するとシートにワードアートが作成されます。
下の画像は作成されたワードアートを
見やすく少し移動してからペイントにコピペして保存したものです。

まあ、
あまり使いどころがなさそうですね。

Format関数を使ったのに違う表示形式が自動で適用される件

この自動で表示形式が適用される機能で
イライラすることが間々あります。

何も入力したことのないセルA1に、
次のコードを実行します。

Sub macro110618a()
'Format関数がオートフォーマットされてしまう
   
    Debug.Print Cells(1, 1).NumberFormatLocal
    Cells(1, 1) = format(Now, "yyyy/mm/dd hh:mm:ss")
    Debug.Print Cells(1, 1).NumberFormatLocal
   
End Sub

実行するとイミディエイトには次のように出力されます。

G/標準
yyyy/m/d h:mm

1行目が入力前、2行目が入力後の表示形式です。
Format関数では、yyyy/mm/dd hh:mm:ss と指定しましたが、
自動で異なる表示形式が適用されました。

次のWebページはこのことについてです。
『 Excel で文字列または数値が意図しない表示形式に変換される 』

このページに次のようにあります。

数値にスラッシュ記号 (/) またはハイフン (-) が含まれている場合は、日付形式に変換されることがあ ります。

Format関数の返す値はセルに入れるとき
文字列とみなされ、
文字列から日付形式に変換されるようです。

表示形式が自動的に適用されないようにする方法が
3つ書かれていますので参考にします。

方法1:入力の最初にスペースを入力

Cells(1, 1) = " " & Format(Now, "yyyy/mm/dd hh:mm:ss")

方法2:手動で表示形式を変更

Cells(1, 1) = Format(Now, "yyyy/mm/dd hh:mm:ss")
Cells(1, 1).NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"

方法3:入力の前にアポストロフィを付ける

Cells(1, 1) = "'" & Format(Now, "yyyy/mm/dd hh:mm:ss")

方法1、3の入力後の表示形式は「G/標準」
方法2の入力後の表示形式は指定どおりに、
「yyyy/mm/dd hh:mm:ss」になりました。

方法1、3はセルの見た目は
意図した通りの日付として見えますが、
文字列ですので日付の計算には不向きだと思います。

結局は方法2が無難ではないでしょうか。
そもそもこの方法だとFormat関数を使う必要はありません。

Cells(1, 1) = Now
Cells(1, 1).NumberFormatLocal = "yyyy/mm/dd hh:mm:ss"

これで意図した形式で表示されます。
一行分だけコード入力が手間ですが、
入力した値が日付形式である便利さを考えると
たかが一行です。

あるあるエラー：Integer型

VBAを使い始めて少したつと経験するであろうエラーです。

最初のころはよく理解できないままやっているので、
整数を使うときは全部Integer型を使っていました。

しかし徐々に多くのデータを扱い始めると
このようなエラーに遭遇します。

特に今まで問題なかったのに突然このエラーが発生すると
原因がわからず首をひねります。
原因がわからないエラーほど単純な原因だったりします。

このエラー何が原因でしょうか？

Integer型は、-32,768 ～ 32,767 の範囲の整数をとるので
この範囲を超える値をInteger型に入れようとすると
オーバーフローします。

例えばこんなコードで突然このエラーが起きたりします。

    Dim i As Integer
   
    For i = 1 To Range ("D1").End(xlDown).Row
   
        '…
       
    Next I

このコードの次の箇所、

Range("D1").End(xlDown).Row

この値が32,767を超えるとオーバーフローします。
この値は、
セルD1から下の方向に
値が連続して入っている一番下の行を返します。

ですから扱う行数が多くなるとFor文に使う変数は
Long型にしなければいけません。

また、上のコードの箇所は
D列に何も値が入っていないときもエラーになります。

D列に何も値が入っていないとき、
上のコードの箇所はシートの最終行数を返すからです。

わかれば単純なんですけど、わからないと

オーバーフローって何？
日本語で言って…

という気持ちになります。

Openメソッドを使ってURLが存在するか確かめる

前の記事 『 Webクエリを使ってURLが存在するか確かめる 』で
できなかったこと、
URLが存在するか確かめるを違った方法でやってみます。

ExcelでExcel形式以外のファイルを開いたことは
あまりないのですが、
URLを指定してWeb上のファイルも開けます。

VBAでURLを指定してWeb上のファイルを開くコードはこうです。

Workbooks.Open Filename:="http://aaa.com/aaa.html"

普通にブックを開く要領です。
これを使ってURLの存在を確認する方法の流れは、
まずエラーを無視する状態にします。
それからURLを指定してWeb上のファイルを開きます。
そのURLが存在しないなら次のエラーになります。

実行時エラー'1004'

ファイル 'http://aaa.com/aaa.html' にアクセスできません。次のいずれかの理由が考えられます。

・ ファイル名またはパス名が存在しない可能性があります。
・ 開こうとしているファイルは、ほかのユーザーまたはプログラムによって開かれています。ほかのプ ログラムで開いている場合、ファイルを閉じた後、もう一度実行してください。
・ 保存しようとしているファイルと同じ名前のファイルが、既に読み取り専用のファイルとして保存さ れています。別の名前で保存してみてください。

このエラーになる原因はURLが存在しないだけでなく
インターネットにつながっていないなど
他にも理由があるかもしれません。

ここではインターネットに接続しているという前提で、
上記のエラーが発生したなら
URLが存在しないとしておきます。

コードはこちら

Sub macro110611a()
'URLの存在を確認
    Dim URL As String
    URL = "http://aaa.com/aaa.html"
    On Error Resume Next
    Workbooks.Open Filename:=URL
   
    If Err <> 0 Then
        Debug.Print "ファイルにアクセスできません "
        GoTo Step0
    End If
    Debug.Print "ファイルを開きました"
    Workbooks(URL).Close SaveChanges:=False
Step0:
    On Error GoTo 0
   
End Sub

このコードを実行して

Debug.Print "ファイルにアクセスできません"

の箇所に行けばURLがない。

Debug.Print "ファイルを開きました"

の箇所に行けばURLがあるということです。

ファイルがあった場合は、
ファイルを開きますので少し時間が掛かります。
プロシージャを終える前に、そのファイルは閉じます。

ファイルの開閉に時間が掛かりすぎると思うので
大量のURLの存在確認には不向きです。

Webクエリを使ってURLが存在するか確かめる

前記事でWebクエリについて整理できたので
いろいろ使いたいと思っていたのですが…
とりあえず結論から言いますと、
Webクエリを使ってURLが存在するか確かめることは
できませんでした。

以下できなかった方法です。

存在しないURLを指定して
Webクエリを作成すると
このようなダイアログが出ます。

Application.DisplayAlerts = False で
この警告を出さないようにして、
再び存在しないURLを指定して
Webクエリを作成すると次のようになります。

同じです。
警告を出さなくして、かつエラーを無視して
Err <> 0 の条件で
URLがあるかどうかを調べようと思ったのですが、
できないようです。

理由はVBAヘルプのRefresh メソッドのページにありました。

DisplayAlerts プロパティの設定にかかわらず、
ダイアログ ボックスが表示されます。

とのこと、
おいおい他の方法を考えます。

Webクエリまとめ

今までの記事の中のWebクエリは
マクロ自動記録で記録したものを使っていました。
必須の引数などがいまいちわからなかったので
そのまま放置していたのですが、
整理します。

VBAヘルプのWebクエリ関連のページはわかりにくいので
まず始めにこのWebページをどうぞ、
Webクエリを作成する方法です。
『 Add メソッド 』
「QueryTables オブジェクトの Add メソッド」の箇所です。

さてここを読んでやっと、
Webクエリ作成に最小限な引数とメソッドがわかりました。

Webクエリを作成する最小限のコードはこちら

Sub macro110604a()
'Webクエリ作成
'最小限

    With ActiveSheet.QueryTables.Add( _
        Connection:="URL;https://yumem.cocolog-nifty.com/blog/2011/05/post-faec.html", _
        Destination:=Range("A1"))
        
        .Refresh
    End With

End Sub

ポイントはRefreshメソッドです。
これをしないと何も表示されません。

上のコードを実行してみると
指定したページのTableだけがとりだされます。
これは、WebSelectionType プロパティが
デフォルトでxlAllTablesになっているためです。

次のリンクのページはWebSelectionType プロパティについてです。
『 WebSelectionType プロパティ 』

xlEntirePage (すべて）
xlAllTables (既定値/すべてのテーブル)
xlSpecifiedTables (特定のテーブル)

この３つの値をWebSelectionType プロパティに指定できます。
カッコ内は説明です。

3番目の値をWebSelectionTypeに指定したときは、
テーブルの番号を指定するWebTables プロパティを併せて使用します。

1番目のテーブルを指定するときはこうです。

WebTables = "1"

1番目と3番目のテーブルを指定するときは

WebTables = "1, 3"

このように複数のテーブルを指定するときはコンマで続けます。
指定したページのテーブルの数や
どのテーブルが何番目のテーブルか？
などはいろいろ試行してください。

WebSelectionType プロパティを使用したWebクエリ作成コードはこちら
xlEntirePage、xlAllTables、xlSpecifiedTables について
それぞれ１つずつあります。

Sub macro110604b1()
'Webクエリ作成
'WebSelectionType = xlEntirePage

    With ActiveSheet.QueryTables.Add( _
        Connection:="URL;https://yumem.cocolog-nifty.com/blog/2011/05/post-faec.html", _
        Destination:=Range("A1"))
        
        .WebSelectionType = xlEntirePage
       
        .Refresh
    End With

End Sub

Sub macro110604b2()
'Webクエリ作成
'WebSelectionType =  xlAllTables

    With ActiveSheet.QueryTables.Add( _
        Connection:="URL;https://yumem.cocolog-nifty.com/blog/2011/05/post-faec.html", _
        Destination:=Range("A1"))
        
        .WebSelectionType = xlAllTables '既定値
        
        .Refresh
    End With

End Sub

Sub macro110604b3()
'Webクエリ作成
'WebSelectionType = xlSpecifiedTables

    With ActiveSheet.QueryTables.Add( _
        Connection:="URL;https://yumem.cocolog-nifty.com/blog/2011/05/post-faec.html", _
        Destination:=Range("A1"))
        
        .WebSelectionType = xlSpecifiedTables
        .WebTables = "3"

        .Refresh
    End With

End Sub

ほとんどの場合はデータのみ欲しいと思いますので、
次はHTML形式を取り込まないようにしたいです。

これを指定するのはWebFormatting プロパティで、
書式をどの程度適用するかを指定します 。

次のリンクのページはWebFormatting プロパティについてです。
『 WebFormatting プロパティ 』

さてxlWebFormattingAllとxlWebFormattingNoneはよいとして
xlWebFormattingRTFとは何ぞや？ということで検索、

リッチ テキスト形式と互換性のある書式をインポートします。

と このページにあります。

とりあえず書式は一切要らない方針なので追求しません。
ここからはバイキング方式でいきます。
以下のコードのどれかをmacro110604b系のRefreshの前の
Withステートメントの中、空白の行に足していってください。

.WebFormatting = xlWebFormattingNone '書式なし
.WebFormatting = xlWebFormattingAll 'すべての書式
.WebFormatting = xlWebFormattingRTF '何らかの書式

次は、最初に名前がありきのはずがここでName登場です。
作成してからWebクエリのプロパティを変えたり、
Refreshメソッドを使うときに
どのWebクエリかを指定するときに使います。

.Name = "クエリの名前"

名前でクエリを指定するには次のようにします。
このコードは指定したクエリを更新します。

ActiveSheet.QueryTables("クエリの名前").Refresh

ここからはさらっと流します。

クエリの自動更新のプロパティ、分単位です。
0は更新なし、
使用できる範囲は、0 ～ 32767 の整数です。

.RefreshPeriod = 0 '既定値=0

更新をバックグラウンド (非同期) で実行するには
BackgroundQuery プロパティをTrueにします。

.BackgroundQuery = True

ブックを開いたときにクエリを更新するかを設定するには、
RefreshOnFileOpen プロパティを使います。
Falseが更新しないです。

.RefreshOnFileOpen = False '既定値

ブックを保存するときに
クエリのデータを保存しないで
クエリがあるということだけ保存する場合は

.SaveData = False

よく使いそうなプロパティは
こんなところでしょうか。

その他のプロパティはこちらのページを参照してください。
『 _QueryTable メンバ 』

あとRefreshメソッドを実行する際に、
バックグラウンドで更新したいとき

.Refresh BackgroundQuery:=True
.Refresh True

などと引数を指定してください。