create Excel Pivot Table from Access

[tyrone]

I am trying to run a series of Access queries and export them to individual excel workbooks ? I then need to create an excel pivot table in each workbook and then batch email when completed.

The Problem: the attached code segment runs Ok until I get to until it gets to then line ?Set PT = PTcache.CreatePivotTable ??.

Reference to Microsoft excel object library is checked in references

I then get an error message telling me that the pivot table field name is invalid. I have tested the address for the data region address for the pivot table cache that is as required.

When I go to excel and create the pivot table manually there are no problems with invalid pivot table field names. Does any one have any ideas as to why I would be receiving this error. Hope someone can help


Sub Rtherapy0506()

Dim objexcel As Object
Dim objwbk As Workbook
Dim objwsh_a As Worksheet
Dim objwsh_b As Worksheet
Dim objwsh_c As Worksheet
Const RadThpyDir As String = "E:\"
Const RadWDat_b = "RTherapy_W_Dat_trans"
Const PVTSht = "Pivot_RTherapy_Wgt"

Dim RtherapyFle As String
RtherapyFle = "Radiotherapy_ytd0506_" & Format(Now(), "DDMMMYY") & "_" & Format(Now(), "hhmm") & ".xls"

DoCmd.OutputTo acQuery, RadWDat_b, acFormatXLS, RadThpyDir & "\" & RtherapyFle

Set objexcel = CreateObject("Excel.Application")
objexcel.Visible = False
Set objwbk = objexcel.workbooks.Open(RadThpyDir & "\" & RtherapyFle)
Set objwsh_b = objwbk.Worksheets(RadWDat_b)

DoCmd.SetWarnings False

objwbk.Worksheets.Add
objwbk.ActiveSheet.Name = PVTSht
Set objwsh_c = objwbk.Worksheets(PVTSht)

Dim PTcache As PivotCache
Dim PT As PivotTable

objwsh_b.Activate

Set PTcache = objwbk.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=objwsh_b.Range("A1").CurrentRegion.Address)
Set PT = PTcache.CreatePivotTable _
(TableDestination:=objwsh_c.Range("a6"), TableName:="Ptable0506 ")

With PT

.PivotFields(1).Orientation = xlPageField
.PivotFields(2).Orientation = xlPageField
.PivotFields(3).Orientation = xlPageField
.PivotFields(4).Orientation = xlPageField
.PivotFields(5).Orientation = xlColumnField
.PivotFields(6).Orientation = xlRowField
.PivotFields(7).Orientation = xlDataField

End With

objwbk.Save
objwbk.Close
objexcel.Quit
Set objexcel = Nothing

End Sub





thanks Tyrone

[OBP]

Have you tried recording an Excel macro of creating the pivot table and comparing the code?
Is it essential to do the pivot table in Excel?
As Access has good "Pivot Table Forms".

[tyrone]

OBP: Thanks for your response. It is essential that the data is provided in excel. The pivot table code in the question is exactly the same as i would write in in excel (and it works). I find it a bit strange ( but maybe not) that it will work in the excel application but not when using excel form access.
Got to try a little harder i guess

tyrone

[OBP]

tyrone, I would add some Error handling and then some msgboxes at strategic points of your code to identify which part(s) is causing the failure.
If you would like to post a copy on here I could have a look at it for you, not that I have ever tried to do anything like it.
Do you have to do it in VBA?
Can't you just link a spreadsheet with pivot table on to an Access query?
Can you run the Excel module by asking Access VBA to run an Excel Macro.
I was just trying some lateral thinking lol.

[XLGibbs]

It would seem that using MS Query and hooking into the Access database, then returning the data into a Pivot table would be the most sensible and efficient way to do this. I have tons and tons of these set up in my reporting files that are linked to multiple databases in SQL server and Access.