Excel １セル当りの文字数が２５５文字を超える場合のＡＤＯからの取

Excel　１セル当りの文字数が２５５文字を超える場合のＡＤＯからの取り扱いについて

前回質問した続きになるのですが、VBA（ADO)で自分自身のSheet間で抽出・コピー・ペーストを行っているのですが、抽出の際に、1セルの内容が255文字を超えている場合、エラーが発生します。

strSQL = "Select * from [sheet1$] where Col1 like '" & schStr & "%'"

Set RS = New ADODB.Recordset
RS.Open SQL, CN, adOpenStatic, adLockReadOnly

RS.Open でエラーが発生します。

私的には、255文字を超えているセルに関しては2行に分けるしかないのかなと考えております。
何か「こうすればいいよ」的なアドバイス頂けましたら幸いです。

よろしくお願い致します。

No.1 回答者： end-u 回答日時： 2010/07/12 23:18

1)ADOでExcelを読み込む場合、256文字以上は切り捨てられる事があります。

　回避するには、８行目までのスキャンデータ中に
　最大文字数のダミーデータを置くか、
　http://support.microsoft.com/kb/189897/ja
　レジストリを修正するか、などになります。
　ただし、全レコードをスキャンするわけですから、
　パフォーマンス的に問題ありそう。

2)また、開いているExcelBookにADOでアクセスすると
　メモリリークが発生します。
　大量のデータを繰り返し抽出する作業には向いていません。
　#OSがVista以降になって改善されたかも。検証不足。

3)ADOとは関係ない部分で、2003では配列内文字列911文字まで
　という制限があります。
　http://support.microsoft.com/kb/818808/ja

255文字制限の場合は切り捨てられるだけで、エラーは発生しません。
仮に911文字制限に引っ掛かったとしても、CopyFromRecordsetメソッドの時に
エラーになります。
ですので
>RS.Open でエラーが発生します。
これは別の要因じゃないでしょうか。

いずれにしても、(2)の不具合がありますから、
自Bookに対するADOアクセスはあまりおすすめしません。

どうしてもADOを使いたい場合、
GetRowsメソッドで抽出結果データを配列に取得します。
その後、配列をLoopし行列をTransposeさせながら文字長をチェックし、
255文字超えるデータを別配列に退避して消去し、
後から１個ずつセルにセットさせたり、

またはvbTabとvbCrLfで配列要素を連結して文字列を作り、
DataObject経由でシートに貼り付けたりとか。

...などが考えられますが、
それよりも、Excelの基本機能を使った方が簡単です。


提示条件と同じ事をAdvancedFilterメソッドを使って
（[データ]-[フィルタ]-[フィルタオプションの設定]）

Sub Macro1()
　　Dim schStr As String　　'抽出条件
　　Dim ws　　 As Worksheet '抽出条件書き出しシート
　　Dim r　　　As Range　　 '元データ範囲

　　With ActiveWorkbook
　　　　'元データ範囲取得
　　　　Set r = .Sheets("sheet1").UsedRange
　　　　Set ws = .Sheets.Add
　　　　schStr = InputBox("schStr")
　　　　'元データの見出し（Col1）をセット
　　　　ws.Range("A1").Value = r.Item(1).Value
　　　　'抽出条件をセット
　　　　ws.Range("A2").Value = schStr
　　　　'[フィルタオプションの設定]
　　　　r.AdvancedFilter Action:=xlFilterCopy, _
　　　　　　　　　　　　 CriteriaRange:=ws.Range("A1:A2"), _
　　　　　　　　　　　　 CopyToRange:=.Sheets.Add.Range("A1"), _
　　　　　　　　　　　　 Unique:=False
　　End With
　　Set r = Nothing
　　Set ws = Nothing
End Sub

ActiveWorkbookにシートを２枚追加し、
１枚はAdvancedFilterの抽出条件書き出し用とします。
もう１枚に抽出結果を書き出します。
あくまでサンプルですから、既存シートへ抽出するようにも変更できます。