ExcelVBA　ADO接続でデータ追加と上書きについて

Question

Win2000SP-4、Office2000SP-3使用しております。
7,050件入力したExcelデータがあり（Ａファイル）、入力用Excelファイル（Ｂファイル）に入力したデータをＡファイルへ追加、上書きしたいと考えております。
Ａファイルには既に46列7,050行のデータが入力されております。（中には入力されていないセルもあります。）
解説書やネットで、ADO接続、SQL「insert into」「update」文使用でＡファイルをExcelで起動しなくても追加、上書きが可能と知り後述の通り組んでみました。
しかし、「実行時エラー’-2147217913(80040e07)':
[Microsoft][ODBC Excel Driver]抽出条件でデータ型が一致しません。」というエラーが返ってきます。
「insert into」文の項目を１つずつ実行したところ、数量や日付などの列に文字が入力されているからのようで、Ａ、Ｂ両方のデータをすべて文字列に変更し実行してみましたが同じエラーが返ってきました。
本来であれば入力されているデータの見直しをすることが先決なのですが、項目件数が多いこと、入力されている文字列がバラバラでコード化することもかなわずどうしたら良いのか煮詰まっております。
どなたかお知恵をお貸し下さい。

Sub DataTsuika()
    
Dim objADOCon As Object
Dim strDBPath As String
Dim strSQL As Variant
    
 strDBPath = ThisWorkbook.Path
 strDBPath = strDBPath & "\Ａデータ.xls"

 Set objADOCon = CreateObject("ADODB.Connection")

  objADOCon.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
                  "DBQ=" & strDBPath & ";" & "ReadOnly=0"

　'ＡファイルmyTextシートへ追加
strSQL = "insert  into [myText$]" _
           & "(kome,整理番号,許可区分,申請日,許可番号,許可日,前許可番号,前許可日,連絡先郵便番号,連絡先住所,連絡先担当,連絡先電話" _
           & ",占用物件1名称,占用物件1寸法規格,占用物件1数量,占用物件1単位,占用物件2名称,占用物件2寸法規格,占用物件2数量,占用物件2単位" _
           & ",許可期間自,許可期間至,占用料数量,占用料単位",占用料月単価,占用料年単価,占用料月数,占用料金額,占用料年額,分類,備考,更新送付日,更新申請日,登録日) " _
           & "values ('" & Range("A2").Value & "','" & Range("B2").Value & "','" & Range("C2").Value & "','" & Range("D2").Value & "'" _
           & ",'" & Range("E2").Value & "','" & Range("F2").Value & "','" & Range("G2").Value & "','" & Range("H2").Value & "'" _
           & ",'" & Range("L2").Value & "','" & Range("M2").Value & "','" & Range("N2").Value & "','" & Range("O2").Value & "','" _
           & ",'" & Range("U2").Value & "','" & Range("V2").Value & "','" & Range("W2").Value & "','" & Range("X2").Value & "'" _
           & ",'" & Range("Y2").Value & "','" & Range("Z2").Value & "','" & Range("AA2").Value & "','" & Range("AB2").Value & "'" _
           & ",'" & Range("AH2").Value & "','" & Range("AI2").Value & "','" & Range("AJ2").Value & "'" _
           & ",'" & Range("AK2").Value & "','" & Range("AL2").Value & "','" & Range("AM2").Value & "','" & Range("AN2").Value & "'" _
           & ",'" & Range("AO2").Value & "','" & Range("AP2").Value & "','" & Range("AQ2").Value & "','" & Range("AR2").Value & "'" _
           & ",'" & Range("AS2").Value & "','" & Range("AT2").Value & "','" & Range("AU2").Value & "')"

 objADOCon.Execute strSQL
 objADOCon.Close
 Set objADOCon = Nothing
End Sub

補足説明等必要でしたらお知らせ下さいませ。
文字数制限のため「insert into」文の途中と「update」文のコードは割愛させていただきました。回答いただけましたら補足させていただきます。よろしくお願いいたします。

Wendy02 · Accepted Answer

こんにちは。

最初のError の　80040e07　のコードは、ODBC から発せられたものですね。私としては、VBAから受ける印象は、補足のお話を読んでみても、最初の発言とは考えが変わらないのですが、開くのに時間が掛かるのは、別の要因かもしれません。私の今使っているファイルは8MB近くありますが、何のストレスも感じておりません。ただし、VBA中心です。そうしなければ、とても使えたものではありません。

>strSQL = "insert into [bufDaityo$]" _
>　& "(kome,整理番号,許可区分,申請日,許可番号,許可日,前許可番号,前許可日) " _
>　& "values ('" & Range("A2").Resize(, 8).Value & "')"
>うまくお伝えできたか不安ですが、引き続きよろしくお願いいたします。

> Range("A2").Resize(, 8).Value
これは、２次元ですから、これは、１次元に変えないといけません。
順序よく文字列に変えると言うのなら、

テストパターンですが、以下でためしてください。
文字列が取れているはずです。

Sub Test()
Dim strTxt As String
Dim r As Range
Dim c As Variant
Set r = Union(Range("A2").Resize(, 8), Range("L2").Resize(, 4), Range("U2").Resize(, 8), Range("AH2").Resize(, 14)).Cells
For Each c In r
　　 strTxt = strTxt & "','" & c.Value
Next c
strTxt = Mid$(strTxt, 3) & "'"
'Debug.Print strTxt 'これで中身を確認
Set r = Nothing
End Sub

Wendy02 · Answer

こんばんは。

>7,050件入力したExcelデータがあり（Ａファイル）、入力用Excelファイル（Ｂファイル）に入力したデータをＡファイルへ追加、上書きしたいと考えております。

ものすごく基本的なことですが、なぜ、上書きや追加するデータにADOが必要なのでしょうか。
私の記憶に間違いなければ、そのコード自体の問題ですが、VBEdiotr に、いわゆる「生きたオブジェクト」を大量に埋め込むと、その分のメモリを割り引いてしまって、思ったようにはいかなくなるように思います。

なんとなく、論理的エラーではなく、物理的なエラーが出てきているような気がします。
一度でも動いたことがあるのでしょうか。ざっと見た感じでは、そのコードは、使えないと思います。

>ＡファイルをExcelで起動しなくても追加、上書きが可能と知り後述の通り組んでみました。

そのまま、ファイルをオープンしたほうがずっと楽だと思います。もしも、Excel自体がないとか、一つのこだわりがあれば別ですが、そのコードを書くぐらいなら、xls は、xls ファイルのまま扱えばよいと思いますね。仮に、4万行だとしても、そのほうが楽です。

今見た限りでは、単に、4つのエリアしかないのだと思います。
Range("A2").Resize(,8)
Range("L2").Resize(,4)
Range("U2").Resize(,8)
Range("AH2").Resize(,13)

この付けたしや上書きのマクロぐらいは、初歩的な内容だと思います。ただし、上書きというか、UpDate 自体の位置関係があまりはっきりしませんが。

tinu2000 · Answer

まったくもってトンチンカンな回答かも知れませんが？
ＡファイルにＢファイルを追加したいだけですよね。
当然、レイアウトは同じで項目数も同じですよね。
ADO接続でデータ追加をしないといけませんか？
定例作業となるのですか？

僕でしたら、
A.xlsファイルをCSV形式で落として、
B.xlsファイルをCSV形式で落として、
A.csvファイルをワードパッドで開いて、
B.csvファイルをワードパッドで開いて、
ワードパッドのB.csvファイルのタイトル行以外をコピーして、
ワードパッドのA.csvファイルの最終行の後ろに貼り付ける、
ワードパッドのA.csvファイルを名前を付けて（C.csv)保存、
C.csvをダブルクリックで読み込み、
エクセル形式でC.xlsとして保存
これで、C.xlsは２つが合わさったファイルとなります。

テストデータ46列7,050行のデータを２つ作って、くっつけました。
テレビを見ながら（ディープインパクト）出来ましたよ。（笑）

外していたらごめん。

ExcelVBA ADO接続でデータ追加と上書きについて

こんにちは。

こんばんは。

この回答への補足

まったくもってトンチンカンな回答かも知れませんが？

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

ExcelVBA　ADO接続でデータ追加と上書きについて