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

質問

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」文のコードは割愛させていただきました。回答いただけましたら補足させていただきます。よろしくお願いいたします。

回答No.3

Wendy02 · ベストアンサー

こんにちは。

最初の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

お礼

おはようございます。たびたびのご回答ありがとうございます。
教えていただいたコードを流用し文字列を取り出すことができ、エラーが起きることなく追加、更新ができました。
ファイルを２つにしたことにより、個々のファイルサイズは作業前よりは軽くなっていますが、ユーザー書式や名前の定義、VB用のコマンドボタン等たくさんのオブジェクトがありますので、そのせいで動作が重くなってしまっているようですね。
当初よりおっしゃられていたご意見を参考にその辺りの見直しと改良を行っていこうと思います。
「Union」「Resize」は今回初めて知りましたが、これは他の場面でも活用できそうです。
本格的にVBAを使うのは今回が初めてでわからないことだらけでした。
なかなか目当ての解説が見つけられず、解説書の山が高くなって行くばかりです。
またわからない所が出てくると思いますが、その際はどうぞよろしくお願いいたします。
ありがとうございました。

回答No.2

Wendy02

こんばんは。

>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 自体の位置関係があまりはっきりしませんが。

お礼

間違えて補足へ書き込みしてしまいました。
ご回答ありがとうございます。
引き続きよろしくお願いいたします。

補足

ご回答ありがとうございます。
もともとはABとも１つのファイルだったものでして、入力用シートで新規データ追加・整理番号でデータ抽出後変更更新（上書き）、7,050行データのあるシートでオートフィルタを使用してデータ検索後、印刷用シートで帳票印刷等を行っていたのですがファイルサイズが増大し（8MB超）、ファイルを開くのに非常に時間がかかっていました。客先のパソコンでは4～5分かかってしまうのです。
客先の要望がなるべく現在の操作手順を変えず、早く開くようにできないかということで、まず7,050行入力されているシートを切り離してＡ、Ｂというファイルにしました。それでもＡファイルが4MBありましてファイルオープンに時間がかかってしまい、色々な方法（mdbやテキストファイルを使用する等）を試して現在のADOという形に行き着いたのです。
質問時に掲載したコードにつきましては作成当初の確認時は追加更新できておりました。ですが昨日いきなりエラーが出てしまいまして…
現在は全ての項目を文字列にできないか方法を検討中です。（セルの表示形式を文字列にしただけでは駄目なようでエラーが返ってきました。）
書き込みしていただいたコードを試してみましたが、同様のエラーが返ってきてしまいました。
strSQL = "insert into [bufDaityo$]" _
　& "(kome,整理番号,許可区分,申請日,許可番号,許可日,前許可番号,前許可日) " _
　& "values ('" & Range("A2").Resize(, 8).Value & "')"
うまくお伝えできたか不安ですが、引き続きよろしくお願いいたします。

回答No.1

tinu 2000（@tinu2000）

まったくもってトンチンカンな回答かも知れませんが？
ＡファイルにＢファイルを追加したいだけですよね。
当然、レイアウトは同じで項目数も同じですよね。
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行のデータを２つ作って、くっつけました。
テレビを見ながら（ディープインパクト）出来ましたよ。（笑）

外していたらごめん。

お礼

ご回答ありがとうございます。
もともとはABとも１つのファイルだったものでして、入力用シートで新規データ入力し、7,050行データのあるシート（データシート）へ追加・整理番号でデータ抽出後変更してデータシートへ更新（上書き）、データシートでオートフィルタを使用してデータ検索後、印刷用シートで帳票印刷等を行っていたのですがファイルサイズが増大し（8MB超）、ファイルを開くのに非常に時間がかかっていました。客先のパソコンでは4～5分かかってしまうのです。
客先の要望がなるべく現在の操作手順を変えず、早く開くようにできないかということで、まずデータシートを切り離してＡ、Ｂというファイルにしました。それでもＡファイルが4MBありましてファイルオープンに時間がかかってしまい、色々な方法（mdbやテキストファイルを読み込む等）を試して現在のADOという形に行き着いたのです。
書き込みしていただいた一連の手順を自動化する方法がすぐには思いつきませんが、OpenTextやOpen～Input～等は既に試しておりまして、ファイル読み込み時や抽出時にデータの欠損が見られたため、テキストファイルを読み込む方法は諦めたのです。
（数量や日付の列に文字が入力されているデータを読み込むと、そのセルが空白になって読み込まれてしまうのです。）
かれこれ２週間程この件にかかりきりで頭が混乱しています。
最悪、ファイルオープンで作業をしていただくしかないかと。（開くのに時間はかかりますがコピペなので貼付先さえ間違わなければ一番確実と思いますので。）
何か良い方法がございましたら引き続きよろしくお願いいたします。