【VBA】BOM付きUTF-8のCSV書き出しと、URLパスの検知

BOM付きUTF-8のCSV書き出しとURLパスの検知と書かれたアイキャッチ。ADODB.StreamのCharset指定のコードが映っている VBA

OneDriveの同期フォルダに置いたブックで、ブックと同じ場所にCSVを書き出すマクロを確かめたところ、書き出しの行でエラーになりました。この環境では、ThisWorkbook.Pathがhttps://で始まるURLを返していたためです。

書き出すCSVは、Excelで開いても文字化けしないBOM付きUTF-8にしたいところです。読み込む側の設定は、【Power Query】CSVの文字化けをEncoding指定で防ぐ方法に書きました。この記事は書き出す側です。BOM付きUTF-8の書き出し方と、URLパスのときに書き出す前に止める検知を、実機で確かめた結果と一緒にまとめます。

BOM付きUTF-8で書き出すコード

Option Explicit

Private Const adTypeText As Long = 2
Private Const adSaveCreateOverWrite As Long = 2

' パスがURL（https:// など）かどうか
Public Function IsUrlPath(ByVal p As String) As Boolean
    IsUrlPath = (InStr(p, "://") > 0)
End Function

' BOM付きUTF-8でCSVを書き出す
Public Sub WriteCsvUtf8Bom(ByVal filePath As String, ByVal csvText As String)
    Dim st As Object
    Set st = CreateObject("ADODB.Stream")
    st.Type = adTypeText
    st.Charset = "UTF-8"          ' 最初のWriteTextより前に指定する
    st.Open
    st.WriteText csvText
    st.SaveToFile filePath, adSaveCreateOverWrite
    st.Close
End Sub

Public Sub ExportCsv()
    If IsUrlPath(ThisWorkbook.Path) Then
        MsgBox "ブックの場所がURLのため、書き出せません。", vbExclamation
        Exit Sub
    End If
    WriteCsvUtf8Bom ThisWorkbook.Path & "\sample.csv", "A,B" & vbCrLf & "1,2"
End Sub

ポイントは2つです。Charsetを"UTF-8"にして最初のWriteTextより前に指定すること。SaveToFileにadSaveCreateOverWriteを付けること。以下で、それぞれ実測した結果を示します。

BOMが付く条件

  • Charset = "UTF-8"を最初のWriteTextより前に指定すると、先頭にEF BB BFの3バイトが付きます。指定する位置はOpenの前でも後でも同じでした。WriteTextを2回呼んでも、BOMは先頭に1回だけです。
  • Charsetを指定しないと、既定はUnicodeで、UTF-16LEのBOM（FF FE）が付きます。
  • WriteTextのあとでCharsetを変えようとすると、実行時エラー3219「このコンテキストで操作は許可されていません。」になります。そのまま保存すると、UTF-16LEのファイルになりました。
  • 空文字をWriteTextして保存すると、BOMの3バイトだけのファイルになります。WriteTextを一度も呼ばずに保存すると、0バイトのファイルです。
  • Typeを省略しても、この環境ではテキストとして書けました（Typeの既定値は2、adTypeTextでした）。

Charsetの位置については、Microsoft LearnのCharsetプロパティにも、開いているStreamではPositionが先頭（0）のときに設定する、既定値はUnicode、という説明があります。実測の結果と一致していました。

イミディエイトウィンドウに、UTF-8・Charset未指定・Shift_JIS・BOMなしの4通りのCSVのバイト列と、IsUrlPathの判定結果が表示されている
再現用の最小コードの実行結果。「あ,B」を4通りで書き出したバイト列

日本語の「あ」は、UTF-8ではE3 81 82の3バイト、Shift_JISでは82 A0の2バイトで、Shift_JISにBOMは付きません。U+20BB7の文字（サロゲートペア）は、UTF-8で4バイト（F0 A0 AE B7）になりました。Shift_JISでバイト数を数えるときの注意は、【VBA】全角氏名のバイト数チェックはLenBとStrConvで行うにまとめています。

このファイルをExcelで開くと、BOMありのファイルは「あい」のまま開きました。BOMなしのファイルは、同じバイト列が別の3文字に化けました。

SaveToFileのオプションは省略しない

SaveToFileのオプションを省略して、すでにあるファイルに保存すると、実行時エラー3004「ファイルへ書き込めませんでした。」になります。マクロを2回目に実行したときに止まるのは、これが原因です。adSaveCreateOverWrite（2）を指定すると、上書きできました。

BOMなしにしたいとき

BOMなしのUTF-8が必要なときは、いったんBOM付きで書き出してから、バイナリとして先頭の3バイトを除いて保存します。実測では、E3 81 82 2C 42になりました。BOMなしのファイルを読む側で判定する方法は、【Power Query】CSVの文字コードを置換文字で判定するに書きました。

Private Const adTypeBinary As Long = 1

Private Sub StripBom(ByVal src As String, ByVal dst As String)
    Dim a As Object, b As Object
    Set a = CreateObject("ADODB.Stream")
    a.Type = adTypeBinary
    a.Open
    a.LoadFromFile src
    a.Position = 3                ' 先頭の3バイト（BOM）を飛ばす
    Set b = CreateObject("ADODB.Stream")
    b.Type = adTypeBinary
    b.Open
    a.CopyTo b
    b.SaveToFile dst, adSaveCreateOverWrite
    a.Close
    b.Close
End Sub

URLのときは、書き出す前に止める

実務のマクロには、パスがURLのときに処理を止める検知を入れています。今回、手元の環境で確かめた結果は次のとおりです。

  • 個人のOneDriveの同期フォルダに置いたブックでは、ThisWorkbook.Pathがhttps://で始まるURL（ホストはlive.net）でした。Pathに"\"とファイル名をつないでSaveToFileすると、エラー3004になりました。
  • 同期していないローカルフォルダのブックでは、Pathは通常のパスで、書き出しは成功しました。
  • 同じ環境で、環境変数OneDriveが指す同期フォルダ（ローカルパス）には、書き出せました。
  • SaveToFileにダミーのURL（https://example.invalid/…）を渡しても、同じエラー3004になりました。
  • このExcelにはAutoSaveのスイッチが表示されず、AutoSaveOnはFalseのままでしたが、それでもPathはURLを返しました。

エラー3004は、すでにあるファイルに上書きできないときと同じ番号・同じ文言です。エラーだけでは、URLだから書けないのか、別の原因なのかが分かりません。フォルダの確認でも区別できませんでした。FileSystemObject.FolderExistsはFalse、Dirは実行時エラー52になりました。そのため、書き出す前に、パスの文字列で判定して止めます。

判定は、Left(path, 4) = "http"でも動きますが、httpdata\xのような相対パスもURLと判定してしまいます。InStr(path, "://") > 0なら、この例を除けました。

パスの文字列Left(path, 4) = “http”InStr(path, “://”) > 0
https://example.invalid/aTrueTrue
HTTPS://EXAMPLE.INVALID/a（大文字）TrueTrue
httpdata\x（相対パス）TrueFalse

なお、InStr(path, "://")は、C:\work・\\srv\share\x・D:\http_logs・空文字でもFalseでした。Leftを使う場合は、LCaseで小文字にそろえて比べました。

今回確認した環境と、確認できていないこと

確認したのは、Excel 16.0.19127（Windows版）、日本語環境（コードページ932）、個人のOneDriveの同期フォルダに置いたブックです。次は確認できていません。

  • ブラウザやTeams経由で開いた、SharePoint上のブックのPath（ホストがsharepoint.comになる場合）
  • AutoSaveのスイッチが使えるExcelでのPathの値
  • Mac、他のバージョンのExcel
  • EUC-JPやUTF-16での書き出し

まとめ

  • BOM付きUTF-8は、Charset = "UTF-8"を最初のWriteTextより前に指定する。Charsetを指定しないとUTF-16LEになる
  • SaveToFileにはadSaveCreateOverWriteを付ける。省略すると、2回目の実行でエラー3004になる
  • OneDriveの同期フォルダのブックは、ThisWorkbook.PathがURLになることがある。書き出す前にInStr(path, "://") > 0で検知して止める

コメント

タイトルとURLをコピーしました