Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Create Excel Pivot in VBA with Access Table as source

Member
  
Join Date: Dec 2008
Posts: 34
#1: Jun 9 '09
I am writing some VBA to create an Excel pivot table in a new worksheet using an existing local table.

Because of volumes, I don't want to store the original data (on another sheet and hide it) within the workbook.

Any ideas for the "SourceType:=" and "SourceData:=" parameters, or an example of a working model?


Thanks.

NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,173
#2: Jun 9 '09

re: Create Excel Pivot in VBA with Access Table as source

I haven't worked with Pivot tables, but one of the nice things about Excel is the ease with which you can create code in (VBA) macros by recording your work.

I suggest you record a macro and set up a noddy pivot table using Access as a repository. You should find out easily enough from that what you need.

Let us know how you get on.
Member
  
Join Date: Dec 2008
Posts: 34
#3: Jun 9 '09

re: Create Excel Pivot in VBA with Access Table as source

I did record a 'pivot create' in Excel and transferred it, but when I ran the VBA code it didn't like it.

Re-reading what I orginally wrote, and just to clarify:

I'm writing VBA in Access, to run a module which will create the pivot (in Excel) using a table in the same database that the module will reside.

I can easily create the data in Excel and then pivot it, but I don't want to store all the data in another sheet because of high volumes. Ideally I just want the pivot with it linked to the table.

Hope that's clear, now.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,173
#4: Jun 9 '09

re: Create Excel Pivot in VBA with Access Table as source

Quote:

Originally Posted by redman08 View Post

I did record a 'pivot create' in Excel and transferred it, but when I ran the VBA code it didn't like it.

Have a look in Application Automation. It should mention that native (Excel) code is a different from that which runs from another application (EG. Access). There are default references in Excel which are not available in Access, so the code needs to refer to the explicitly (where it doesn't need to in Excel).
Quote:

Originally Posted by redman08 View Post

Re-reading what I orginally wrote, and just to clarify:

I'm writing VBA in Access, to run a module which will create the pivot (in Excel) using a table in the same database that the module will reside.

I can easily create the data in Excel and then pivot it, but I don't want to store all the data in another sheet because of high volumes. Ideally I just want the pivot with it linked to the table.

Hope that's clear, now.

I said earlier I have no direct idea about Pivot Tables.

Can you create one that links to an Access table? If so, I suggest you do so then examine the results (properties etc) for the bit that determines how it is connected.

I'm afraid I can only really point you in that direction. Someone else may be able to give more precise instructions, but I expect this should be enough.
Member
  
Join Date: Dec 2008
Posts: 34
#5: Jun 9 '09

re: Create Excel Pivot in VBA with Access Table as source

Thanks, I did create one, as you suggested with 'macro recording on', and then transposed the code to my Access module. I tred to filter out various bits of code in an attempt to make it run, but to no avail. I'll repeat the exercise and check properties, etc. in the morning. I have successfully created code for situations where I have built the data in one sheet, then created the pivot in another, but, as I said originally, because of the large volumes expected, and the fact that I will need to create a number of these pivots in the same workbook, referencing large quantities of data, I need to find a way of linking the original tables (easy enough manually) and produce a module to run the job from a macro, as automatically as possible.

Thanks for your input. The fight goes on.
Expert
  
Join Date: Jul 2008
Location: Maryland
Posts: 1,253
#6: Jun 9 '09

re: Create Excel Pivot in VBA with Access Table as source

PivotTable.SourceData Property
The code here will at least help you see what the SourceData is set to when you create a PivotTable. I'm still not sure you will be able to use external data without importing it into the workbook.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,173
#7: Jun 9 '09

re: Create Excel Pivot in VBA with Access Table as source

If you can create one manually (as you say) which references the original table (in Access), then it should be easy enough to grab the code recorded shouldn't it. I'm guessing you're having a problem with this (lack of Eureka etc) but I can't think why.
Member
  
Join Date: Dec 2008
Posts: 34
#8: Jun 9 '09

re: Create Excel Pivot in VBA with Access Table as source

Grabbing the code is easy enough. Making it work....that's the problem. There's lots of 'dummy' or null entries, double/triple sets of double-inverted commas, etc. I'll paste in the code tomorrow for you to see. Meanwhile, the search (for inspiration) goes on.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,173
#9: Jun 9 '09

re: Create Excel Pivot in VBA with Access Table as source

Quote:

Originally Posted by redman08 View Post

Making it work....that's the problem. There's lots of 'dummy' or null entries, double/triple sets of double-inverted commas, etc. I'll paste in the code tomorrow for you to see.

Sounds like a blast. We'll have a look tomorrow.
Member
  
Join Date: Dec 2008
Posts: 34
#10: Jun 10 '09

re: Create Excel Pivot in VBA with Access Table as source

Good morning!

Here's what was recorded:
Expand|Select|Wrap|Line Numbers
  1. Range("A7").Select
  2.     Workbooks("Book2").Connections.Add "18Wk RTT Reporting Data", "", Array( _
  3.         "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=Y:\PROVIDER SECONDARY CARE\PTL MONITORING\18 Weeks Outco" _
  4.         , _
  5.         "me Reporting\Databases\18Wk RTT Reporting Data.mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet" _
  6.         , _
  7.         " OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Globa" _
  8.         , _
  9.         "l Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=Fa" _
  10.         , _
  11.         "lse;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False" _
  12.         , ";Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"), Array( _
  13.         "T003 Clock Running Referrals"), 3
  14.     ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
  15.         ActiveWorkbook.Connections("18Wk RTT Reporting Data"), Version:= _
  16.         xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R7C1", _
Here are the Connection properties (not too dissimilar):
Expand|Select|Wrap|Line Numbers
  1. Provider=Microsoft.ACE.OLEDB.12.0;
  2. User ID=Admin;
  3. Data Source=Y:\PROVIDER SECONDARY CARE\PTL MONITORING\18 Weeks Outcome Reporting\Databases\18Wk RTT Reporting Data.mdb;
  4. Mode=Share Deny Write;
  5. Extended Properties="";
  6. Jet OLEDB:System database="";
  7. Jet OLEDB:Registry Path="";
  8. Jet OLEDB:Engine Type=5;
  9. Jet OLEDB:Database Locking Mode=0;
  10. Jet OLEDB:Global Partial Bulk Ops=2;
  11. Jet OLEDB:Global Bulk Transactions=1;
  12. Jet OLEDB:New Database Password="";
  13. Jet OLEDB:Create System Database=False;
  14. Jet OLEDB:Encrypt Database=False;
  15. Jet OLEDB:Don't Copy Locale on Compact=False;
  16. Jet OLEDB:Compact Without Replica Repair=False;
  17. Jet OLEDB:SFP=False;
  18. Jet OLEDB:Support Complex Data=False
Now then, I've been thinking..overnight...and possibly, we're thinking about this from the wrong angle. The code is on the Access database, as is the table (T003...etc) and we connect to Excel to build the worksheet/workbook, etc. The connection we have recorded IN EXCEL is a connection from Excel to Access, i.e. the otherway round from where we are going to be running the code.

I think we should be able to just pick-up the table as a recordset, or similar (because it will be local to the code) and use it that way.

.....or am I talking rubbish so early in the morning?
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,173
#11: Jun 10 '09

re: Create Excel Pivot in VBA with Access Table as source

Quote:

Originally Posted by redman08 View Post

Good morning!

Here's what was recorded:

Expand|Select|Wrap|Line Numbers
  1. ...
  2.         ActiveWorkbook.Connections("18Wk RTT Reporting Data"), Version:= _
  3.         xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R7C1", _

Unfortunately, you chopped off the code half way through a statement (last character is a continuation character _)
Quote:

Originally Posted by redman08 View Post

I think we should be able to just pick-up the table as a recordset, or similar (because it will be local to the code) and use it that way.

.....or am I talking rubbish so early in the morning?

I don't think that can work.
Member
  
Join Date: Dec 2008
Posts: 34
#12: Jun 10 '09

re: Create Excel Pivot in VBA with Access Table as source

Only the last line was missing. Here it is again in full:
Expand|Select|Wrap|Line Numbers
  1. Workbooks("Book1").Connections.Add "18Wk RTT Reporting Data", "", Array( _
  2.     "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=Y:\PROVIDER SECONDARY CARE\PTL MONITORING\18 Weeks Outco" _
  3.     , _
  4.     "me Reporting\Databases\18Wk RTT Reporting Data.mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet" _
  5.     , _
  6.     " OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Globa" _
  7.     , _
  8.     "l Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=Fa" _
  9.     , _
  10.     "lse;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False" _
  11.     , ";Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"), Array( _
  12.     "T003 Clock Running Referrals"), 3
  13. ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
  14.     ActiveWorkbook.Connections("18Wk RTT Reporting Data"), Version:= _
  15.     xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R7C1", _
  16.     TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,173
#13: Jun 10 '09

re: Create Excel Pivot in VBA with Access Table as source

I will look at this in detail shortly. There is some code to understand here.

In the mean time, please note the edit notes I've added to two of your posts in this thread. You're a full member now (congratulations), so you are expected to use the tags. I will need to issue an infraction if you fail to in future.

Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your profile options (Look near the bottom of the page).
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,173
#14: Jun 10 '09

re: Create Excel Pivot in VBA with Access Table as source

Right then. If we're still trying to answer the original question it seems that :
Expand|Select|Wrap|Line Numbers
  1. SourceType:=xlExternal
  2. SourceData:=ActiveWorkbook.Connections("18Wk RTT Reporting Data")
Clearly the Connection would have to be set up prior to setting this parameter.
Example code for that is also included in your last post.

Are there any parts of this that you still feel confuse you?
Member
  
Join Date: Dec 2008
Posts: 34
#15: Jun 10 '09

re: Create Excel Pivot in VBA with Access Table as source

I have tried various combinations in trying to establish a connection (obviously not my stringest suit) and then using it to create the pivot.

The current version looks like this:
Expand|Select|Wrap|Line Numbers
  1. Dim Excelconn As ADODB.Connection
  2. Set Excelconn = New ADODB.Connection
  3. With Excelconn
  4.    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Y:\PROVIDER SECONDARY CARE\PTL MONITORING\18 Weeks Outcome Reporting\Databases\18Wk RTT Reporting Data.mdb;Persist Security Info=False, Array([T003 Clock Running Referrals])"
  5. End With
Followed by:
Expand|Select|Wrap|Line Numbers
  1. With AppExcel.ActiveWorkbook.PivotCaches
  2.     With .Create(SourceType:=xlExternal, SourceData:=Excelconn, Version:=3)
  3.         Set PTable = .CreatePivotTable(TableDestination:=T003Sheet.Range("A" & T003HeadRow), TableName:="T003Pivot", DefaultVersion:=3)
  4.     End With
  5. End With
Currently, this produces a run-time error '1004' (Application/object-defined error) on the "With .Create(SourceType:=xlExternal......" line.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,173
#16: Jun 10 '09

re: Create Excel Pivot in VBA with Access Table as source

Quote:

Originally Posted by NeoPa View Post

You're a full member now (congratulations), so you are expected to use the tags. I will need to issue an infraction if you fail to in future.

I'm confused. Do you want to receive an infraction?
Member
  
Join Date: Dec 2008
Posts: 34
#17: Jun 10 '09

re: Create Excel Pivot in VBA with Access Table as source

I've taken the connection details from what was recorded and tried to apply them to my code. I'm new to "connections" so there may be a problem, there. Having set up the connection details, I've tried to run the code with the results as per my previous post.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,173
#18: Jun 11 '09

re: Create Excel Pivot in VBA with Access Table as source

You can assume I won't be paying much attention to your posts while you're clearly ignoring mine.
Reply

Tags
pivot< access, table, vba

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/other

Forums
Visit our community forums for general discussions and latest on Bytes

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 229,155 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved