Create Pivot Table using VBA (Access 2007)

[redman08]

I have produced an Excel (2007) worksheet, with which I want to create a Pivot Table. This would all be done from a module run on an Access 2007 database.

Please can anyone supply some simple code for this?

Many thanks for any help received.

[NeoPa]

.SpecialCells(x lLastCell) is really useful in Excel worksheets, but there are circumstances where it's unreliable (When rows or columns have been deleted since the last save for instance).

A technique I use to get the last Row or Column of a particular column or row (NB. Only works when you know which column or row to look in) is to go to the last position and skip back.

EG. If I know that column A has a unique identifier in it (or PK), then I use :

Dim lngLastRow As Long
 
lngLastRow = Range("A65536").End(xlUp).Row

If I know Row 1 has the titles in it I may use (for the Column) :

Dim intLastCol As Integer
 
intLastCol = Range("IV1").End(xlToLeft).Column

These techniques are the VBA equivalents of using Ctrl-Up & Ctrl-Left from the extreme edges of the worksheet.

[redman08]

Re. xlPivotTableVer sion12 ...I've got a blank line.

[NeoPa]

That makes sense. That is a value set up in Excel, and probably available only there.

You can get around this by defining a constant in your code.

[FishVal]

Sacramental question:
Do you have "Microsoft Excel xx.x Object Library" referenced?

[redman08]

Checked on Excel: xlPivotTableVer sion12 is set to 3, so have replaced xlPivotTableVer sion12 with a constant.

Still no go!

I don't have "Microsoft Excel xx.x Object Library" referenced.

[FishVal]

So, reference it or use numeric equivalent of excel constants.

Oh, sorry, I see you've already tried. Did you replace all constants?

P.S. Anyway, I strongly recommend you to reference excel library in Access project. It will add Excel constants to Access namespace and enable Intellisense to resolve excel object model.

[NeoPa]

That's an Office 2007 constant I see.

I tested in Access 2003 with a database with an Excel reference and saw nothing. When I changed it to xlPivotTableVer sion10 it worked fine for me, so a reference to Excel should make that constant available to you, as Fish so rightly says.

[redman08]

I've added Microsoft Excel 12.0 Object Library and Microsoft Office 12.0 Object Library into my References....a nd am still getting '448' errors on line 2.

The code currently looks like this:

With AppExcel.ActiveWorkbook.PivotCaches
    With .Add(SourceType:=xlDatabase, SourceData:=Datasheet.Range("A1:H" & LastRowNumber), Version:=3)
        Set PTable = .CreatePivotTable(TableDestination:=CurrentSheet.Range("A6"), TableName:="AnalysisPivot", DefaultVersion:=3)
    End With
End With

There's not much to look at on that line. The SourceData is being referenced ok (have tested in debug for that); the Version number is ok. The only thing is "xlDatabase " - where is that picked-up from?

[FishVal]

I'd like to draw your attention back to post #14 and #16.
Could you check in object browser (button F2 in VBA IDE) what method PivotCaches class has "Add" or "Create" and what are arguments for the existing method?

[redman08]

Eureka!

It is Create, not Add, and the remaining parameters are as they are now.

Has run...now only the remaining bits...formatti ng...usage., etc.


Many thanks to all contributors.