TransferSpreadsheetメソッドの引数の内、インポートするセル範囲を指定する「Rangeプロパティ」(↓赤字の部分)の記述方法についてまとめました。 DoCmd.TransferSpreadsheet (TransferType, SpreadsheetType, TableName, FileName, HasFieldNames, Range, UseOA)
インポートするExcelのシート数は1枚か？複数か？
シートが1枚だけの場合は、基本的にセル範囲の指定だけでOK！
| 取込対象 | 解説 | 記述例 |
|---|---|---|
| 定型のセル範囲 | 対象範囲左上のセル番地と右下のセル番地を「:」で繋げて記述。 | “A1:G12” |
| 不定型のセル範囲 (列の範囲は不動だが、 行数が変動する場合) | 対象範囲左上のセル番地と右端列の列番地を「:」で繋げて記述 。末端行は自動判定される。 | “A2:R” (1列だけなら↓) “A1:A” |
| Excelの「名前」 のついたセル範囲 | 対象のセル範囲に「取込範囲」という「名前」を付けておいた場合。 | “取込範囲” |
| シート全体 | シート名が「Data」なら、後ろに「!」を付ける。 セル範囲を指定していないので、インポートの対象セル範囲は、自動判定になる。 | “Data!“ |
| 省略する(非推奨) | ちなみにRangeプロパティは省略可能です。 公式情報には→「この引数を指定しないと、ワークシート全体がインポートされます。」とあります。 ・HasFieldNames プロパティがTrue ・Excelのヘッダー項目がテーブルと完全に一致 で実験してみたところ、省略しても成功しました。でもお勧めはしないよ～。 | ー |
シートが複数の場合は、なんだかんだでシートを特定する必要あり！
| 取込対象 | 解説 | 記述例 |
|---|---|---|
| 特定シートの 定型のセル番地 | シート名が「Data」の場合、後に「!」を挟んで、対象範囲左上のセル番地と右下のセル番地を「:」で繋げて記述。 | “Data!A1:F31“ |
| 特定シートの 不定型のセル番地 | シート名が「Data」の場合、 後に「!」を挟んで、対象範囲左上のセル番地と右端列の列番地を「:」で繋げて記述 。末端行は自動判定される。 | “Data!A2:G” |
| シート全体 | シート名が「Data」なら、後ろに「!」を付ける。シートのみの指定になるので、インポート対象のセル範囲は、自動判定になる。 | “Data!“ |
| Excelの「名前」 のついたセル範囲 | 対象のセル範囲に「取込範囲」という「名前」を付けておいた場合。 (Excelの「名前」には、シートの指定も含まれているので、複数シートがあっても、「名前」だけを書けばOK) | “取込範囲” |
＜参考情報＞Excelの「名前」の確認方法は↓コチラ
- Excelの「数式」タブにある「名前の管理」をクリックすると名前の設定を確認できます。
- 「参照範囲」欄に「シート名!セル番地」の形式で記述されています。
変数を使って記述する場合
| 取込対象 | 解説 | 記述例 |
|---|---|---|
| セル番地のみ を変数で指定 | Tgt_RG = “A1:F” ←インポート対象のセル範囲指定 セル番地の変数の記述だけでOK！ | Tgt_RG |
| シートも セル番地も 変数で指定 | Tgt_ST = “Data” ←インポート対象のシート名 Tgt_RG = “A1:F” ←インポート対象のセル範囲指定 シート名の変数とセル番地の変数をダブルコーテーションで囲った「!」を挟んで記述。 | Tgt_ST & “!” & Tgt_RG |
| シート全体を 変数で指定 | Tgt_ST = “Data” ←インポート対象のシート名 シート名の変数の後ろにダブルコーテーションで囲った「!」をつける。 | Tgt_ST ＆“!” |
| 末端行を 変数で取得 | 事前に対象のExcelを読み取り専用で開いて、 Cnt = Application.WorksheetFunction.CountA(Range(“A:A”)) 等で末端行を取得。 | “A1:F”＆ Cnt |
- 変数はString型を使用。だから変数にはダブルコーテーション囲いは要らないよ！
- 「末端行を変数で取得」パターンの変数「Cnt」は数値型の変数ですが、””囲いの文字列と「＆」で繋げた段階で文字列扱いになります。
他のパターンも見つけたら、随時更新していく予定です。
