Excel ADOのエラー

ADOでExcelのシートAから、条件に一致する行の必要なセルの値をシートBにコピーしています。
検索する行を広げると添付のエラーが発生します。
特定の行や特定のセルで、というわけではありません。
検索行範囲を狭めれば、エラーが発生した行の値も正常に取得できます。
なんらかの上限に引っかかっているのだと思いますが、それが何だかわかりません。
どなたか解決ににつながるヒントをお教えください。
よろしくお願いいたします。

【コードのスケルトン】

Type Structure構造体
メンバー1 As Variant
メンバー2 As Variant
メンバー3 As Variant
End Type

Sub リスト更新()
Const adOpenKeyset = 1
Const adLockReadOnly = 1
Dim Struct構造体() As Structure構造体

：
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Provider = "Microsoft.ACE.OLEDB.12.0"

cn.Properties("Extended Properties") = "Excel 12.0;HDR=NO;IMEX=1"

cn.Open ThisWorkbook.FullName

strSQL = ""
strSQL = strSQL & " SELECT F1,F2,F4 "
'strSQL = strSQL & " FROM [シートA$A1:C300" & "] "
' シートAが300行あるとして、↑この範囲指定だと下記＊のどこかでエラー
strSQL = strSQL & " FROM [シートA$A1:C10" & "] "
' ↑このくらいの範囲の指定だとエラーは発生せず。
' エラーが発生した行を含めて狭い範囲を指定すればエラー発生せず。
strSQL = strSQL & " WHERE (F1 IS NOT NULL) "

rs.Open strSQL, cn, adOpenKeyset, adLockReadOnly
ReDim Struct構造体(rs.RecordCount - 1)

rs.MoveFirst
i = 0

Do Until rs.EOF
Struct構造体(i).メンバー1 = rs(0) ' ＊このどこかでエラー
Struct構造体(i).メンバー2 = rs(1) ' ＊エラーになる行は不定
Struct構造体(i).メンバー3 = rs(2) ' ＊print rs(N) で添付のエラーになる

rs.MoveNext
i = i + 1
Loop

For i = 0 To rs.RecordCount - 1
Worksheets("シートB").Cells(i + 4, 1) = Struct構造体(i).メンバー1
Worksheets("シートB").Cells(i + 4, 2) = Struct構造体(i).メンバー2
Worksheets("シートB").Cells(i + 4, 3) = Struct構造体(i).メンバー3
Next i

End Sub

質問者からの補足コメント

• 「エラーの出る行がマチマチ」と書きましたが、セル範囲が同じであれば必ず同じ行の同じrs(N)でエラーになります。
  
  'strSQL = strSQL & " FROM [シートA$A1:C300" & "] "
  
  例えば上記で、30行目のrs(1)でエラーが発生したとき、
  
  'strSQL = strSQL & " FROM [シートA$A25:C35" & "] "
  
  というように範囲を狭めれば、30行目ではエラーは発生せず正常終了する、ということです。

    補足日時：2018/05/13 05:22

• Excel 2007-2010 マクロ有効ブック (.xlsm) "Excel 12.0 Macro"
  
  を試してみましたが、結果は変わらないようです・・

    補足日時：2018/05/13 11:19

No.6 ベストアンサー 回答者： よろずやkinchan 回答日時： 2018/05/13 06:52

全体のメモリ消費量ではなく、EXCELが使っているメモリサイズです。

エクセルは、スタックエリア（数値などの変数領域）をメモリの若い方から順に使って行き、
文字列等のエリアはメモリの反対側から使っていきます。
その両者がぶつかると、メモリオーバーフローです。
案外小さい値でパンクします。
データサイズ＋Variant型の消費メモリ(各24バイト)と配列(各４バイト)は概算でどのくらいでしょう。

この回答へのお礼

よろずやkinchanさん

朝早くから回答ありがとうございます。

>データサイズ＋Variant型の消費メモリ(各24バイト)と配列(各４バイト)は概算でどのくらいでしょう。

データサイズはまちまちで、日本語で最大30文字程度、平均は5文字くらいかと（ところどころ数値もあり）。
Structureのメンバーは21個なので 24バイト×21個＝504バイト

なんとなくメモリオーバーフローのような気がしてきました。
スケルトンのコードとして
strSQL = strSQL & " SELECT F1,F2,F4 "
のように書きましたが、実はSELECTの後にFnが21個並んでます。
で、行の範囲を色々変えて試しているうちに、エラーにはならないのに検索結果の行数（rs.RecordCount）が変わってしまうことがわかりました。
単純化すると、下記で29～32行の範囲で WHERE (F1 IS NOT NULL) 条件で検索すると rs.RecordCount は 1 なのに
28～32行の範囲で検索すると rs.RecordCount が 0 になってしまうことがある、ということです。

行番号 F1
28 NULL
29 NULL
30 ああああ
31 NULL
32 NULL

1回の読み込みの行数を絞ってLoopさせるのが常套手段でしょうか・・

お礼日時：2018/05/13 08:07

No.9 回答者： よろずやkinchan 回答日時： 2018/05/13 11:21

セル一つ一つに格納する処理は遅いですよ。

構造体を使ってますが、シートに貼り付ける速度を考えると
２次元配列の方が一気に貼り付けでき速いです。
２１個のどこに入れるか添え字だけだとプログラムの可読性が低下しますが
Enum 宣言で
メンバー１＝０
メンバー２＝１
などと宣言しておけば、可読性が向上します。

ADODB.Recordsetを使うなら
CopyFromRecordsetなんてので一行または全行一度に格納するとか。

そもそも、なんで配列に入れる必要があるのでしょう？
現状では、シートA→ADODB.Recordset→配列→シートＢ
と、メモリを食いまくっています。
シートＡ→ADODB.Recordset→シートＢ
あるいは
シートＡ→シートＢ
にできないものでしょうか？

この回答へのお礼

よろずやkinchanさん

アドバイスありがとうございます。

>そもそも、なんで配列に入れる必要があるのでしょう？

他にもいくつか条件（※）があって、SQLで取得したデータをそのまま貼り付けられないのです。
SQL 1文で書ければいいんですが、私にはそのスキルがないもので処理的にはかなり多くなってます。

（※）メンバー1と合致する、他のシートのセルAを探してその行のN行上（何行上かは探してみないとわからない）のセル値を取得して・・とか。

少し考え方を変えて別の方法で実現しようかと思います。

お礼日時：2018/05/14 06:32

No.8 回答者： めぐみん_ 回答日時： 2018/05/13 08:28

No.7のお礼について。

ちょっと寝ぼけてたかな？

別件で気になったのは接続文字で Properties("Extended Properties") = "Excel 12.0; とあるのですが、以前私が調べてた際に

Excel 97-2003 ブック (.xls) "Excel 8.0"
Excel 2007-2010 ブック (.xlsx) "Excel 12.0 Xml"
Excel 2007-2010 マクロ有効ブック (.xlsm) "Excel 12.0 Macro"
Excel 2007-2010 XML 以外のバイナリ ブック (.xlsb) "Excel 12.0"

このような記事を見つけてたのですが、余り関係ないんですかね？

この回答へのお礼

.xlsm ファイルです。

>Excel 2007-2010 マクロ有効ブック (.xlsm) "Excel 12.0 Macro"

こういう書き方もあるんですか。
試してみます。

お礼日時：2018/05/13 10:42

No.7 回答者： めぐみん_ 回答日時： 2018/05/13 07:57

そのエラーに類似する記事で

http://hro-blog.blogspot.jp/2018/03/excelvba.html
こんなのもありました。

空白セルに対してイレギュラー的にエラーが出る場合があるそうです。
コードでは１列目に対してのみそれを回避するようにしているみたいですが、２列目と４列目も回避させられないのでしょうか？
回避させられなければNo.4のようにデータを差し替えるとかになるのでは？

検証できないですが
WHEN NULL
なのか
WHEN IS NULL
なのか
どうなんだろう？

この回答へのお礼

めぐみん_さん

色々ありがとうございます。

http://hro-blog.blogspot.jp/2018/03/excelvba.html
この記事は「RecodeSetのデータが全てNULLの場合」ですよね。
なので WHERE (F1 IS NOT NULL) を書いておけば少なくとも上記記事のエラーにはならないかと。
実際エラーが発生した行のRecodeSetのデータを確認しましたが、rs(0)にはデータが入っており
例えばrs(18)でエラーが発生して、イミディエイトウィンドウで print rs(18) しようとすると、
同じエラーが表示されます。

お礼日時：2018/05/13 08:16

No.5 回答者： めぐみん_ 回答日時： 2018/05/13 06:05

ちなみに『自分自身への接続』であるので、実行する際に他に何か作業とかされてませんか？

例えば直前の作業がいつも同じだったとか、実行前にデータを打ち込んでたけど上書き保存をしてなかったとか。
昔は自分自身への接続は余り感心されない方法として言われてたのですが、バージョンが変わって改善されてるのかな？
わたしのは古いのでどうなのかは検証できないですが。

No.4 回答者： めぐみん_ 回答日時： 2018/05/13 05:56

No.3です。

CASE式で条件分岐をSQL文に任せる
https://qiita.com/sfp_waterwalker/items/acc7f95f …

を参考に

strSQL = strSQL & " SELECT F1,F2,F4 "

を

strSQL = strSQL & " SELECT F1, CASE F2 WHEN NULL THEN "" ELSE F2 END AS F2 , CASE F4 WHEN NULL THEN "" ELSE F4 END AS F4"

とか？
⇒ "" にすべきか 0 にすべきかは判断がつかなかったですし、F4 も同じなのかどうか・・・・？ですけど。
取り敢えず他の行に合わせて "" か 0 か等決めてみるとか？

No.3 回答者： めぐみん_ 回答日時： 2018/05/13 05:42

No.2です。

補足を見て。

その３０行目には何があるのでしょう？

Variant型で作られていても、それ以前の行の値型と『３０行目の値型』が違うと言われているのではなくて？

蛇足な疑問ですけど。

＞'strSQL = strSQL & " FROM [シートA$A1:C300" & "] "

って

'strSQL = strSQL & " FROM [シートA$A1:C300] "

とは書けない物なのでしょうか？（私セル範囲を指定して書いた事がないし、文字列として考えるとちょっと気になっただけですが）

この回答へのお礼

めぐみん_さん

朝早くから回答ありがとうございます。

＞'strSQL = strSQL & " FROM [シートA$A1:C300" & "] "
>って
>'strSQL = strSQL & " FROM [シートA$A1:C300] "
>とは書けない物なのでしょうか？

すみません。これだけ見るとヘンですよね。
実際はシートAの行数は日によって変わるので最終行は下記のように変数で扱っています。
そのままテストで固定行数入れてました。

strSQL = strSQL & " FROM [シートA$A1:C" & LastRow & "] "

お礼日時：2018/05/13 08:08

No.2 回答者： めぐみん_ 回答日時： 2018/05/12 19:16

Sheet名にセル範囲を指定するのは初めて見ました。

で、エラーの出る行がマチマチと言うけれど、その時データの型に前行と変化しているとかはないのでしょうか？

この回答へのお礼

めぐみん_さん、

回答ありがとうございます。
データの型は、変化するといえば変化します。
文字が入っているときと空白（データとしては NULL）のときがあります。
ただ、そういうことがほとんどのデータなので、前行と違っているからエラーということでもなさそうなんですが。

お礼日時：2018/05/13 05:13

No.1 回答者： よろずやkinchan 回答日時： 2018/05/12 14:09

単純にメモリオーバーフローなんじゃないですか？

タスクマネージャでメモリサイズを確認できます。

この回答へのお礼

よろずやkinchanさん、

回答ありがとうございます。
メモリは8GBで、タスクマネージャーで見ていても利用は5GB前半なのでメモリオーバーフローではなさそうなんですが・・

お礼日時：2018/05/13 05:14