Create Excel Pivot in VBA with Access Table as source | Member | | Join Date: Dec 2008
Posts: 34
| | |
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.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | 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
| | | 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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: Create Excel Pivot in VBA with Access Table as source
Quote:
Originally Posted by redman08  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 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
| | | 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,176
| | | 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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | 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
| | | 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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: Create Excel Pivot in VBA with Access Table as source
Quote:
Originally Posted by redman08 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
| | | re: Create Excel Pivot in VBA with Access Table as source
Good morning!
Here's what was recorded: - Range("A7").Select
-
Workbooks("Book2").Connections.Add "18Wk RTT Reporting Data", "", Array( _
-
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=Y:\PROVIDER SECONDARY CARE\PTL MONITORING\18 Weeks Outco" _
-
, _
-
"me Reporting\Databases\18Wk RTT Reporting Data.mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet" _
-
, _
-
" OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Globa" _
-
, _
-
"l Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=Fa" _
-
, _
-
"lse;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False" _
-
, ";Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"), Array( _
-
"T003 Clock Running Referrals"), 3
-
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
-
ActiveWorkbook.Connections("18Wk RTT Reporting Data"), Version:= _
-
xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R7C1", _
Here are the Connection properties (not too dissimilar): - Provider=Microsoft.ACE.OLEDB.12.0;
-
User ID=Admin;
-
Data Source=Y:\PROVIDER SECONDARY CARE\PTL MONITORING\18 Weeks Outcome Reporting\Databases\18Wk RTT Reporting Data.mdb;
-
Mode=Share Deny Write;
-
Extended Properties="";
-
Jet OLEDB:System database="";
-
Jet OLEDB:Registry Path="";
-
Jet OLEDB:Engine Type=5;
-
Jet OLEDB:Database Locking Mode=0;
-
Jet OLEDB:Global Partial Bulk Ops=2;
-
Jet OLEDB:Global Bulk Transactions=1;
-
Jet OLEDB:New Database Password="";
-
Jet OLEDB:Create System Database=False;
-
Jet OLEDB:Encrypt Database=False;
-
Jet OLEDB:Don't Copy Locale on Compact=False;
-
Jet OLEDB:Compact Without Replica Repair=False;
-
Jet OLEDB:SFP=False;
-
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?
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: Create Excel Pivot in VBA with Access Table as source
Quote:
Originally Posted by redman08 Good morning!
Here's what was recorded:
- ...
-
ActiveWorkbook.Connections("18Wk RTT Reporting Data"), Version:= _
-
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 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
| | | re: Create Excel Pivot in VBA with Access Table as source
Only the last line was missing. Here it is again in full: - Workbooks("Book1").Connections.Add "18Wk RTT Reporting Data", "", Array( _
-
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=Y:\PROVIDER SECONDARY CARE\PTL MONITORING\18 Weeks Outco" _
-
, _
-
"me Reporting\Databases\18Wk RTT Reporting Data.mdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet" _
-
, _
-
" OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Globa" _
-
, _
-
"l Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=Fa" _
-
, _
-
"lse;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False" _
-
, ";Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False"), Array( _
-
"T003 Clock Running Referrals"), 3
-
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
-
ActiveWorkbook.Connections("18Wk RTT Reporting Data"), Version:= _
-
xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R7C1", _
-
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | 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).
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | 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 : - SourceType:=xlExternal
-
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
| | | 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: - Dim Excelconn As ADODB.Connection
-
Set Excelconn = New ADODB.Connection
-
With Excelconn
-
.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])"
-
End With
Followed by: - With AppExcel.ActiveWorkbook.PivotCaches
-
With .Create(SourceType:=xlExternal, SourceData:=Excelconn, Version:=3)
-
Set PTable = .CreatePivotTable(TableDestination:=T003Sheet.Range("A" & T003HeadRow), TableName:="T003Pivot", DefaultVersion:=3)
-
End With
-
End With
Currently, this produces a run-time error '1004' (Application/object-defined error) on the "With .Create(SourceType:=xlExternal......" line.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | re: Create Excel Pivot in VBA with Access Table as source
Quote:
Originally Posted by NeoPa 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
| | | 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.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,730
| | | 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.
|  |
Similar Microsoft Access / VBA 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 226,471 network members.
|
