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]

I'm afraid not.

We are here to assist people to develop their own work, not to do the work for them. There's flexibility to this, but in a case such as this, where it could easily be a homework or project question, we would be very hesitant to provide anything.

Please see the Posting Guidelines for how you could redo this in such a way that we can help.

[redman08]

This is not homework, nor a project question.

I have come across various bits of code on the net and looked at output from a macro recording when creating a pivot table in escel (2007).

However, when transferring this to a vba module, I keep getting errors. In a vain attempt to see if there was a quick and simple resolution to this problem, I raised this post.

I have seen far simpler (I know, in my opinion) problems resolved in this forum, so it seemed right to ask here.

[NeoPa]

I'm certainly not saying you can't ask for help. Nor that the problem is too complex.

Please reread the post, with specific reference to the linked Help section that tells how questions should be asked. Posting what you have, with an explanation of what's failing where, would be acceptable. There is nothing to stop me, or anyone else, providing better code at that stage if that seems appropriate.

None of us is looking to make it hard to ask questions (why would we be here for that?), but the link does explain what we expect in the way of asking questions, and alo why if you're interested to look that deeply.

[redman08]

The current code looks like this, where Datasheet is the worksheet containing the data, and CurrentSheet is a new sheet being created to hold the Pivot Table:

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

I have tried various versions of the above including holding the data/table output ranges in named variables.

Currently, this code returns a "Run-time error '448':Named argument not found" error message.

[NeoPa]

Now you're talking.

I will see if I can help some here, but I'm not experienced with Pivot Tables. Probably later though as I'm working just now.

BTW. Can you say which line of the code the error message came on? It'll be either #2 or #3 I would guess, but that can tie down where to look.

[redman08]

The error is on Line #2

[FishVal]

Frankly speaking, the code looks fine.
Maybe Excel doesn't like the range you make PivotCache from.
I've made some trials and found that CreatePivotTabl e method fails if first cell in the range is empty.

[FishVal]

@redman08
what value is stored in LastRowNumber variable at this point?

[Stewart Ross]

Beware! The macro recorder produces code that runs fine inside an Excel workbook where the Application object is implied. In an Excel sheet you can refer to ActiveSheet, ActiveWorkbook and so on without specifying the application object explicitly. However, references to implied Excel application objects do not work when running code in an Access VBA module using Access to communicate with Excel as an automation server.

For instance, the macro recorder frequently produces code where the ActiveSheet object is implied, so you will find things like

Range("A1:A1"). <Do Something>

which fail in Access VBA, as there is no implicit Application.Som eWorksheet object wrapping the code to link back to the specified Range object.

You will need to fully qualify implicit references to tie them to the appropriate Application object, as you are doing in the first part of the With on line 1, and ensure that all object variables you use (such as DataSheet and CurrentSheet) are explicitly set:

...SourceData:=Datasheet.Range("A1:H" & LastRowNumber)...
...TableDestination:=CurrentSheet.Range("A6")...

You will also need to check that the xlDatabase and xlPivotTableVer sion12 constants are available within your Access VBA code module - the debugger can help you here - as if they are not you will need to substitute their actual values for the symbolic ones listed (or supply equivalent constants of your own).

I would expect to see lines like this to set the value of Datasheet, for example:

Dim DataSheet as WorkSheet
Set DataSheet = AppExcel.ActiveWorkBook.Sheets("DataSheet")
...

If you have not set Datasheet as a worksheet object you cannot treat it as such in line 2.

-Stewart