L
LongVacation
Would anyone advice on why I am getting this error? Excel VBA code to extract
data from Access (see picture).
Thanks in advance.
Sub CreatePivotTableFromDB()
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim DBFile As Variant
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0
'Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
'Connect to database and do query
DBFile = ThisWorkbook.Path & "\budget.mdb"
ConString = "ODBC;DSN=MS Acess Database;DBQ=" & DBFile
QueryString = "SELECT * FROM BUDGET"
With PTCache
.Connection = ConString
.CommandText = QueryString
End With
'add new worksheet
Worksheets.Add
ActiveSheet.Name = "PivotSheet"
'create pivot table<===========Error at the line below
Set PT =
PTCache.CreatePivotTable(TableDestination:=Sheets("PivotSheet").Range("A1"),
TableName:="BudgetPivot")
'add fields
With PT
.PivotFields("DEPARTMENT").Orientation = xlRowField
.PivotFields("MONTH").Orientation = xlColumnField
.PivotFields("DIVISION").Orientation = xlPageField
.PivotFields("BUDGET").Orientation = xlDataField
.PivotFields("ACTUAL").Orientation = xlDataField
End With
End Sub
data from Access (see picture).
Thanks in advance.
Sub CreatePivotTableFromDB()
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim DBFile As Variant
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0
'Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
'Connect to database and do query
DBFile = ThisWorkbook.Path & "\budget.mdb"
ConString = "ODBC;DSN=MS Acess Database;DBQ=" & DBFile
QueryString = "SELECT * FROM BUDGET"
With PTCache
.Connection = ConString
.CommandText = QueryString
End With
'add new worksheet
Worksheets.Add
ActiveSheet.Name = "PivotSheet"
'create pivot table<===========Error at the line below
Set PT =
PTCache.CreatePivotTable(TableDestination:=Sheets("PivotSheet").Range("A1"),
TableName:="BudgetPivot")
'add fields
With PT
.PivotFields("DEPARTMENT").Orientation = xlRowField
.PivotFields("MONTH").Orientation = xlColumnField
.PivotFields("DIVISION").Orientation = xlPageField
.PivotFields("BUDGET").Orientation = xlDataField
.PivotFields("ACTUAL").Orientation = xlDataField
End With
End Sub