概要

Excel からのリンクテーブルを作成した際、各列に適用されるデータ型は Jet プロバイダによって自動で決定されます。

このため、データ型が混在する Excel ワークシートからリンクテーブルを作成すると、ユーザーの希望と反するデータ型になったり、「#Num!」のエラー表示になってしまうことがあります。

今回は、Excel からのリンクテーブルとデータ型の関係を整理し、意図通りのリンクテーブルを作成するコツについてまとめてみることにしましょう。

※ Access 2000 での動作確認を基にしています。Access 97 以前でも同様と思われますが、未確認のため対象バージョンから外しています。

※ 断定的に記述している箇所が有りますが、YU-TANG は特に Excel に詳しいわけではなく、動作確認からの推測に基づく仮定が含まれています。誤認・誤謬が有りましたら、ご指摘いただければ幸いです。

※ Access 2003 では、Office 2003 Service Pack 2 を適用すると、Excel からのリンクテーブルに対する追加・更新・削除が出来なくなります。SP2 適用前に、影響範囲について十分な調査・検討を行うことをお奨めします。

Excel のデータ型

まず、Excel のデータ型について基本を確認したいと思います。

Excel は MS-Access と違って、どのセルに何のデータでも入れられるため、そこにデータ型が存在するという感覚が希薄だと思います。人によってはデータ型が無いと思っている方もいらっしゃるかもしれませんが、実際には Excel にもちゃんとデータ型が有ります。しかしデータ型が有ると認識している場合でも、Excel にはセルの書式設定が有って、その表示形式で「文字列」や「数値」を指定できるため、これをデータ型と混同して同一視しているケースが見られます。

実際には Excel のデータ型と書式設定は別物であり、この区別があいまいなことが、Excel からのリンクテーブル作成にまつわるデータ型問題の混乱に拍車をかけている感が拭いきれません。

そこで、この章では YU-TANG が知りうる範囲で、Excel のデータ型概念を明確にしていきたいと思います。

まず簡単な実験をしてみましょう。

Excel 2000 で、次のような表を作成してみます。

 ABCD
1データデータ入力前
の表示形式
データ入力後
の表示形式
データ型
2 標準標準=CHOOSE(TYPE(A2),"数値","テキスト")
3 標準文字列=CHOOSE(TYPE(A3),"数値","テキスト")
4 文字列文字列=CHOOSE(TYPE(A4),"数値","テキスト")

A4 のセルのみ、事前に「文字列」の表示形式を設定しています。

また D 列には数式を設定して、A 列のデータ型を表示させるようにします。

表では分かりやすいように、オプションを一時的に変更して、数式自体を表示させるようにしています。

数式では「数値」と「テキスト」の 2 種類しか判別していませんが、日付型、通貨型、論理値などは大まかな区分としては数値に分類されるため、基本的なデータ型はこの 2 種類でまかなうことが出来ます。

では、オプションを標準(数式ではなく値を表示)に戻して、A 列に数字の「1」を入力していきます。

結果は以下の表の通りになりました。

 ABCD
1データデータ入力前
の表示形式
データ入力後
の表示形式
データ型
21標準標準数値
31標準文字列数値
41文字列文字列テキスト

表示形式が「標準」のセル A2、A3 のデータ型は、D2、D3 を見れば明らかなように数値型です。

一方、表示形式が「文字列」のセル A4 のデータ型は、D4 を見るとテキスト型になっています。

ここまでは当たり前の結果に思えるかもしれません。

では、今度はセル A3 の表示形式を「文字列」に変更してみます。

[セルの書式設定] ダイアログ - 表示形式

結果は以下の通りです。

 ABCD
1データデータ入力前
の表示形式
データ入力後
の表示形式
データ型
21標準標準数値
31標準文字列数値
41文字列文字列テキスト

ポイントとなるセルは背景色を黄色で着色してあります。

セル A3 を見ると、表示形式はたしかに「文字列」に変わっています。

セル A3 と A4 はまったく同じであるかのように見えるかもしれません。

しかしセル D3 を見てください。データ型は「数値」のままであることが確認できます。

すなわちセル A3 は数値型データを文字列の表示形式で表示しているに過ぎず、データ型は依然として数値型のままなのです。

セルの書式設定で後から変更した表示形式と、データ型とは、何の関係も無いことが、これで明らかでしょう。

今度はセル A3 を選択して【F2】キーを押下し、いったん編集状態にしてから【Enter】キーで確定し直します。

直後の状態が以下の表です。

 ABCD
1データデータ入力前
の表示形式
データ入力後
の表示形式
データ型
21標準標準数値
31標準文字列テキスト
41文字列文字列テキスト

セル A3 の見た目には何の変化もありませんが、セル D3 を見ると、ここで初めてデータ型がテキスト型に変化したことを確認できます。

このことから、Excel のデータ型は確定時に決定されることが分かります。

では本章の最後に、Excel のデータ型の特徴について整理してみましょう。

リンク時のデータ型

前章で Excel のデータ型について明らかにしました。

今度は、MS-Access がリンクテーブルを作成する際に、どのように各列のデータ型を決定しているのかを見ていくことにしましょう。

Excel は各セルごとに異なるデータ型を保持できますが、ご存知のように MS-Access は列(フィールド)単位でデータ型が決まります。そのため、Excel の各列に対してどのデータ型を割り当てるのが適切か決定するため、Jet プロバイダは事前に Excel のデータをスキャンします。

このときスキャンする行数は、レジストリの以下のエントリによって規定されています。

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows

上記は Access 2000 の場合であるため、Access 97 では Jet のバージョン部分が 4.0 ではなく 3.5 になります。

私が調べた限りでは、Access 97 での既定値は 8、Access 2000 での既定値は 25 のようです。

ただし端末によっては、ユーザーの恣意的な変更や MDAC または各種 SR/SP によって変更されている可能性が有り、常に上記の値が保証されているわけではありません。

またスキャンする行数には、タイトル行は含まれません。

もしデータソース(ワークシート全体あるいは名前付き範囲/名前無し範囲)の 1 行目がタイトル行に指定されていれば、2 行目から TypeGuessRows の行数分がスキャン対象となります。

Jet プロバイダはスキャンした結果、各列について最も多かったデータ型をリンクテーブルのデータ型として採用します。

このロジックはインポート時とは異なるので、注意してください。

インポート時は、スキャン行数内でデータ型が混在していれば、通常はテキスト型に統一されます。したがって、スキャン行数内の数値データの中に 1 行でも文字列データが混じっていれば、その列はテキスト型になります。

一方リンク時は、スキャン行数内で最も多かったデータ型が採用されます。もしスキャン行数内の数値データと文字列データが同数で有れば、その列のデータ型は数値型(倍精度浮動小数点型)としてリンクされます。したがって、スキャン行数内の数値データの中に 1 行だけ文字列データが混じっていても、その列は(TypeGuessRows が 1 でもない限り)テキスト型にはなりません。

ではここで、実際にいろいろなデータを Excel ワークシートに入力し、それをリンクした時にどのような状態になるか確認をしてみましょう。

まず Excel 2000 で、下表のようなワークシートを用意します。

【Sheet1 - Excel ワークシート】
 ABCDE
1テキスト型数値型日付型通貨型論理型
2あいうえお992 2004/10/15\1,234TRUE
3かきくけこ993 2004/10/16\2,345FALSE
4さしすせそ994 2004/10/17\3,456TRUE
5たちつてと995 2004/10/18\4,567FALSE
6なにぬねの996 2004/10/19\5,678TRUE
7はひふへほ997 2004/10/20\6,789FALSE
8まみむめも998 2004/10/21\7,890TRUE
9     
10らりるれろらりるれろらりるれろらりるれろらりるれろ
11999 999 999 999 999 
122004/10/222004/10/222004/10/222004/10/222004/10/22
13\8,901\8,901\8,901\8,901\8,901
14TRUETRUETRUETRUETRUE

ご覧の通り、タイトル行がデータ型を表しており、2 〜 8 行目までの最初の 7 行については、タイトル通りのデータが入力されています。

日付型、通貨型、論理型は、実質的には数値型の亜種のようなものですが、リンク時には表示形式との兼ね合いで異なるデータ型として取り扱われます。

この表は全体で 14 行しかなく、タイトル行を除けばスキャン対象は 13 行です。その過半数に当たる最初の 7 行が同一データ型なので、MS-Access へのリンク時に使用される各列のデータ型は、これで決定することになります(7 行で決定するのは、このサンプル表のスキャン対象がたまたま 13 行しかないから、過半数が 7 行になっているだけです。常に 7 行で決まるわけではありません。スキャン行数はあくまで、前述の通り TypeGuessRows の値によって決まります)。

9 行目には、空白セルを混ぜています。そして 10 行目以降には、それまでと異なるデータ型を意図的に混在させました。

この表を Access 2000 へリンクした状態が、下の表です。

【Sheet1 - Access 2000 リンクテーブル】
 テキスト型数値型日付型通貨型論理型
 あいうえお9922004/10/15\1,234True
 かきくけこ9932004/10/16\2,345False
 さしすせそ9942004/10/17\3,456True
 たちつてと9952004/10/18\4,567False
 なにぬねの9962004/10/19\5,678True
 はひふへほ9972004/10/20\6,789False
 まみむめも9982004/10/21\7,890True
      
 らりるれろ#Num!#Num!#Num!#Num!
 #Num!9991902/09/25\999True
 #Num!382822004/10/22\38,282True
 #Num!89011924/05/14\8,901True
 #Num!#Num!#Num!#Num!True

空白行(Null)までは問題なくデータが表示されていますが、その下からはデータによってまちまちの結果になっています。

この結果を踏まえて、分かりやすいように星取表形式の表にまとめてみましょう。

【リンク時の変換対応表】フィールドのデータ型
テキスト型数値型日付/時刻型通貨型Yes/No型
Excel 側の
データ型
テキスト型正常#Num!#Num!#Num!#Num!
数値型#Num!正常日付に変換通貨に変換論理値に変換
日付型#Num!数値に変換正常通貨に変換論理値に変換
通貨型#Num!数値に変換日付に変換正常論理値に変換
論理型#Num!#Num!#Num!#Num!正常

全体的に、テキスト型と論理型の、他のデータ型との相性の悪さが目に付きます。型が違うとエラーになってしまいます。

また数値型、日付型、通貨型は、相互間でエラーにはならずに表示はされるものの、表示形式は変わってしまうことが分かります。

トラブルの再現

Excel のデータ型と MS-Access の変換法則を把握したところで、さらに理解を深めるために、ここで Excel からのリンクテーブル作成時に遭遇するトラブルの実例を見ていくことにしましょう。

以下のような Excel ワークシートが有るとします。

【商品 - Excel ワークシート】
 ABCD
1商品コード仕入先コード商品名単価
2123001 B003アブ-クラッシャー\10,980
3004593 2874 マシュマロ マット\7,800
4368854 C417マイナスイオン煙草\350
576A48B9002 電子辞書どーでも英和\12,800
61A26CZ433 次世代携帯カイロ\100

この Excel ブックは過去の経緯により社内で共有されており、各部署より随時更新されているため MS-Access にインポートできません。しかし集計作業は MS-Access で行なうことになったため、リンクテーブルとして閲覧することになりました。

しかし実際にリンクテーブルを作成すると、問題が発生しました。

【商品 - Access 2000 リンクテーブル】
 商品コード仕入先コード商品名単価
 #Num!#Num!アブ-クラッシャー\10,980
 0045932874マシュマロ マット\7,800
 #Num!#Num!マイナスイオン煙草\350
 76A48B9002電子辞書どーでも英和\12,800
 1A26CZ433次世代携帯カイロ\100

1 行目と 3 行目の [商品コード] と [仕入先コード] がエラーになってしまっています。

確認した結果、Excel ワークシート側は A 列も B 列も [標準] 書式になっており、MS-Access のリンクテーブル側は [商品コード] フィールドが「テキスト型」に、[仕入先コード] フィールドが「数値型」に、なっていることが判明。データベースの担当者は、Excel ワークシート側において数値と文字列が混在していることが原因と判断しました。

今回 Excel ワークシート側の書式が [標準] になっていたのは、単に初期設定を変更しなかっただけであって、[標準] である必然性が有るわけではありません。

そこで担当者は、Excel ワークシート側の A 列と B 列の書式を [標準] から [文字列] に変更することにしました。

【商品 - Excel ワークシート(書式変更後)】
 ABCD
1商品コード仕入先コード商品名単価
2123001B003アブ-クラッシャー\10,980
30045932874マシュマロ マット\7,800
4368854C417マイナスイオン煙草\350
576A48B9002電子辞書どーでも英和\12,800
61A26CZ433次世代携帯カイロ\100

書式変更後、A 列と B 列は左揃えになり、文字列の表示形式になりました。

担当者はこれによって、A 列と B 列はともに文字列で統一されたものと誤解します。

ここまでをお読みの方はお分かりの通り、正確には文字列の表示形式で統一されただけであって、依然としてデータ型は数値型とテキスト型とが混在している状態です。

その後、担当者はリンクテーブルを再作成しますが、エラーが表示される状態は何も変わりませんでした。

その結果、担当者は次のような結論に至ります。

Excel からのリンクテーブル作成時のフィールドのデータ型は、Excel 側の書式には依存しない。あくまでデータのみを見ているので、Excel ワークシート側をどう変えても、制御できない。

上記結論の前半は、半分だけ合っていて、半分は間違っています。

広義の数値型とテキスト型との区別に関しては、たしかに表示書式は無関係です。Excel 側のデータ型に依存しますが、この例での担当者には、Excel にデータ型が存在するという認識が欠落しているため、気付いていません。また広義の数値型の中で、狭義の数値型と日付型、通貨型等の区別は表示書式に依存しています。

さらに、結論の後半はまったく間違っています。

Excel ワークシート側で適切なデータ型に変換できれば、リンクテーブル作成時のフィールドのデータ型はあるていど制御できます。

では、このようなケースで有効な回避策とは、どのようなものなのでしょうか。

次章で具体的に見ていくことにしましょう。

回避策

回避策は一種類ではありません。

状況によって、いくつかの選択肢があります。以下に概要を列挙してみます。

Excel ワークシート側での対処

データ型を直接変換する

データ型を数式によって間接的に変換する

MS-Access 側での対処

IMEX を変更する

データ型を直接変換する

Excel ワークシート側での対処です。

これは Excel ワークシート側でデータ型が意図せずに混在しており、本来であれば統一したい(あるいは統一して構わない)場合に使用する、最も確実な対処策です。

たとえば以下のようなケースが該当するでしょう。

以下の手順で操作します。

  1. 対象の Excel ブックを Excel で開きます。

  2. データ型を統一したい列を、列セレクタ(列番号が表示されています)をクリックして、1 列だけ選択します。

  3. [データ] メニューから [区切り位置] コマンドを選択します。

  4. [区切り位置指定ウィザード - 1/3] ダイアログの [元のデータの形式] で、「カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ(D)」が選択されていることを確認し、[次へ >] ボタンを押下します。

    元のデータの形式

    データのファイル形式を選択してください。

    カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ(D)

    スペースによって右または左に揃えられた固定長フィールドのデータ(W)

  5. [区切り位置指定ウィザード - 2/3] ダイアログの [区切り文字] で、データに使用されていない文字が選択されていることを確認し、[次へ >] ボタンを押下します。

    通常は既定の「タブ(T)」で問題ないと思いますが、入力データがタブを含んでいるときは別の区切り文字を選択してください。

  6. [区切り位置指定ウィザード - 3/3] ダイアログの [列のデータ形式] で、統一したいデータ形式を選択して [完了(F)] ボタンを押下します。

    通常、テキスト型で統一したい場合は「文字列(T)」を、数値で統一したい場合は「G/標準(G)」を選択します。

    また [完了(F)] ボタン押下時に "コピーまたは移動先のセルの内容を置き換えますか?" という注意ダイアログが表示される場合は、手順 5 での区切り文字の指定が誤っていたことを示しています。 [キャンセル] ボタンを押下して、正しい(データ中で使用されていない)区切り文字を指定し直してください。

  7. データ型を統一したい列が他にも有れば、その列に対して 2 〜 6 の手順を繰り返します。

  8. Excel ブックを上書き保存して Excel を終了し、MS-Access 側でリンクテーブルを作成します。

    既に作成済みのリンクテーブルに対して設定の変更が即座に反映されないか、あるいは表示が不正になる場合は、リンクテーブルを再作成するか、あるいはリンクテーブル マネージャで更新します。

区切り位置指定ウィザードは複数列同時に適用することができないので、1 列ずつ操作する必要が有る点に留意してください。

この操作によって、選択範囲が再入力・再確定されたのと同じ状態になり、データ型が更新されます。

前章のトラブルの再現で取り上げた例に当てはめるのであれば、あらかじめ Excel ワークシート側で A 列(商品コード)と B 列(仕入先コード)を、区切り位置指定ウィザードを使って「文字列(T)」で更新した後に、MS-Access 側でリンクテーブルを作成すると、下図のように問題なく全てのデータが表示されます。

【商品 - Access 2000 リンクテーブル】
 商品コード仕入先コード商品名単価
 123001B003アブ-クラッシャー\10,980
 0045932874マシュマロ マット\7,800
 368854C417マイナスイオン煙草\350
 76A48B9002電子辞書どーでも英和\12,800
 1A26CZ433次世代携帯カイロ\100

なお、Web ページの表などから数値データを Excel に貼り付けると、しばしばデータの末尾に非表示の文字が混じる場合があります。

このような文字が混じっていると、区切り位置指定ウィザード - 3/3 で「G/標準(G)」を選択しても、数値型に変換されません。

たとえばレイアウトの都合で数値データの前後に   という HTML キャラクタを付けた Web ページの表をコピーして Excel に貼り付けると、文字コード 0xA0 のスペース文字がデータに混入します。人間が目で見ても何も無いように見える上に、通常のスペース文字(0x20)なら取り除けるはずの CLEAN 関数や TRIM 関数でも取り除けません。

この場合は、置換機能などで非表示文字を一括削除してから、区切り位置指定ウィザードを実行する必要が有りますので、ご注意ください。

データ型を数式によって間接的に変換する

引き続き、Excel ワークシート側での対処です。

これは Excel ワークシート側でデータ型を意図的に混在させていて、何らかの理由でデータ型を統一することができない場合に使用します。

数式を使用したリンク専用の作業列を設ける方法です。

やはり前章のトラブルの再現で取り上げた例を使って説明します。

まず全てテキスト型に変換するという前提です。

  1. 対象の Excel ブックを Excel で開きます。

  2. データ型を統一したい列数と同じだけの作業列を挿入します。

    下図の例では、A 列と B 列の 2 列が対象になるので、その右側に 2 列挿入しています。ただし挿入位置はどこでも構いません。例ではたまたま対象列の右側に挿入していますが、末尾列でも構いません。

    【商品 - Excel ワークシート】
     ABCDEF
    1商品コード仕入先コード  商品名単価
    2123001 B003  アブ-クラッシャー\10,980
    3004593 2874   マシュマロ マット\7,800
    4368854 C417  マイナスイオン煙草\350
    576A48B9002   電子辞書どーでも英和\12,800
    61A26CZ433   次世代携帯カイロ\100
  3. 挿入列の 1 行目にリンク用のタイトルを設定します。

    これは元の列と同じでも別のタイトルでも構いません。下図の例では同じタイトルにしています。

    【商品 - Excel ワークシート】
     ABCDEF
    1商品コード仕入先コード商品コード仕入先コード商品名単価
    2123001 B003  アブ-クラッシャー\10,980
  4. セル C2 に、以下のような数式を設定します。

    =IF(ISTEXT(A2),A2,TEXT(A2,"#"))

    続いてセル C2 をオートフィルでセル D2 にコピーし、フィルハンドルをダブルクリックして D6 までコピーします。

    上記操作によって C 列と D 列が数式で埋められた結果が下図です。

    【商品 - Excel ワークシート】
     ABCDEF
    1商品コード仕入先コード商品コード仕入先コード商品名単価
    2123001 B003123001B003アブ-クラッシャー\10,980
    30045932874 0045932874マシュマロ マット\7,800
    4368854 C417368854C417マイナスイオン煙草\350
    576A48B9002 76A48B9002電子辞書どーでも英和\12,800
    61A26CZ433 1A26CZ433次世代携帯カイロ\100
  5. リンク用の名前付き範囲を設定します。

    例では、A 列と B 列はリンク後は使用しないため、C1 から F6 までの範囲を選択して [挿入]-[名前]-[定義] から、任意の名前を付けて追加します。

    下図の例では、「リンク用」という名前を付けています。

    [名前の定義] ダイアログ

    Excel の操作に慣れている場合は、[名前] ボックス(通常は数式バーの左側に表示されています)から追加しても構いません。

    またリンク用の作業列を Excel 側で表示したくない場合は、この時点で C 列と D 列を選択して非表示([書式]-[列]-[表示しない] あるいは選択範囲上で右クリックから [表示しない])にしておきます。

  6. Excel ブックを上書き保存し、Excel を終了します。

  7. MS-Access 側でリンクテーブルを作成します。

    このとき、ワークシート リンク ウィザードの最初の画面で [名前の付いた範囲] を選び、手順 5 で作成した名前付き範囲を選択して、ウィザードを完了まで進めてください。

    [ワークシート リンク ウィザード] ダイアログ

  8. 作成したリンクテーブルをデータシートビューで開くと、下図のように全データが正常に表示されます。

    【商品 - Access 2000 リンクテーブル】
     商品コード仕入先コード商品名単価
     123001B003アブ-クラッシャー\10,980
     0045932874マシュマロ マット\7,800
     368854C417マイナスイオン煙草\350
     76A48B9002電子辞書どーでも英和\12,800
     1A26CZ433次世代携帯カイロ\100

この数式を使用した方法には、次のような制限があります。

この手法は非常に制約が多いため、限定的な使用に留め、出来ればリンクテーブル側では参照のみで取り扱う方が望ましいと言えます。

また、上記はテキスト型で統一したい場合の手順ですが、逆に何らかの理由でテキスト型で入力されている数字を数値型に統一したい場合は、手順 4 の数式を以下のように変更します。

=VALUE(A2)

なお、上記数式の参照先セルが空白の場合は 0 が、数値変換できない文字列だった場合はエラーが表示されます。

IMEX を変更する

MS-Access 側(というか、Jet 側)の対応です。

Excel ISAM ドライバで接続する際、IMEX オプションを指定することが出来ます。

# IMEX が何の略称なのかは知りません。

※ こま さんより、IMport/EXport の略ではないか、というご指摘をいただきました。公式資料は未確認ですが、非常に説得力があるので追記させていただきます。こま さん、有難うございました。(2004/10/28 by YU-TANG)

IMEX オプションには次の 3 種類が有ります。

0 - Export mode

1 - Import mode

2 - Linked mode

リンク時の既定は 2 です。この場合はリンク時のデータ型でも触れたように、スキャン行数内で最も多かったデータ型が採用されます。

一方インポート時は、スキャン行数内でデータ型が混在していれば、レジストリの ImportMixedTypes エントリによって規定されるデータ型に統一されます。

ImportMixedTypes エントリの既定値は「Text」なので、多くの環境ではテキスト型に統一されるはずです。

これを利用して、IMEX オプションに明示的に 1(Import mode)を指定してリンクテーブルを作成することにより、データ型混在時はテキスト型に統一されることが期待されます。

残念ながら MS-Access のワークシート リンク ウィザードや TransferSpreadsheet メソッドでは IMEX オプションを指定できません。

そこで、リンクテーブルの作成には専用のユーザー定義関数を使用することにします。下記よりモジュールにコピーしてください。

Public Function GenExcelLinkTable( _
             sTableName As String, _
             sFileName As String, _
             sSheetName As String, _
    Optional fUseHeader As Boolean = True, _
    Optional fImportMode As Boolean = False) As Long
' 関数名:GenExcelLinkTable
' 作成者:YU-TANG@http://www.f3.dion.ne.jp/~element/msaccess/
' 目的:Excel からのリンクテーブルを作成します。
' 戻り値:成功時は 0、エラー発生時はエラー番号を返します。
' 引数:
'   sTableName  必須です。作成するリンクテーブル名を指定します。
'               テーブル名に使用できる文字および文字数は Access の
'               仕様制限に準じます。違反した場合は実行時エラーが
'               発生します。
'   sFileName   必須です。リンク元の Excel ファイルのフルパスを
'               指定します。
'   sSheetName  必須です。リンク元の Excel ワークシート名あるいは
'               名前付き範囲/名前無し範囲を指定します。ワークシート名の
'               末尾には忘れずに $ 記号を付加してください。
'               名前無し範囲は、"Sheet1$A1:F6" のように指定します。
'   fUseHeader  省略可能です。データソースの 1 行目をタイトル行として
'               使用する場合(既定)は True を、1 行目もデータ行として
'               使用する場合は False を指定します。
'   fImportMode 省略可能です。IMEX タイプに ImportMode を使用する場合
'               は True を、LinkMode を使用する場合(既定)は False を
'               指定します。
' 使用例:GenExcelLinkTable "テーブル名", "C:\Book1.xls", "Sheet1$"
' 注意 1:DAO ライブラリへの参照設定が必須です。
' 注意 2:同名のテーブルが存在した場合は、エラーが発生します。
On Error GoTo ErrorHandler

    '----( 変数宣言 )-----------------------------------
    Dim db  As DAO.Database
    Dim tdf As DAO.TableDef

    '----( 開始処理 )-----------------------------------
    Set db = CurrentDb()
    Set tdf = db.CreateTableDef(sTableName)
    tdf.SourceTableName = sSheetName
    tdf.Connect = "Excel 5.0" _
                & ";HDR=" & IIf(fUseHeader, "YES", "NO") _
                & ";IMEX=" & IIf(fImportMode, "1", "2") _
                & ";DATABASE=" & sFileName
    db.TableDefs.Append tdf
    RefreshDatabaseWindow

    '----( 終了処理 )-----------------------------------
ExitProcedure:
On Error Resume Next
    Set tdf = Nothing
    db.Close: Set db = Nothing
    Exit Function

    '----( エラー処理 )-----------------------------------
ErrorHandler:
    GenExcelLinkTable = Err.Number
    MsgBox Err.Description, vbCritical, Err.Number
    Resume ExitProcedure
End Function

イミディエイト ウィンドウ(Access 97 以前ではデバッグ ウィンドウ)で、下記のようなコードを記述して [Enter] キーで実行すると、IMEX がインポートモードのリンクテーブルを作成できます。

GenExcelLinkTable "テーブル名", "C:\Book1.xls", "Sheet1$", True, True

トラブルの再現で取り上げた Excel ワークシートからのリンクテーブルを、この GenExcelLinkTable 関数を利用して作成すると、下図のように全データが正常に表示されます。

【商品 - Access 2000 リンクテーブル】
 商品コード仕入先コード商品名単価
 123001B003アブ-クラッシャー\10,980
 0045932874マシュマロ マット\7,800
 368854C417マイナスイオン煙草\350
 76A48B9002電子辞書どーでも英和\12,800
 1A26CZ433次世代携帯カイロ\100

データ型が混在していた [商品コード] と [仕入先コード] は、共にデータがテキスト型に変換されてから、表示されています。

また、次のような SQL 文の選択クエリーを作成することで、リンクテーブルの代用とすることも可能です。

SELECT * FROM [Excel 5.0;HDR=YES;IMEX=1;DATABASE=C:\Book1.xls;].[Sheet1$];

なお例では ISAM タイプに「Excel 5.0」という Excel 95 ブック (Excel Version 7.0)対応形式を指定していますが、Excel 97 以降のブック形式ファイルにしか接続しないことが明らかな場合は、「Excel 8.0」に変更しても構いません。

この方法の注意点は、データ型が混在しているかどうかチェックする行数が、あくまでレジストリの TypeGuessRows エントリに依存する点です。

たとえば TypeGuessRows エントリが 8 になっている端末で開いたときに、データ行の先頭 8 行分のデータ型が全て数値型であれば、Jet はデータ型が混在していないものと判断し、数値型フィールドとしてリンクします。

もし 9 行目以降にテキスト型のデータが存在していれば、それは結局「#Num!」のエラー表示になってしまいます。

TypeGuessRows エントリの値を 0 にすることで、全行をスキャン対象にすることも出来ますが、これは端末依存になってしまう上に、その端末の全 Excel-ISAM 接続に影響しますので、注意が必要でしょう。

関連技術情報

[ACC2000] リンクした Microsoft Excel ワークシートに #Num と表示される

162539 - ACC: #Num Appears in Linked Microsoft Excel Spreadsheet機械翻訳

Microsoft Office アシスタント: Microsoft Office Excel ドライバを初期化する

194124 - PRB: Excel Values Returned as NULL Using DAO OpenRecordset機械翻訳

257819 - [HOWTO] Visual Basic または VBA から ADO を Excel データで使用する

303814 - [HOWTO] VB または VBA から ADOX を Excel データで使用する

190195 - How To Extract Information from Excel Sheet with DAO機械翻訳

109376 - [AC97]Excel データインポート時の型変換について

906460 - Office 2003 Service Pack 2 で修正される Access 2003 の問題

282263 - ACC2002: Ignored MaxScanRows Setting May Cause Improper Data Types in Linked Tables

968580 - Access にて 複数のデータ型が混在する Excel のデータ (XLS 形式 または XLSX形式) をインポートするときに、エラー メッセージ "データ型変換エラー" が表示される場合がある

更新履歴