By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,842 Members | 2,154 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,842 IT Pros & Developers. It's quick & easy.

Create Pivot Table using VBA (Access 2007)

P: 34
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.
Apr 21 '09 #1
Share this Question
Share on Google+
32 Replies


NeoPa
Expert Mod 15k+
P: 31,306
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.
Apr 21 '09 #2

P: 34
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.
Apr 21 '09 #3

NeoPa
Expert Mod 15k+
P: 31,306
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.
Apr 21 '09 #4

P: 34
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:
Expand|Select|Wrap|Line Numbers
  1. With AppExcel.ActiveWorkbook.PivotCaches
  2.     With .Add(SourceType:=xlDatabase, SourceData:=Datasheet.Range("A1:H" & LastRowNumber), Version:=xlPivotTableVersion12)
  3.         Set PTable = .CreatePivotTable(TableDestination:=CurrentSheet.Range("A6"), TableName:="AnalysisPivot", DefaultVersion:=xlPivotTableVersion12)
  4.     End With
  5. 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.
Apr 21 '09 #5

NeoPa
Expert Mod 15k+
P: 31,306
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.
Apr 21 '09 #6

P: 34
The error is on Line #2
Apr 21 '09 #7

FishVal
Expert 2.5K+
P: 2,653
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.
Apr 21 '09 #8

FishVal
Expert 2.5K+
P: 2,653
@redman08
what value is stored in LastRowNumber variable at this point?
Apr 21 '09 #9

Expert Mod 2.5K+
P: 2,545
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:

Expand|Select|Wrap|Line Numbers
  1. ...SourceData:=Datasheet.Range("A1:H" & LastRowNumber)...
  2. ...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:

Expand|Select|Wrap|Line Numbers
  1. Dim DataSheet as WorkSheet
  2. Set DataSheet = AppExcel.ActiveWorkBook.Sheets("DataSheet")
  3. ...
If you have not set Datasheet as a worksheet object you cannot treat it as such in line 2.

-Stewart
Apr 21 '09 #10

NeoPa
Expert Mod 15k+
P: 31,306
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.
Apr 21 '09 #11

FishVal
Expert 2.5K+
P: 2,653
@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.
Apr 21 '09 #12

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

Expand|Select|Wrap|Line Numbers
  1. Dim CurrentSheet As Variant
  2. Dim Datasheet As Variant
  3. Dim LastRowNumber As Integer
  4.  
  5. Dim AppExcel As Object
  6. Set AppExcel = CreateObject("excel.application")
  7.  
  8. LastRowNumber = AppExcel.WorksheetFunction.CountA(CurrentSheet.Range("A1:A65536"))
  9.  
  10. Dim PTable As Variant
  11.  
  12. Set Datasheet = AppExcel.ActiveWorkbook.Sheets("Data")
  13. Set CurrentSheet = AppExcel.ActiveWorkbook.Worksheets.Add
  14.  
  15. With CurrentSheet
  16.     .Name = "Analysis"
  17. End With
  18.  
  19.  
  20. With AppExcel.ActiveWorkbook.PivotCaches
  21.     With .Add(SourceType:=xlDatabase, SourceData:=Datasheet.Range("A1:H" & LastRowNumber), Version:=xlPivotTableVersion12)
  22.         Set PTable = .CreatePivotTable(TableDestination:=CurrentSheet.Range("A6"), TableName:="AnalysisPivot", DefaultVersion:=xlPivotTableVersion12)
  23.     End With
  24. End With
Hope I've not missed anything out.
Apr 22 '09 #13

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. LastRowNumber = AppExcel.WorksheetFunction.CountA(CurrentSheet.Ran ge("A1:A65536"))
  2.  
since CurrentSheet variable is not initialized.
Apr 22 '09 #14

P: 34
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)
Apr 22 '09 #15

FishVal
Expert 2.5K+
P: 2,653
  • 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?
Apr 22 '09 #16

P: 34
Removing 'Version' parameter only produces a '448' error.
Apr 22 '09 #17

FishVal
Expert 2.5K+
P: 2,653
What with the rest from post #16 ?
Apr 22 '09 #18

Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. 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
Apr 22 '09 #19

NeoPa
Expert Mod 15k+
P: 31,306
@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 :
Expand|Select|Wrap|Line Numbers
  1. ?xlPivotTableVersion12
If any value is displayed then you know that it is set. If it is not recognised you will see a blank line.
Apr 22 '09 #20

NeoPa
Expert Mod 15k+
P: 31,306
.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 :
Expand|Select|Wrap|Line Numbers
  1. Dim lngLastRow As Long
  2.  
  3. lngLastRow = Range("A65536").End(xlUp).Row
If I know Row 1 has the titles in it I may use (for the Column) :
Expand|Select|Wrap|Line Numbers
  1. Dim intLastCol As Integer
  2.  
  3. 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.
Apr 22 '09 #21

P: 34
Re. xlPivotTableVersion12 ...I've got a blank line.
Apr 22 '09 #22

NeoPa
Expert Mod 15k+
P: 31,306
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.
Apr 22 '09 #23

FishVal
Expert 2.5K+
P: 2,653
Sacramental question:
Do you have "Microsoft Excel xx.x Object Library" referenced?
Apr 22 '09 #24

P: 34
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.
Apr 22 '09 #25

FishVal
Expert 2.5K+
P: 2,653
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.
Apr 22 '09 #26

NeoPa
Expert Mod 15k+
P: 31,306
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.
Apr 22 '09 #27

P: 34
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:
Expand|Select|Wrap|Line Numbers
  1. With AppExcel.ActiveWorkbook.PivotCaches
  2.     With .Add(SourceType:=xlDatabase, SourceData:=Datasheet.Range("A1:H" & LastRowNumber), Version:=3)
  3.         Set PTable = .CreatePivotTable(TableDestination:=CurrentSheet.Range("A6"), TableName:="AnalysisPivot", DefaultVersion:=3)
  4.     End With
  5. 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?
Apr 23 '09 #28

FishVal
Expert 2.5K+
P: 2,653
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?
Apr 23 '09 #29

P: 34
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.
Apr 23 '09 #30

FishVal
Expert 2.5K+
P: 2,653
You are welcome.

Best regards,
Fish.
Apr 23 '09 #31

NeoPa
Expert Mod 15k+
P: 31,306
Don't you just love those Eureka moments ;)

Very pleased to hear that's resolved Redman.
Apr 23 '09 #32

P: 1
@redman08
This topic saved my life.
Thanks :PPP
Dec 28 '11 #33

Post your reply

Sign in to post your reply or Sign up for a free account.