Error in Creating EXCEL Pivot Table using ACCESS DataBase

[vivek1431]

Hello:

I am trying to create a pivot table in EXCEL using an ACCESS Database. However, I am getting an error which says -Runtime Error 1004 : [Microsoft][ODBC Microsoft ACCESS Driver] Could not find the file 'unknown'.

This is a VBA code straight from John Walkenbach's book. It wuns when I run the code from CD provided in the book. However, it does not run when I save the code and ACCESS Database on the c: drive.

Following is the code:
Sub CreatePivotTableFromDB()
Dim PTCache As PivotCache
Dim PT As PivotTable

' Delete PivotSheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet&quot.Delete
On Error GoTo 0

' Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlExternal)

' Path to database file
DBFile = ThisWorkbook.Path & "\budget.mdb"

' Connection String
ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile

' QueryString
QueryString = "SELECT * FROM `" & ThisWorkbook.Path & _
"\BUDGET`.Budget Budget"

With PTCache
.Connection = ConString
.CommandText = QueryString
End With

' Add new worksheet
Worksheets.Add
ActiveSheet.Name = "PivotSheet"

' Create pivot table
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet&quot.Range("A1&quot, _
TableName:="BudgetPivot&quot

' Add fields
With PT
' Add fields
.PivotFields("DEPARTMENT&quot.Orientation = xlRowField
.PivotFields("MONTH&quot.Orientation = xlColumnField
.PivotFields("DIVISION&quot.Orientation = xlPageField
.PivotFields("BUDGET&quot.Orientation = xlDataField
.PivotFields("ACTUAL&quot.Orientation = xlDataField
End With
End Sub

Can somebody help me?

Thanks!

 

[SkipVought]

Hi,

So you have BOTH the Excel workbook AND the Access database in the same folder?

What statement is it cratering on?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[vivek1431]

Thanks for the reply. It stops at following step:

' Create pivot table
Set PT = PTCache.CreatePivotTable( _
TableDestination:=Sheets("PivotSheet&quot.Range("A1&quot, _
TableName:="BudgetPivot&quot

 

[SkipVought]

I think that you need a workbook object in the TableDestination

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com