Excel VBA で「型が一致しませんエラー

質問

Excel VBA で「型が一致しません」というエラーが発生します。

--------------------------------------------------------------------------------
Dim katacode as String

katacode = "(1001,1002,1005,1010,1015,1020,1030,1035,1036,1040,…（省略）…,1150)"

With ActiveSheet.QueryTables.Add(Connection:= _
pubfncgetConnectString,Destination:=Range("A1"))
.CommandText = Array(_
"SELECT ～
FROM ～
WHERE コード IN katacode

--------------------------------------------------------------------------------

「katacode」の値が、文字列の長さが155までは上手く動くのですが
　それ以上に追加すると「型が一致しません」というエラーが発生します。

原因がわかりましたら教えていただけないでしょうか。よろしくお願い致します。

回答No.2

end-u

>Arrayをとった記述をしてみましたが、SELECT～の文が長くて複雑で
>うまくいきませんでした。
では、試しに
>--------------------------------------------------------------------------------
>Dim katacode as String
>katacode = "(1001,1002,1005,1010,1015,1020,1030,1035,1036,1040,…（省略）…,1150)"
>With ActiveSheet.QueryTables.Add(Connection:= _
>　pubfncgetConnectString,Destination:=Range("A1"))
> .CommandText = Array(_
> "SELECT ～
>　FROM ～
>　WHERE コード IN katacode
>--------------------------------------------------------------------------------
Dim katacode as String
Dim v
katacode = "(1001,1002,1005,1010,1015,1020,1030,1035,1036,1040,…（省略）…,1150)"
With ActiveSheet.QueryTables.Add(Connection:= _
　pubfncgetConnectString,Destination:=Range("A1"))
　v = Array(_
　"SELECT ～",
　" FROM ～",
　" WHERE コード IN " & katacode
　.CommandText = Join(v, "")

みたいにワンクッション入れてみたらどうなりますか？

もしくは、例えば
Sub sample()
　　Dim katacode As String
　　Dim s(2) As String

　　katacode = "(1001,1002,1005,1010,1015,1020,1030,1035,1036,1040,…（省略）…,1150)"
　　s(0) = "SELECT ～"
　　s(1) = "FROM ～"
　　s(2) = "WHERE コード IN " & katacode
　　MsgBox Join(s)
End Sub
..のように適宜、解かり易く句単位で区切って文字列型配列に入れ、
Join関数で結合しても良いかと思います。

お礼

回答ありがとうございました。
試してみましたが
.CommandText = Join(v, "")　の記述では同じく「型が一致しません」エラーになりました。
Join(s)　の記述もうまくいきませんでした。

結果、「katacode」の値を見直して減らしてもらい対応することになりました。

いろいろ勉強になりました。
度々回答いただき感謝しております。

回答No.1

end-u

CommandTextプロパティの値の設定に、Array関数を使っているからです。
（おそらく）この時のArray関数の引数に１要素あたりの文字数制限があると思われます。
（255or256文字くらい？）

マクロ記録で、SQL文が長いとArray関数を使ったものが記録されますが、
これはマクロ記録時の制限によるものです。
実際にはCommandTextプロパティには255文字を超えた文字列を直接設定できます。
例えば
.CommandText = Array( _
　　　　"SELECT field1, field2, field3, ..(途中省略）.., fie" _
　　　　, _
　　　　"ldX, fieldXX, ..(途中省略）.., fieldXXX " & Chr(13) & "" & Chr(10) & "_
　　　　, _
　　　　"FROM [data$]" & Chr(13) & "" & Chr(10) & "WHERE (CODE=?)")

こんな感じのCommandTextは

.CommandText = "SELECT field1, field2, ..(途中省略）.., fieldXXX FROM [data$] WHERE (CODE=?)"

..みたいに繋げて書く事ができます。

お礼

回答ありがとうございました。
Arrayをとった記述で試してみます。

補足

Arrayをとった記述をしてみましたが、SELECT～の文が長くて複雑で
うまくいきませんでした。
他にいい方法がありましたら教えてください。