ray88’s diary

お仕事で困ったとき用の自分用の覚書

トップ > AccessのデータからExcelのテーブルをVBAで作成する

AccessのデータからExcelのテーブルをVBAで作成する


■Excelのテーブルを作成するにはListObjectコレクションのAddメソッドを用いる

オブジェクト.ListObject.Add(SourceType,Source,LinkSource, _
                                XlListObjectHasHeaders,Destination,TableStyleName)

オブジェクト・・・・・テーブル作成先のWorksheetオブジェクト
SourceType・・・・・テーブルの元となるデータの種類の定数を下表から指定。省略可。
 　　　　　　　　　　省略した場合はxlSrcRange

定数 意味
xlSrcExternal 外部データ
xlSrcModel パワーピポットモデル
xkSrcQuery クエリ
xlSrcRange セル範囲
xlSrcXml XML
xlGuess データから自動判定
xlNo データの先頭行を見出しとしない
xlYes データの先頭行を見出しとする

Source・・・・・・データの元となるデータ
LinkSource・・・・外部データの場合、リンクするならTrue,しないならFlaseを指定。
 　　　　　　　　　省略可。省略した場合はTrueが指定される
xlListObjectHasHeaders・・・データの先頭行を見出しとするか下表から指定。
 　　　　　　　　　 　　　省略可。省略した場合、xlGuessが指定される

定数 意味
xlGuess データから自動判定 　　
xlNo データの先頭行を見出しとしない
xlYes データの先頭行を見出しとする

Destination・・・・・テーブルの作成先となるセルのオブジェクトを指定
 　　　　　　　　元となるデータがセル範囲の場合は省略可。その場合は元のセル範囲がテーブル化される。
TableStyleName・・・・・テーブルのスタイル。
 　　　　　　　　　　　　省略可。省略すると既定のスタイルが適用される
■引数SourceがAccessデータベースの場合の設定方法

OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=データベースファイル名

データベースファイル名・・・・・目的のAccessデータベースのファイル名を絶対パス付で指定
※以下は記述例

OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\デスクトップ\test.accdb

■サンプルコード（AccessDBよりSheet1へテーブル取り込み）

Sub テーブル作成()
    Dim path As String
    Dim lo As ListObject
    
    '取り込み対象のAccessDBファイルのパス
    path = "C:\デスクトップ\test.accdb"
    
    'Listobject型の変数に取り込み対象のテーブルを格納
    Set lo = Worksheets("Sheet1").ListObjects.Add(SourceType:=xlSrcExternal, _
             Source:="OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path, _
             Destination:=Range("A1"))
             
    '格納したテーブルに対してクエリを設定
    With lo.QueryTable
        .CommandType = xlCmdTable
        .CommandText = "Q_注文データ"
        .Refresh BackgroundQuery:=False
    End With
    
    'テーブル名を指定し、書式を設定
    lo.Name = "注文データ"
    Range("注文データ[注文日]").NumberFormatLocal = "yyyy/m/d"
    Range("注文データ[単価]").Style = "通貨"
    Range("注文データ[小計]").Style = "通貨"
End Sub

テーブル作成に追記したコードについての説明
■取り込み選択クエリの設定等をVBAで行う
① AccessDBの選択クエリを指定するにはQueryオブジェクトのComanndTypeプロパティに定数xlCmdTableを設定する

オブジェクト.CommandTYpe = 設定値

オブジェクト・・・・・QueryTalbeオブジェクト
設定値・・・・・Accessデータベースの選択クエリなら定数xlCmdTableを指定
②CommandTextプロパティに選択クエリの名前を文字列として設定する

オブジェクト.CommandText = クエリ名

オブジェクト・・・・・QueryTableオブジェクト
クエリ名・・・・・Accessデータベースの選択クエリ名を文字列として指定
③RefreshメソッドでテーブルにAccessデータベースの選択クエリからデータを取り込む

オブジェクト.Refresh(BackGroundQyery)

オブジェクト・・・・・QueryTableオブジェクト
BackGroundQuery・・・・・バックグラウンドで更新するならTrue、しないならFalseを指定
■Excelのテーブルの名前や表示形式をVBAで設定する
①ListObjectのNameプロパティでテーブル名を設定する

オブジェクト.Name = テーブル名

オブジェクト・・・・・ListObjectオブジェクト
テーブル名・・・・・テーブル名を文字列として指定
②Rangeオブジェクトを使用し以下の書式で目的のテーブルのセル範囲を取得する

Range("テーブル名[列名]")

テーブル名・・・・・テーブル名を指定
列名・・・・・列名を指定
※以下は記述例

Range("注文データ[注文日]")

③NumberFormatプロパティでテーブルの表示形式を変更する

オブジェクト.NumberFormatLocal = 表示形式

オブジェクト・・・・・セル範囲のオブジェクト
表示形式・・・・・表示形式を書式記号で文字列として指定
※書式記号は「セルの書式設定」のダイアログボックスの「表示形式」タブの分類「ユーザ定義」で使用する
  表示形式と同じ。以下は記述例

Range("注文データ[注文日]").NumberFormatLocal = "yyyy/m/d"

④Styleプロパティでセルのスタイルを変更する
　※Formatプロパティ以外の方法としてStyleプロパティでセルの表示形式を変更する方法

オブジェクト.Style = スタイル

オブジェクト・・・・・セル範囲のオブジェクト
スタイル・・・・・スタイル名を文字列として指定
※以下は記述例・「"通貨"」に替えて「”Currency[0]"」でもOK

Range("注文データ[単価]").Style = "通貨"