Create Excel Pivot in VBA with Access Table as source

[redman08]

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]

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.

[redman08]

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]

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).

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.

[redman08]

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.

[ChipR]

PivotTable.Sour ceData 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]

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.

[redman08]

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]

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.

[redman08]

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..overn ight...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]

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 _)

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.

[redman08]

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

[NeoPa]

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 (congratulation s), 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]

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?

[redman08]

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(SourceT ype:=xlExternal ......" line.