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 CreatePivotTable 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.SomeWorksheet 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 xlPivotTableVersion12 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]

@redman08
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.Create 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.Application 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, "LastRowNumber 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 "xlPivotTableVersion12" is set up ok (apologies if too daft a question)

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

@redman08
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(xlLastCell) 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. xlPivotTableVersion12 ...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: xlPivotTableVersion12 is set to 3, so have replaced xlPivotTableVersion12 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 xlPivotTableVersion10 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....and 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...formatting...usage., etc.


Many thanks to all contributors.

[FishVal]

You are welcome.

Best regards,
Fish.

[NeoPa]

Don't you just love those Eureka moments ;)

Very pleased to hear that's resolved Redman.

[Predoff]

@redman08
This topic saved my life.
Thanks :PPP