Problem creating Excel Pivot table from Access (office 2000)

Question

Hi,

I'm working in a mixed office 2003- office 2000 environment.  One of the VBA procedures I have been creating retrieves data from an access database and outputs it to an excel overview and pivot table. In the office 2003 environment it runs without a hitch.  However, in office 2000 the procedure crashes on the creation of the pivotcache.

As far as I can tell I'm missing something in the way access 2000 handles other applications compared to access 2003, I have no idea what though.

Help appreciated.

Stand-alone (debug) code segment :

Attribute VB_Name = "test"
Option Compare Database
Option Explicit
'use late binding to avoid ref lib problems

'xlConstants to compensate for late binding
Private Const xlWhole = 1
Private Const xlValues = -4163
Private Const xlDatabase = 1
Private Const xlDataField = 4
Private Sub cmdExport_Click()

Dim db As Object 'Database
Dim xl As Object 'Excel.Application
Dim wkbOne As Object 'Excel.workbook
Dim strfilename As String
Dim qryPH As Object 'QueryDef
Dim strSQL As String
Dim my As Variant 'my Computer
Dim xlRunning As Boolean

Set db = CurrentDb
strfilename = "c:\temp\test_purpose.xls"

On Error Resume Next
Set xl = GetObject(, "Excel.Application")
xlRunning = True
If Err.Number > 0 Then
    Set xl = CreateObject("Excel.Application")
    xlRunning = False
End If
With xl
    .DisplayAlerts = False
    Set wkbOne = .Workbooks.Open(strfilename)
    Call XTableKPI(wkbOne) 'create overview tables
    If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
    Err.Clear
    'wkbOne.Name = "KPI " & quarter & year
    wkbOne.SaveAs strfilename
    If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
    Err.Clear
    wkbOne.Close
    If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
    Err.Clear
    If Not (xlRunning) Then .Quit
    '.Close
    If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
Err.Clear
End With
End Sub

Private Sub XTableKPI(wkb As Object)

Dim wshtOverview As Object 'worksheet
Dim wshtControl As Object 'worksheet
Dim SourceDataRange As Object 'range
Dim C As Object 'placeholder cell
Dim PTCache As Object 'PivotCache
Dim PT As Object 'PivotTable
Dim FirstEmptyRow As Long
Dim FirstEmptyColumn As Long


On Error Resume Next


wkb.Activate

Set wshtControl = wkb.Worksheets(1)
If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
   Err.Clear
wshtControl.Name = "Details Controls"
If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
Err.Clear
wkb.Worksheets(2).Delete 'avoid memory issues with non-deletion of pivot caches
wkb.Save

Set wshtOverview = wkb.Worksheets(2)
If Err.Number > 0 Then
    Err.Clear
    Set wshtOverview = wkb.Worksheets.Add(After:=wshtControl)
End If
If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
Err.Clear
wshtOverview.Name = "Overview"
If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
Err.Clear

wshtControl.Activate
Set C = wshtControl.Columns(1).Find("", LookIn:=xlValues, lookat:=xlWhole)
If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
FirstEmptyRow = C.Row

Set C = wshtControl.Rows(1).Find("", LookIn:=xlValues, lookat:=xlWhole)
If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
Err.Clear
FirstEmptyColumn = C.Column

Set SourceDataRange = wshtControl.Range(wshtControl.Cells(1, 1), wshtControl.Cells(FirstEmptyRow - 1, FirstEmptyColumn - 1))
If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
Err.Clear

Set PTCache = wkb.pivotcaches.Add(SourceType:=xlDatabase, SourceData:=SourceDataRange)
If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
Err.Clear

Set PT = PTCache.CreatePivotTable(TableDestination:=wshtOverview.Cells(1, 1), _
        TableName:="Controls")
        ', DefaultVersion:=xlPivotTableVersion10
If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
Err.Clear
'wshtOverview.PivotTableWizard TableDestination:=wshtOverview.Cells(3, 1)
'If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
'Err.Clear
'ActiveSheet.Cells(3, 1).Select
wshtOverview.PivotTables("Controls").AddFields RowFields:=Array("Name", _
    "Company", "district", "place", "Type dossier", "Projectnr"), PageFields:= _
    "Type visit"
wshtOverview.PivotTables("Controls").PivotFields("date executed"). _
    Orientation = xlDataField

wshtOverview.PivotTables("Controls").PivotFields("Type dossier").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)

wshtOverview.PivotTables("Controls").PivotFields("place").Subtotals = Array( _
    False, False, False, False, False, False, False, False, False, False, False, False)

wshtOverview.PivotTables("Controls").PivotFields("district").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)
If Err.Number > 0 Then MsgBox ("error: " & Err.Number & " " & Err.Description)
Err.Clear

Set PT = Nothing
Set PTCache = Nothing

End Sub
<br/><br/>