かんたんExcel接続
ADOを使ってマクロのブック自身に接続する場合、ThisWorkbookモジュールのプロパティに接続文字列を書いておく。
'[ThisWorkbook]
Property Get ConnectionString()
ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES;"""
End Property
接続したいときに１行で呼べてかっこいい（だからなんだ）
'[どこかのモジュール]
Sub hoge()
Dim conn As New ADODB.Connection
conn.Open ThisWorkbook.ConnectionString
End Sub
ちなみにWin7 32bit Excel2003環境では上記プロパイダで接続しようとすると以下のエラーになる。
| 実行時エラー '3706': |
| プロバイダーが見つかりません。正しくインストールされていない可能性があります。 |
Providerを"Microsoft.Jet.OLEDB.4.0"に差し替えると動いた。
Excelデータのテーブル名指定方法
Excelのデータへのアクセス方法。名前の有無でテーブル名の設定内容が変わる。
シート名とセルアドレスを直接指定して接続する場合の書き方。
rs.Open "SELECT * FROM [シート名$A1:C5]", conn
A1セルから始まるデータを持つシートの場合、シート名のみでアクセス可能。
rs.Open "SELECT * FROM [シート名$]", conn
ブックレベルの名前付きセル範囲にアクセスする場合、"シート名$"の記述は不要。というかシート名があるとエラーになる。
rs.Open "SELECT * FROM [名前付きセル範囲]", conn
シートレベルの名前付きセル範囲にアクセスする場合は"シート名$"の記述が必要。
rs.Open "SELECT * FROM [シート名$名前付きセル範囲]", conn
可変長の名前付きセル範囲を直接指定してアクセスすることは不可能。（実行時エラーが発生する）
rs.Open "SELECT * FROM [可変長の名前付きセル範囲]", conn
| 実行時エラー '-2147217865 (80040e37)': |
| オブジェクト '可変長の名前付きセル範囲' が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください。 |
★可変長の名前付きセル範囲にアクセスするためには
セル範囲をテーブル名に変換するヘルパー関数を使用する。
rs.Open "SELECT * FROM " & ToTableName(Range("可変長の名前付きセル範囲"))
' セル範囲を"[シート名$セルアドレス(A1形式)]"に変換するヘルパー関数
Function ToExcelTableName(ByVal rng As Range) As String
ToExcelTableName = "[" & rng.Parent.Name & "$" & rng.Address(False, False) & "]"
End Function
このヘルパー関数は、可変長の名前付きセル範囲に限らずどんなセル範囲でもテーブル名に変換できるので便利。
Excelデータ取得時のエラー別対処方法
ADODB.RecordsetでExcelのデータを取ろうとしたらエラーになるんですけどぉー！？
という場合のエラーメッセージ別対処方法。
Win7 32bit Excel2003環境にて確認。
1 つ以上の必要なパラメーターの値が設定されていません｡
| 実行時エラー '-2147217904 (80040e10)': |
| 1 つ以上の必要なパラメーターの値が設定されていません｡ |
→SQLのSELECT,WHERE,ORDER BY等に渡すフィールドの名前が間違っている可能性がある。
BOF と EOF のいずれかが True になっているか、または現在のレコードが削除されています。要求された操作には、現在のレコードが必要です。
| 実行時エラー '3021': |
| BOF と EOF のいずれかが True になっているか、または現在のレコードが削除されています。要求された操作には、現在のレコードが必要です。 |
→何だかよく分からないがフィールド名に"No"を使っていてWhere条件で"No"を指定するとこのエラーがでる。
予約語？"Yes"ではエラーにならなかった。
抽出条件でデータ型が一致しません｡
| 実行時エラー '-2147217913 (80040e07)': |
| 抽出条件でデータ型が一致しません｡ |
→SQLのWHERE条件に指定したフィールドのセルの分類によりこのエラーが発生する場合がある。
「標準」
そのデータが数値であれば、フィールド名=1と指定しなければならない。
もしデータが数値以外の文字列であれば、フィールド名='ABC'と'で括って指定する。
「文字列」
データが数値であってもフィールド名='1'と指定すること。
「数値」
調査中…。
メモリ不足です｡
| 実行時エラー '-2147467259 (80004005)': |
| メモリ不足です｡ |
→FROM句に指定したセル範囲に、全く、何も値が入っていない場合このエラーが発生する可能性がある。
リンクされている Excel のワークシートを表示するための接続が切断されました。
| 実行時エラー '-2147467259 (80004005)': |
| リンクされている Excel のワークシートを表示するための接続が切断されました。 |
→この記事を書くために実験しまくっていたらこのエラーが出るようになってしまった。
実験内容はFROM句に指定したセル範囲のフィールド名が空だったらどうなるかというもの。
このエラーメッセージが出るとどんなに正しいSQLで接続してもこのエラーを出すようになってしまう。ひどい。
対処法としてはExcelファイルを新しく作り直すことぐらい。
今まで見たことがないので普通に正しくADODBを使っていれば大丈夫…と思う。
最終更新：2015年11月15日 15:45
