適材適所

PowerShellを中心にプログラミングやシステム管理の備忘録的なブログ

Excelシートの禁則文字入りAccessテーブルをExcelにエクスポートするとExcelブックが壊れる件について

はじめに

「AccessからエクスポートしたExcelブックが壊れて開かない!」という問い合わせを受け、調べたときのまとめです。

環境

  • Access 2019
  • Excel 2019 ※どちらもデスクトップ版

Accessテーブル名の仕様

Accessのテーブル名の仕様は次のようになっているそうです。

  • 64 文字まで使用できます。
  • 文字、数字、スペース、ピリオド (.)、感嘆符 (!)、アクセント記号 (')、および角カッコ ([]) を除く特殊文字を任意に組み合わせることができます。 ↑上記に記載されていたアクセント記号(')をテーブル名に付けてもエラー起こらず。エラーが起こるのは(`)の記号。シフト+@で出てくる記号。 ↑また、難しい日本語になっているが、組み合わせ可能なのは、文字+数字+スペース+特殊文字(ピリオド、感嘆符、アクセント記号、角カッコを除く)という意味っぽい。

  • 先頭にスペースは使用できません。

  • 制御文字 (ASCII 値 0 ~ 31) を挿入することはできません。
  • Microsoft Access プロジェクトのテーブル、ビュー、または ストアド プロシージャの名前に二重引用符 (") は使用できません。

フィールド、コントロール、オブジェクトに名前を付けるためのガイドライン

Excelシート名の仕様

Excelのシート名の仕様は次のようになっているっぽいです。

  • 31文字まで使用可能(実際に入力できた文字数)

  • 次の使用できない文字が含まれていないこと: コロン (:)、円記号 ()、スラッシュ (/)、疑問符 (?)、アスタリスク (*)、左角かっこ ([)、右角かっこ (]) Excelのヘルプより

実験

それぞれの仕様がわかったところで、それぞれの禁則文字の検証と、禁則文字入りテーブルをExcelにエクスポートしたらどうなるか、検証をしてみましょう。

Excelの禁則文字はこれであっている?

念のため、試してみましょう。

Excel VBAで次のようなチェックプログラムを作ってみました。

Asciiコードの記号・英数字をシート名に設定してみて、

エラーとなったものをイミディエイトウィンドウに表示します。

 
Sub sheetNameErroTest()
    'asciiの32~126を調査する(spaceから~まで)
    On Error Resume Next
    Dim i As Long
    For i = 32 To 126
        Err.Clear
        Sheet1.Name = Chr(i)
        If Err.Number <> 0 Then
            Debug.Print "10進 → " & i & " ascii → " & Chr(i)
        End If
    Next i
    On Error GoTo 0
End Sub

結果は次のとおり

10進 → 39 ascii → '
10進 → 42 ascii → *
10進 → 47 ascii → /
10進 → 58 ascii → :
10進 → 63 ascii → ?
10進 → 91 ascii → [
10進 → 92 ascii → \
10進 → 93 ascii → ]

ヘルプにはありませんが、シングルクォーテーションがエラーになりました。

どうやらシングルクォーテーションを先頭か末尾につけるとエラーになるようです。

中間であれば問題なく使えました。

Accessの禁則文字はこれであっている?

念のため、Accessでも同様に試してみましょう。

デフォルトのテーブル1があるものとします。

 
Option Compare Database
Sub tableNameErroTest()
    'asciiの32~126を調査する(spaceから~まで)
    On Error Resume Next
    Dim i As Long
    Dim orgName As String
    Dim newName As String
    Dim oldName As String
    orgName = "テーブル1"
    oldName = orgName
    For i = 32 To 126
        newName = Chr(i)
        Err.Clear
        DoCmd.Rename newName, acTable, oldName
        If Err.Number <> 0 Then
            Debug.Print "10進 → " & i & " ascii → " & Chr(i)
        Else
            oldName = newName
        End If
    Next i
    DoCmd.Rename orgName, acTable, oldName
    On Error GoTo 0
End Sub

結果は次のとおり。

10進 → 32 ascii →  
10進 → 33 ascii → !
10進 → 46 ascii → .
10進 → 61 ascii → =
10進 → 63 ascii → ?
10進 → 91 ascii → [
10進 → 93 ascii → ]
10進 → 96 ascii → `

スペースは先頭に使えないとのことでその通りでした。

他にも、=と?についても先頭で使えないようです。

中間だと問題なく使用できました。

また、二重引用符(ダブルクォーテーション ")については問題なく使用することができてしまいました。

もしかして、使えるけれども、バグの温床になるという爆弾的な扱いなのでしょうか。

Accessテーブル名とExcelシート名の禁則文字の比較

以上の結果からAccessテーブルとExcelシートの禁則文字の比較は次のようになりました。

記号 Excel Access
スペース △ ※先頭のみ×
感嘆符! ×
シングルクォーテーション' △ ※先頭と末尾は×
アスタリスク* ×
ピリオド. ×
円記号\ ×
コロン: ×
疑問符? × △ ※先頭のみ×
角カッコ[] × ×
スラッシュ/ ×
アクセント記号` ×
二重引用符"

Excelシートの禁則文字入りAccessテーブルをExcelにエクスポートすると?

本題からだいぶ逸れてしまいましたが、やっと本題。

Excelシートの禁則文字が入ったAccessテーブルをExcel形式でエクスポートするとどうなるのでしょうか。

Excelシートの禁則文字でAccessテーブルで使用可能な文字は次の6つの文字。

  • 先頭か末尾の「'」(シングルクォーテーション)
  • 「*」(アスタリスク)
  • 「\」(円記号)
  • 「:」(コロン)
  • 「?」(疑問符)
  • 「/」(スラッシュ)

以下各文字の名前のAccessテーブルをExcelにエクスポートしてみます。

ブック名は指定せず、デフォルトの設定のままエクスポートをしてみました。

テーブル名「'」をエクスポートした結果

ブック名「'.xlsx」でエクスポートできましたが、開くと

''.xlsx'の一部の内容に問題が見つかりました。可能な限り内容を回復しますか?(以下省略)

のエラーが出ました。

テーブル名「*」をエクスポートした結果

ブック名は「_.xlsx」に置き換えられました。

しかし、ブックを開くと、

'_.xlsx'の一部の内容に問題が見つかりました。可能な限り内容を回復しますか?(以下省略)

のエラーが出ました。

ブック名は置換するのに、どうやらシート名は置き換えてくれないようです。

テーブル名「\」をエクスポートした結果

*のときと全く同じでした。

テーブル名「:」をエクスポートした結果

*のときと全く同じでした。

テーブル名「a?」をエクスポートした結果

*のときと全く(ry

テーブル名「/」をエクスポートした結果

*のと(ry

結果

というわけで、Excelシートの禁則文字を使ったAccessテーブルをExcel形式でエクスポートすると、

エクスポート操作はできてしまいますが、ブックに問題が見つかり、

異常な状態のExcelブックができてしまうようです。

www.tekizai.net

終わりに

今回検証したのはどちらもバージョン2019だったのですが、

どちらもMicrosoft製品ということもあり、

将来先改善され、さらにいいアプリになってくれるといいですね。

というわけでここまでお付き合いいただきありがとうございました。

`; Array.prototype.forEach.call(document.getElementsByClassName('kijinai_koukoku'),function(d){d.innerHTML=KIJINAI_ADS;}); Array.prototype.forEach.call(document.getElementsByClassName('adsbygoogle'), function(){ (adsbygoogle = window.adsbygoogle || []).push({});}); -->