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]

The error is on Line #2

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

[NeoPa]

Thanks for picking this up guys.

I hadn't realised it was an automation issue. I might have been able to help there, but as you caught that already Stewart, I suspect there's little more for me to do.

I will keep monitoring of course.

[FishVal]

....

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'm not sure about Excel 2007, but in Excel 2003 PivotCaches.Add method has no "Version" argument, which is, BTW, what error message is about.

[redman08]

Just to put more meat on the bones regarding definitions (and to repeat the problem coding):

Dim CurrentSheet As Variant
Dim Datasheet As Variant
Dim LastRowNumber As Integer

Dim AppExcel As Object
Set AppExcel = CreateObject("excel.application")

LastRowNumber = AppExcel.WorksheetFunction.CountA(CurrentSheet.Range("A1:A65536"))

Dim PTable As Variant

Set Datasheet = AppExcel.ActiveWorkbook.Sheets("Data")
Set CurrentSheet = AppExcel.ActiveWorkbook.Worksheets.Add

With CurrentSheet
    .Name = "Analysis"
End With


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

Hope I've not missed anything out.

[FishVal]

MSDN article does not enlist at all Add method in PivotCaches object in Excel 2007 object model. It could be supported though for backward compatibility but certainly with two arguments as in Excel 2003. At the same time PivotCaches.Cre ate method is being called with arguments you try to pass to "Add" method.

P.S. Looks like excel 2007 automatically adds Workbook object to Workbooks collection when Excel.Applicati on object has been created, otherwise the code will fail much earlier. IMHO, it is weird and may depend on Excel application settings which could vary from installation to installation.

P.P.S. Actually, I don't have any idea why the code doesn't fail on this line

LastRowNumber = AppExcel.WorksheetFunction.CountA(CurrentSheet.Ran ge("A1:A65536"))

since CurrentSheet variable is not initialized.

[redman08]

Fishval, "LastRowNum ber is set to 18 when the error occurs.

When using ".Add", I get an error '448' - Named argument not found,;

When using ".Create", i get an error '5' - Invalid procedure call or agument.

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