VBA:改行コードの取り扱いに注意が必要！ExcelとAccessでは改行コードが違います！

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ファミリーなのですからここらへんは統一しておいて欲しいものです。

この記事自体はかなり昔書いた記事で、最近コメントが付いて内容を見たらあまりきちんと書かれていなかったので記事を改版しました。