ExcelとAccessでは何故か改行コードが異なります。
Excelは、改行コードがchr(10)ですが、Accessは、chr(13)+chr(10)になっています。
そのためExcel上のデータをAccessのメモフィールド等、改行コードを意識するフィールドにVBAを使用して転送する場合、あらかじめ改行コードを置換して置く必要があります。
ついつい忘れるので覚書きしておきます。
Excelの改行コートとAccessの改行コードについて
Excelの改行コードは、Chr(10)、すなわちvbLfです。
Accessは、Chr(13)+Chr(10)、つまりvbCrLfとなります。
同じOfficeファミリーでありながらExcelとAccessで改行コードが違う理由は不明です。
ExcelのデータをAccessに転送させる場合の例
Excelから改行コードを意識しないといけないようなデータをAccessにVBAで転送する場合、例えばExcelのメモフィールドの内容をAccessのメモフィールドに書き込む場合、転送時にvbLfをvbCrLfに置き換えておく必要があります。
vbLfからvbCrlfの置き換えはそれほど難しくありません。
Replace関数を使用して、vbLfをvbCrLfに置換する処理を追加すれば良いだけです。
vbLfをvbCrLfに置換してAccessのフィールドに値を書き込むサンプルプログラム
以下にExcelのセルA1の値をAccessのテーブル"T"のメモフィールド"Memo"に書き込むExcelのVBAのサンプルプログラムを記載します。
注意1
サンプルプログラムを実行する際は、「Microsoft ActiveX Data Objects Library」の参照設定を忘れずに。
注意2
OLEDBのバージョンについては、使用環境に合わせてください
Sub Sample01()
Dim con As New ADODB.Connection
Dim rec As New ADODB.Recordset, xlMemo As String
xlMemo = ThisWorkbook.Worksheets(1).Range("a1").Value
con.Open "Provider=Microsoft.ACE.OLEDB.15.0;Data Source=C:¥Temp¥Northwind.accdb;Mode=ReadWrite;"
rec.Open "T", con, adOpenDynamic, adLockOptimistic
rec.AddNew
rec("memo") = Replace(xlMemo, vbLf, vbCrLf)
rec.Update
End Sub
この記事のまとめ
ついつい忘れがちになることなので覚書きしておきました。
他のアプリケーションとデータをやり取りする際には、文字コード、改行コードについては必ず注意が必要となります。
基本的には相手の使用文字コード(SJISやUTF8など)、改行などのルールに合わせて、事前に変換してデータを送る必要があります。
しかしExcelとAccessは同じOfficeファミリーなのですからここらへんは統一しておいて欲しいものです。
この記事自体はかなり昔書いた記事で、最近コメントが付いて内容を見たらあまりきちんと書かれていなかったので記事を改版しました。
コメント
参考になりました。
Access のフィールド内改行は、Excell のセル内改行と同様、vbLf だと早合点していました。先ほど、いろいろ試して vbCrLf であることに気づき、御記事で確認できました。有難うございました。
1年後にはまた忘れてそう。
コメントありがとうございます。
私もついつい忘れるので覚書きしました。