ExcelVBAでCSVファイル上書き後の読み込み

質問

マクロ１では元々存在するold.csvファイルを上書きしています。
old.csvはテキストエディタを使い、手で作ったファイルです。

マクロ２では、マクロ１で上書きされたold.csvファイルを読み込み
データが終了するまで処理します。
この時、old.csvは存在するのに
実行時エラー　'3021'
BOFとEOFのいずれかがTrueになっているか、または現在のレコードが削除されています。
要求された操作には、現在のレコードが必要です。
となります。

どなたか解決していただけないでしょうか？
Excel2003 SP3です。
どうかよろしくお願いします。

マクロ１
FilePass = "c:\Documents and Settings\デスクトップ\old.csv"

A_CONcsv = "Driver={Microsoft Text Driver (*.txt; *.csv)}; " & _
"DBQ=c:\Documents and Settings\デスクトップ;" & _
"ReadOnly=1"

Call Old_data_UP(StrOld_data, A_CONcsv)

'old.csvファイル出力
Open FilePass For Output As #1
Print #1, StrOld_data
Close #1

Function Old_data_UP(FStrOld_data, CONcsv)
new_x.csvを読み込みold.csvを更新するための編集をしています。
（old.csvは最初だけ手で作り、次からはマクロ１で更新された物を使いたいです。）
省略します。
End Function

マクロ２
FilePass = "c:\Documents and Settings\デスクトップ\new_x.csv"

A_CONcsv = "Driver={Microsoft Text Driver (*.txt; *.csv)}; " & _
"DBQ=c:\Documents and Settings\デスクトップ;" & _
"ReadOnly=1"

A_CONxls = "Driver={Microsoft Excel Driver (*.xls)}; " & _
"DBQ=c:\Documents and Settings\デスクトップ\db\Mydata.xls;" & _
"ReadOnly=1"

Call data_match(StrNew_x, A_CONcsv, A_CONxls)

'new_x.csvファイル出力
Open FilePass For Output As #1
Print #1, StrNew_x
Close #1

Function data_match(FStrNew_x, CONcsv, CONxls)
new.csvとold.csvとMydata.xlsを読み込み
new_x.csvを出力するための編集をしています。
new.csvはテキストエディタを使い、手で作ったファイルです。
省略します。
End Function

回答No.4

layy · ベストアンサー

＞Excelでマッチングしてファイル出力

普通「マッチング」のロジックでは、ＥＯＦ判定は重要です。
比較する２ファイルのレコード数は同じですか？、違いますよね？。

簡単に言えば、
ファイル１：あ、い、う、お
ファイル２：あ、う、え
ファイル２の「え」、ファイル１の「う」の後は、
共にどういうロジックか、です。
ファイル１の「お」のときの比較対象であるファイル２はＥＯＦになってしまってます。
これと似た感じでしょう。

どんなロジックにしたかは分かりませんが、
ＥＯＦになったときの処置を中心に見たら良いかと思います。

原因箇所がつかめたら、「マッチング」のロジックの見直しでしょう。

お礼

＞ファイル１の「お」のときの比較対象であるファイル２はＥＯＦになってしまってます。
これと似た感じでしょう。

このアドバイスを元に、Debug.Printをあちらこちらに入れて見直しをした結果
原因がわかりました！

やはりnew.csvは値が表示されているのに
old.csvは値が空白で表示され、２件目以降を読み込むことなく
new.csvが先にEOFになっていました。
new.csvが先にEOFにならないことを前提にロジックやテストデータを作っていますので
テストデータがおかしいのでは？と思い、テキストエディタで開いて見てみました。

今まで出力結果をExcelで開いて確認していたのでわからなかったのですが
old.csv更新時、空白行が最後に付加されて出力されていました。
FStrOld_data = FStrOld_data & ＩＤ,名前,キャリア,点数,所属 & Chr(10)
と編集してから一括で出力していたので余計な空白行が付加されたようです。
そして、所属,ＩＤの昇順でデータを読んでいるのでold.csvの１件目の値が空白で表示されたようです。

FStrOld_data = FStrOld_data & Chr(10) & ＩＤ,名前,キャリア,点数,所属
と変えることで正常に処理されるようになりました。

長くて読みずらいロジックを全部載せることができたら
もっと早く解決できたのではないかと思います。
時間を割いて回答をくださいまして
本当にありがとうございました。

回答No.6

layy

原因つかめないから他のやり方で、これもどうかと思いますが。

ロジック見直しは当然として、

ファイル番号を同じ#1でなく変えてみる。

ファイルは最後にはクローズ出来ているか。オープンしたままのロジックはないか。

マクロ実行１回目はＯＫだが繰り返した２回目はおかしいか。

OLDを更新しないケースなら実行前後で同じファイル構造になっているはず。

見る観点はあります。

回答No.5

Wendy02

期待する回答は得られないので、こちらの考え方で書いてみました。どれほどの技術かは分かりませんが、そのままで、ADODBを使うのは無理だと思います。なお、ご質問のエラーは、適当なファイルがないか、コードを間違えているということです。

Sub TestMacro()
　Dim mPath As String, oldFile As String, xlsFile As String, newFile As String
　Dim fNum As Integer, orgNum As Integer, i As Long
　Dim TextLine As String, rn As String, mArray As Variant
　Dim wb As Workbook, sh As Worksheet
　Dim myArray As Variant
　'必ず、mPath は。末尾に\ を後に入れる
　mPath = "C:\Documents and Settings\デスクトップ\"
　oldFile = "001.csv"
　xlsFile = "aa01.xls"
　newFile = "n001.csv"
　'csvのインポート
　If Dir(mPath & oldFile) = "" Or Dir(mPath & xlsFile) = "" Then MsgBox "ファイルが用意されていません。", 48: Exit Sub
　With Application
　　orgNum = .SheetsInNewWorkbook
　　.SheetsInNewWorkbook = 1
　End With
　Set wb = Workbooks.Add
　With wb
　　fNum = FreeFile()
　　Open mPath & oldFile For Input As #fNum
　　Application.ScreenUpdating = False
　　Do While Not EOF(fNum)
　　　Line Input #fNum, TextLine
　　　i = i + 1
　　　myArray = Split(TextLine, ",")
　　　ActiveSheet.Cells(i, 1).Resize(, UBound(myArray) + 1).Value = myArray
　　Loop
　　Close #fNum
　　Application.ScreenUpdating = True
　　Set sh = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
　End With
　'シートのインポート
　With Workbooks.Open(mPath & xlsFile)
　　.Worksheets(1).Cells.Select
　　.Worksheets(1).Cells.Copy sh.Cells
　　.Close False
　End With
　With wb
　 Set sh = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
　 rn = .Worksheets(2).Range("A1").CurrentRegion.Address(0, 0)
　 With sh
　　　'結合の数式
　　 .Range(rn).Formula = _
　　 "=IF(AND(Sheet1!RC<>"""",Sheet2!RC<>""""),Sheet2!RC,IF(Sheet1!RC="""",Sheet2!RC,Sheet1!RC))"
　　 .Range(rn).Value = .Range(rn).Value
　　 '出力
　　 fNum = FreeFile()
　　 Open mPath & newFile For Output As #fNum
　　 For i = 1 To .Range(rn).Rows.Count
　　　mArray = Application.Transpose(.Range(rn).Rows(i).Value)
　　　mArray = Application.Transpose(mArray)
　　　Print #fNum, Join(mArray, ",")
　　 Next i
　　 Close #fNum
　 End With
　End With
　wb.Close False
　Beep
　Application.SheetsInNewWorkbook = orgNum
End Sub

回答No.3

Wendy02

>特に参考にしたのは下記のサイトです。

リンクで紹介された方法は方法は知っています。それは、どちらかというと、Excelが得意でない人用だと思います。仮に便利だと思っても、Excelには、ADODBは、標準装備していませんから、参照設定しても、インスタンスを生成するので、オーバーヘッドが掛かってしまいます。例えば、Excelファイルでは扱えないような巨大なファイル（例：郵便番号.csv）の時に利用するのが一般的だと思っています。

私が始めて見たというのは、二つのConnection オブジェクトを比較する方法が見えてこないのです。ある程度の確信があってしているのだろうから、それなりのロジックを持っているはずだと思いました。だから、逆にご質問をしたわけです。

>BOFとEOFのいずれかがTrueになっているか、
ここの、RecordSet の部分でエラーが発生しているのだろうと思います。そこは書かれていません。

>ExcelやCSVをDB感覚で使えたら良いのにな、という思いから
ということですから、ADODB(Jet)を利用する方法は変えられないとすれば、手が付きません。私の個人的なアドバイスとしては、今の方法は、可能であるという自信がなければ、辞めたほうがよいのではないかと思います。

Fields の数もあるかと思いますから、拾い出す手間もあります。
Line Input なら、Split 関数で引き出すことは出来ますが、Fields はオブジェクトですから、一括では出来ません。

Function data_match(FStrNew_x, CONcsv, CONxls)

別に、コードを出さなくてもよいけれども、そのロジックが分からないのです。Connectionオブジェクトを与えているなら、その後で、RecordSet があるはずです。ロジックというのは、今回の場合、比較する方法ということです。その比較する方法が分からないのです。1対1にしなければ比較できませんから、一旦、配列に置いているのでしょうか？

仮に達成できたとしても、比較はテキストベースで行うので、思った以上に、分かりにくく無駄な部分が多くなるのではないかと思います。また、xls側のファイルは、そのまま変換も出来ませんから、シートに上げるのが早いような気がします。だったら、最初から、CSVのテキストもしく、ワークシート上で比較する方法をすべきではないのかなって思います。

お礼

＞仮に達成できたとしても、比較はテキストベースで行うので、思った以上に、分かりにくく無駄な部分が多くなるのではないかと思います。

はい。かなり無駄が多いと思います。
ですから、たいした処理ではないのに入力文字数制限でマクロ１本分すら載せられないのです。
私が知っている範囲で期待する処理結果を得るためには、ADOでデータを取得して処理する方が早く
趣味で使うマクロなので、処理速度が遅かったり、ロジックに無駄が多かったりしても良いと思いました。

今、CSVデータの件数が約500件、Excelデータの件数が約1500件で処理をしています。
今後、CSVデータの件数が約1000件、Excelデータの件数が約10000件になることを予想しています。
今のままでは処理速度が遅くて使い物にならなくなる気がします。
もっと勉強して、教えていただいたコードのように
データをシート上に展開して処理するよう新たに作りたいです。

時間を割いてサンプルコードまで載せてくださいまして
本当にありがとうございました。
また質問させていただくことがあると思います。
どうぞ、よろしくお願いします。

回答No.2

Wendy02

本当に、Excelなのでしょうか。そういう手法が出来るのですか？何か参考資料があるのだろうと思いますが、私は、始めて見ました。よほど、そういう手法を得意としているなら、それ自体にこちらは何も言えないけれども、ただ、質問の際には、もう少し詳しく説明してほしいです。

ADODB.Connection で両方を読み込み、それを比較するなどという技術は、BOFとEOFとは、片方がバイナリで片方がテキストファイルですから、当然、比較したら、そのようなエラーの現象は分かるのですが、その比較のロジックが理解できません。

>new.csvとold.csvとMydata.xlsを読み込みnew_x.csvを出力するための編集をしています。

Excelを持っていらっしゃるなら、new.csv をシートにExcel上に上げて、Mydata.xlsの特定のシートを、どのように処理するかは知りませんが、それで比較すればよいのではないかと思います。

ただ、例えば、old.csv のA1には値が入ってい、B1 が空の状態で、 new.csvが、A1が空で、B1が入っている状態で、 new_x.csvは、A1, B1のセルに値が入っているものにする場合などは、少なくとも、ワークシート上でもなかなか難しいものではないかと思います。そういう規則性をきちんと組み立てているのでしょうか。

あえて、Jet を利用するというのは、データ・サイズそのものが違うはずですから、それなりの確信がなくてはとても手が出せるものではないと思います。

>Function data_match(FStrNew_x, CONcsv, CONxls)
>new.csvとold.csvとMydata.xlsを読み込み
>new_x.csvを出力するための編集をしています。
>new.csvはテキストエディタを使い、手で作ったファイルです。
>省略します。
>End Function

ここは、どういうロジックを使っているのでしょうか？
もう少し、詳しい内容が必要だと思います。

お礼

＞本当に、Excelなのでしょうか。そういう手法が出来るのですか？何か参考資料があるのだろうと思いますが、私は、始めて見ました。よほど、そういう手法を得意としているなら、それ自体にこちらは何も言えないけれども、ただ、質問の際には、もう少し詳しく説明してほしいです。

ExcelやCSVをDB感覚で使えたら良いのにな、という思いから
自分専用でこのマクロを作りました。
Excelでマッチングしてファイル出力なんて強引だ、ということもわかっています。
特に参考にしたのは下記のサイトです。
http://www.happy2-island.com/access/gogo03/capter00503.shtml

質問するにあたり、入力文字数制限の関係でいろいろ省略してしまいました。
説明不足で申し訳ありません。

時間を割いて回答をくださいまして
ありがとうございました。

回答No.1

layy

デバッグしましょう。

１行ずつやれば、どこで何が起きているか、エラーの原因は多少でも見えてきます。

デバッグできないなら、質問の前にデバッグを学習する。

お礼

デバッグした結果、old.csvは存在するのに実行時エラーが発生しているのではないか？
と思いました。
説明不足で申し訳ありません。

マクロ２
old.csvとnew.csvの差分をnew_x.csvに出力しています。
new.csvのみにデータが存在する場合、Mydata.xlsにそのデータが存在するか見ています。

マクロ１
new_x.csvのデータがMydata.xlsに存在するかを見てold.csvを出力しています。
old.csvは既に存在するファイルなので上書きされます。

マクロ２、マクロ１の順序で実行し、期待通りに出力していることを確認しました。
再度、マクロ２を実行すると実行時エラー　'3021'が発生しました。

そこで、マクロ１実行前に避けておいたold.csvで上書きして
マクロ２を実行するとエラーはなく、期待通りに出力していました。
マクロ１でold.csvを上書きすることによって、old.csvが認識できなくなっているのでは？
と思いました。

質問するにあたり、入力文字数制限の関係でいろいろ省略してしまいました。
上記の質問内容で説明は足りるかな、と不安でしたが
やはり説明不足でした。

時間を割いて回答をくださいまして
ありがとうございました。