下記のコードを実行すると、
.Cells(1, 2).Value = rs(1)
でエラーが発生するのですが、理由がわかりません。
rs(1) を rs(1).Value にすると回避できるので、
既定のプロパティか何かが関係しているのかな？とは思うのですが・・・


' ActiveX Data Object Library
' Excel Library
' を参照設定
Sub Main()
  
  Dim cn      As ADODB.Connection
  Dim rs      As ADODB.Recordset
  Set cn = New ADODB.Connection
  cn.Open "UID=***;PWD=***;Database=***;Server=***;Driver={SQL Server};DSN='';"
  Set rs = cn.Execute("select '''aa', ''") ' "'aa" と "" を抽出
  
  Dim xlApp   As Excel.Application
  Dim xlBook  As Excel.Workbook
  Set xlApp = New Excel.Application
  Set xlBook = xlApp.Workbooks.Add()
  With xlBook.Worksheets(1)
    .Cells(1, 1).Value = rs(0) ' Ａ１セルに 'aa をセット
    .Cells(1, 2).Value = rs(1) ' Ｂ１セルに空の文字列をセット
  End With
  xlBook.Close False
  xlApp.Quit
  Set xlBook = Nothing
  Set xlApp = Nothing
  
  rs.Close: Set rs = Nothing
  cn.Close: Set cn = Nothing
  
End Sub

ちなみに、抽出クエリ
select '''aa', '' （ "シングルクォート・ａ・ａ" と空の文字列 "" を抽出）
を
select '''aa', 'bb' （ "シングルクォート・ａ・ａ" と "ｂ・ｂ" を抽出）
や
select 'a''a', '' （ "ａ・シングルクォート・ａ" と空の文字列 "" を抽出）
にするとエラーが発生しなくなります。

それも意味が分かりません・・・

どのような理由で
select '''aa', '' （ "シングルクォート・ａ・ａ" と空の文字列 "" を抽出）
というような抽出クエリの時だけ
.Cells(1, 2).Value = rs(1)
でエラーが発生するのか教えていただけないでしょうか？

こんにちは。

まず、SQL文は正しいでしょうか？
あと、rs(1)に格納された値は確認することは可能でしょうか？

環境はＶＢ６＋Ｅｘｃｅｌ２０００＋ＳＱＬＳｅｒｖｅｒです。

誰か同様の現象になるか、実行していただけますでしょうか・・・

SQL Server にて、以下のクエリを実行
-------------------------------------------------
create table t1 (
  no int,
  f1 varchar(10),
  f2 varchar(10)
)
go
insert into t1 values(1, '''aa', 'bb')
go
insert into t1 values(2, 'a''a', '')
go
insert into t1 values(3, '''aa', '')
go
-------------------------------------------------

Sub Main()
  
  Dim xlApp   As Excel.Application
  Dim xlBook  As Excel.Workbook
  Set xlApp = New Excel.Application
  xlApp.Visible = True
  Set xlBook = xlApp.Workbooks.Add()
  
  Dim cn      As ADODB.Connection
  Dim rs      As ADODB.Recordset
  Set cn = New ADODB.Connection
  ' *** 部分は、それぞれの環境に合った値を設定
  cn.Open "UID=***;PWD=***;Database=***;Server=***;Driver={SQL Server};DSN='';"
  Set rs = cn.Execute("select f1, f2 from t1 order by no")
  
  ' no = 1 のレコード
  With xlBook.Worksheets(1)
    .Cells(1, 1).Value = rs(0) ' Ａ１セルに 'aa をセット（エクセルの表示上は aa ）
    .Cells(1, 2).Value = rs(1) ' Ｂ１セルに bb をセット
  End With
  
  rs.MoveNext
  
  ' no = 2 のレコード
  With xlBook.Worksheets(1)
    .Cells(2, 1).Value = rs(0) ' Ａ２セルに a'a をセット
    .Cells(2, 2).Value = rs(1) ' Ｂ２セルに空の文字列をセット
  End With
  
  rs.MoveNext
  
  ' no = 3 のレコード
  With xlBook.Worksheets(1)
    .Cells(3, 1).Value = rs(0) ' Ａ３セルに 'aa をセット（エクセルの表示上は aa ）
    .Cells(3, 2).Value = rs(1) ' Ｂ３セルに空の文字列をセット→ここでエラーが発生
  End With
  
  rs.Close: Set rs = Nothing
  cn.Close: Set cn = Nothing
  Set xlBook = Nothing
  Set xlApp = Nothing
  
End Sub

私も同じ環境でテストしてみましたところ、
同じ行でエラーになりました。

ちなみに
.Cells(3, 2).Value = rs(1) & ""
にしたら通りました。

申し訳ありませんが、私には難しかったです。

> rs(1) を rs(1).Value にすると回避できるので、

「既定のプロパティは省略しない方が安全」という教訓ということで…。



> 誰か同様の現象になるか、実行していただけますでしょうか・・・

なりますねぇ。「!」経由でも同様でした。

  With xlBook.Worksheets(1)
    .Cells(3, 1).Value = rs(0)
    .Cells(3, 2).Value = rs!f2    '下記のエラーが発生。rs("f2") でも同様。
  End With

|
| 実行時エラー '-2147417851 (80010105)':
| 'Value' メソッドは失敗しました: 'Range' オブジェクト
|


さらに検証。

<追加実験 1> Field.Value プロパティだけでなく、Recordset.Collect プロパティでも OK。

  With xlBook.Worksheets(1)
    .Cells(3, 1).Value = rs(0)
    .Cells(3, 2).Value = rs.Collect(1)  ' エラーにならない
  End With


<追加実験 2> というより、一度成功した後なら大丈夫らしい……。

  With xlBook.Worksheets(1)
    .Cells(3, 1).Value = rs(0)
    .Cells(3, 2).Value = rs.Collect(1)  ' エラーにならない
    .Cells(3, 3).Value = rs(1)          ' エラーにならない
  End With


<追加実験 3> その上、エラーになるのは最初の 1 回だけということが発覚。

  With xlBook.Worksheets(1)
    .Cells(3, 1).Value = rs(0)
    On Error Resume Next
    .Cells(3, 2).Value = rs(1)  'エラーを読み飛ばしてから
    On Error GoTo 0
    .Cells(3, 2).Value = rs(1)  'もう一度同じことをすると、今度は成功
  End With


<追加実験 4> ちなみに、括弧で囲っただけでも動作する模様。

  With xlBook.Worksheets(1)
    .Cells(3, 1).Value = rs(0)
    .Cells(3, 2).Value = (rs(1))  '括弧で囲むと、エラーにならない
  End With