エクセルのシートにSQLで入力すると文字列にシングルクオーテーションがつく

Question

前提・実現したいこと

エクセルのシートに他シートのデータをSQLで入力すると、
文字列先頭にシングルクオーテーションがつくのを回避したい

(SQLで入力したい理由は、入力シートとデータ一覧を別ブックにして、ブックを開かずにデータ一覧に書き込みたいためです）
(ScreenUpdatingは理解しています。)

発生している問題・エラーメッセージ

入力したデータの文字列にシングルクオーテーションが表示される

該当のソースコード

Sub test()

    Dim clm1 As Long
    Dim clm2 As String
    Dim clm3 As String
    
    With Worksheets("入力シート")
        clm1 = .Range("A1").Value
        clm2 = .Range("D1").Value
        clm3 = .Range("D2").Value
    End With


    Dim objCn As New ADODB.Connection
    Dim objRS As ADODB.Recordset
    Dim strSQL As String

    'コネクション
    With objCn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0"
        .Open ThisWorkbook.Path & "\test.xlsm"
    End With
  
    'SQL作成
    strSQL = ""
    strSQL = strSQL & " INSERT INTO [一覧データ$] (列１,列２,列３)"
    strSQL = strSQL & " VALUES ( " & clm1 & "," & "'" & clm2 & "'" & "," & "'" & clm3 & "'" & ")"

    '接続
    Set objRS = New ADODB.Recordset
    Set objRS = objCn.Execute(strSQL)
  

    '切断
    objCn.Close
    Set objRS = Nothing
    Set objCn = Nothing
End Sub

試したこと

事前にデータ一覧シートの書式設定を文字にしてもダメでした。
データを書きこんだ後に書式設定のクリアをすれば消えますが、その場合ブックを開かないといけないので、
SQLで入力する意味がないなと思っております。

接頭辞については以下のサイトを見ました。
接頭辞の解説と削除方法

excelのバージョンは2010です。
近々パソコンの買い替え予定があり、2019になる予定です。

どうぞよろしくお願い致します。

Answer 1

Excel2013で実験してみたら、再現したりしなかったりしました。
(1) 最初、勘違いで、[入力シート]と[一覧データ]を同一Book内に置いて実行したら、シングルクオーテーションが付きました。
(2) 勘違いに気づき、[入力シート]と[一覧データ]を別のBookに置いて実行すると、シングルクオーテーションは以下のように付いたり付かなかったりしました。
(2a) (1)を実行してデータにシングルクオーテーションがついた状態のまま、引き続き(2)を実行したら、シングルクオーテーションがつきました。
(2b) 「すぐ上のセルの状態を継承するのか」と思い、(1)を実行し、シングルクオーテーションがついたデータを[Delete]キーでクリアしてから(2)を実行しても、シングルクオーテーションはつきました。
(2c) 「クリアでは状態が消えないのか」と思い、(1)を実行し、シングルクオーテーションがついたデータを[削除]-[シートの行を削除]で行削除してから(2)を実行したら、シングルクオーテーションはつきませんでした。

なので、一度、データを行削除してから実行してみてはいかがでしょうか。

Comments

[hatena19]

同じ実験をしてみて、当方の環境でも現象を再現できました。

しかし、上のセルの状態を継承するという仕様はまだ分かりますが、
同一ブックと外部ブックで動作が違うのはなんでやねん！という感じですね。

[bors]

kitasue様 細かく検証して頂きありがとうございます。説明わかりにくくてすいません。
理解できなくて申し訳ありませんが、お伺いしたいのが、(2a)は入力シート→同ブック内一覧データシート→他ブック内一覧データシートという意味でしょうか？それとも入力シートから直接他ブック内一覧データシートでしょうか？
また「シートの行削除を実行して」というのは、出力先の一覧データシートでしょうか？

私の方で、入力シートから直で別ブックの一覧シートにSQLで入力した場合、最初は文字列にシングルクオーテーションがつきませんでした。しかし、数字が文字として入力されました。
（正確にいうと、緑の△に”文字列として入力されています。”とでました。
　書式設定は標準のままです）
そこで、一覧データシートの数字列だけ書式設定を数値にして、SQLを実行したところ、数字で入りましたが、追加された行だけが、書式設定が標準に変更されました。
そこから色々ためしていたら、シングルクオーテーションがつくようになっていました。
自分で何をしたかよくわからないため、もう少し検証してご報告いたします。

[kitasue]

私は最初勘違いをして、test.xlsmにプログラムも[入力シート]も[一覧データ]も収めたのです。そしてプログラムを実行したらシングルクォーテーション付きでデータが作成されました。その後勘違いに気づき、他のBookにプログラムを置き、実行したら、やはりシングルクォーテーションが付きました。それが(2a)です。
> そこから色々ためしていたら、シングルクオーテーションがつくようになっていました。
一度シングルクォーテーションが付いてしまうと、クリアしてもしつこく蘇ってしまうので、[一覧データ]のデータを行削除したら消えませんか？　という問いかけをしたつもりでした。

[bors]

早々のご返答ありがとうございます。しつこくて申し訳ありませんが、
私としては
test.xlsm（プログラムと入力シート）　別ブック（一覧シート）
という形で考えていました。
kitasue様に実行して頂いたのは　
test.xlsm（入力シートと一覧シート）　別ブック(プログラム)
でしょうか？
検証してみたいので、お手数ですが、ご返答よろしくお願い致します。

[kitasue]

当方で検証したのは、
test.xlsm（一覧シート）　別ブック(入力シートとプログラム)
です。
borsさんの
test.xlsm（プログラムと入力シート）　別ブック（一覧シート）
の別ブックはどこで指定していますか？

[bors]

ご返答ありがとうございます。たびたびすいません。
私も
test.xlsm（一覧シート）　別ブック(入力シートとプログラム)
でした。
よく確認せず申し訳ございません。検証してみます。

[bors]

お待たせしました。
book1（入力シートとプログラム）book２(一覧シート)として一覧シートに出力した場合、
book2を開いた状態でプログラムを実行→文字列には必ずシングルクオーテーションがつく
book2が閉じた状態でプログラムを実行→前行にシングルクオーテーション有だとつく
〃　　　　　　　　　　　　　　　　　→前行にシングルクオーテーション無だとつかない
となりました。
ブックが開いているかどうかが優先され、その次にkitasue様のおっしゃるとおり、前行のデータが影響するようです。
ブックが開いている場合、数字列は書式設定の有無でシングルクオーテーションがつくつかないが
変わるようです。（これは詳細に検証していません）
ブックを開かずにと自分でいいながら、ブックを開きながら検証していてわけがわからなくなっていました。
また、ご指摘の通り、データを削除したい場合は行削除しないとダメでした。
お付き合い下さりありがとうございました。

[kitasue]

フィードバックありがとうございました。

Answer 2

当方の環境ではそのコードで文字列先頭にシングルクオーテーションはつかずに入力できました。
excelのバージョンは2019です。
バージョンの違いなのか、それともどこかに設定があるのか、どうなんでしょうね。

最悪、ブックを開くしかないように思いますが、その時は、下記のようにすると普通に開くより高速化できます。
Newで新規Excelインスタンスを生成してそちらで開く。

　　Dim xls As New Excel.Application
　　Dim wb As Workbook
　　Application.ScreenUpdating = False
　　Set wb = xls.Workbooks.Open(ThisWorkbook.Path & "\test.xlsm")

    '以下略

Comments

[bors]

検証ありがとうございます。kitasue様の方法でも試していただきありがとうございます。kitasue様のコメントに記載させていただきました通り、もう少し検証して再度ご報告いたします。
１つお伺いしたいのが、新規インスタンスを生成してブックを開くことができないのですが、
どうしてかお分かりになるでしょうか？
testブックと同一フォルダ内にブックを作成
教えていただいたコードに、
Application.ScreenUpdating = True
Range("A1").Select
を追加して実行しましたが、青いクルクルが２～３秒でてブックが開きません。エラーは出ません。
参照設定でMicrosoft Excel 14.0 Object Libraryにチェックは入っています。
レイトバインディングで実行してもダメでした。
32ビット版なのですが、何か関係しているでしょうか？

[hatena19]

Newしたエクセルから開くと非表示で開きますので、Selectをしないようにコーディングしてください。
データの転記ならSelectする必要はないです。

[bors]

ご返答ありがとうございます。新規インスタンスを生成してブックを開くことができました。また、kitasue様へのコメント欄の方にsqlでの実験結果を記載させていただきました。

Answer 3

先頭のシングルクオートは、続く文字を文字列として扱うことを示すものです。

質問のプログラムでは、clm1が数字(Long)、clm2とclm3が文字列(String)であると宣言されています。

clm1,clm2,clm3の値をExcelのセルに入れると、clm1が入れられるA列は数字なので右詰になり、clm2とclm2が入れられるB,C列は文字列なので左詰になっています。

Comments

[hatena19]

VBAのコードとSQLのコードは全く別物です。
VBAからSQLコードは実行できませんので、ADOを介して実行してもらいます。その時、SQLコードはADOに文字列として渡します。
VBAコードの方で宣言している型はSQLには影響しません。

[bors]

coco_bauer様 ご回答ありがとうございます。私もVBA側の宣言が理由なのかと思ってvariantにして試したりしたのですが、ダメでした。hatena19様のご指摘の通り、SQLには影響していないようですね。難しいです。