MS-ACCESS内のクエリーをPIVOT化する(リンクされているためリフレッシュ可能)
更にそのPIVOTからグラフを作成する
sample table field: shohin,month,bunsi,bunbo

Sub make_pivot(Tbl$, PVN As Integer, GOAL As Boolean)
	'Tbl$ = 対象のTable/クエリー名
	Dim PV As Variant, i As Integer
	'ソース情報(MDB)の取り出し文を作成
	a1$ = “ODBC;DSN=MS Access Database;"
	a2$ = “DBQ=\\file_server\path\FIR09.mdb;"
	a3$ = “DefaultDir=\\file_server\path;"
	a4$ = “DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
	'pivot名称(内部用)
	PV = “pivot" & PVN
	'PIVOT用に新規シート作成
	Worksheets.Add
	'PVOTシート名はTable名とする
	ActiveSheet.Name = Tbl$
	Range(“B4").Select
	'PIVOT作成
	With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
		.Connection = Array(Array(a1$), Array(a2$), Array(a3$), Array(a4$))
		.CommandType = xlCmdSql
		.CommandText = Array(“SELECT * FROM “ & Tbl$)
		.CreatePivotTable TableDestination:=“R3C1", TableName:=PV, DefaultVersion:=xlPivotTableVersion10
	End With
	
	On Error Resume Next
	'RATEフィールドの追加(DBには無いField)
	ActiveSheet.PivotTables(PV).CalculatedFields.Add “RATE", “=bunshi/bunbo", True
	ActiveSheet.PivotTables(PV).PivotFields(“RATE").Orientation = xlDataField
	Range(“B4").Select
	
	'グラフ作成
	Charts.Add
	ActiveChart.SetSourceData Source:=Sheets(Tbl$).Range(“B4")
	ActiveChart.Location Where:=xlLocationAsNewSheet
	ActiveChart.ChartArea.Select
	ActiveChart.ChartType = xlColumnClustered
	ActiveChart.Location Where:=xlLocationAsNewSheet
	ActiveWindow.Zoom = 100
	ActiveChart.Axes(xlValue).Select
	Selection.TickLabels.NumberFormatLocal = “0.00%"
	ActiveChart.ChartArea.Select
	Selection.AutoScaleFont = True
	With Selection.Font
		.Name = “Verdana"
		.Size = 8
		.Strikethrough = False
		.Superscript = False
		.Subscript = False
		.OutlineFont = False
		.Shadow = False
		.Underline = xlUnderlineStyleNone
		.ColorIndex = xlAutomatic
		.Background = xlAutomatic
	End With
	
	'グラフに縦横フィールド名セット
	ActiveChart.PivotLayout.PivotTable.PivotFields(“shohin").Orientation = xlRowField
	ActiveChart.PivotLayout.PivotTable.PivotFields(“shohin").Position = 1
	ActiveChart.PivotLayout.PivotTable.PivotFields(“month").Orientation = xlColumnField
	ActiveChart.PivotLayout.PivotTable.PivotFields(“month").Position = 1
	'bunboの合計をを値としてセット
	ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _
		PivotTable.PivotFields(“bunbo"), “合計 / bunbo", xlSum
	   
	On Error GoTo 0
	'グラフシートの名前をセット
	ActiveSheet.Name = Tbl$ & “グラフ"
	
	'凡例の表示
	ActiveChart.ChartArea.Select
	ActiveChart.HasLegend = True
	'凡例の場所設定
	ActiveChart.Legend.Select
	Selection.Position = xlBottom
	ActiveChart.Deselect
	
End Sub