Create Pivot Table using VBA (Access 2007)

[FishVal]

• I still have no idea how LastRowNumber gets ever value since the code is more than expected to fail when you try to invoke method of not initialized object variable CurrentSheet (see my previous post), unless it is initialized with the code you haven't posted.
• Check in object browser (F2 button in VBA IDE) what method "Add" or "Create" PivotCaches class has and what are the arguments of this method.
• What happen if you just remove "Version" argument from the problematic code line?

[redman08]

Removing 'Version' parameter only produces a '448' error.

[FishVal]

What with the rest from post #16 ?

[Stewart Ross]

If you need to find the last row number of the active sheet, there is no need to use CountA to do so. Like Fish I can't see why you don't have a run-time error occurring on the CountA line as a result of the uninitialised CurrentSheet object reference.

To find the last row number of the active sheet you can use

LastRowNumber = AppExcel.ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row

This uses the .Row property of the Range object to return the row reference of the range to the last active cell. There is also a .Column property which will return the column number (as an integer, not in A, B...ZZ form) should this be needed.

Stewart

[NeoPa]

How do I check if "xlPivotTableVe rsion12" is set up ok (apologies if too daft a question)

Not at all.

From the VBA IDE (Alt-F11 from the main app window), use Ctrl-G to go to the Immediate Pane, and type :

?xlPivotTableVersion12

If any value is displayed then you know that it is set. If it is not recognised you will see a blank line.

[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.