Creating a PivotTable Programmatically

DotNetKicks.com — Sat, 26 Jul 2008 05:23:15 GMT

You've been kicked (a good thing) - Trackback from DotNetKicks.com

re: Creating a PivotTable Programmatically

Alexandre Brisebois — Fri, 01 Aug 2008 17:10:47 GMT

How can I loop through all the PivotFields of a Pivot table in C# ?

I currently need to find all the PivotFields of an already built PivotTable.

re: Creating a PivotTable Programmatically

andreww — Fri, 01 Aug 2008 22:07:47 GMT

Alexandre - you can use the PivotFields method to retrieve the PivotFields collection, and then iterate through it. The exact mechanism is slightly obscure, but pretty simple. Here's an example:

Excel.PivotFields fields = (Excel.PivotFields)this.pivotTable.PivotFields(missing);

int fieldCount = fields.Count;

StringBuilder builder = new StringBuilder();

for (int i = 1; i <= fieldCount; i++)

{

Excel.PivotField field = (Excel.PivotField)this.pivotTable.PivotFields(i);

builder.AppendLine(field.Name);

}

MessageBox.Show(builder.ToString());

re: Creating a PivotTable Programmatically

Alexandre Brisebois — Sat, 02 Aug 2008 00:12:35 GMT

thank you for the prompt reply,

I found out about the optional parameter while looking through msdn a couple of hours after asking here, but I still have one problem.

I now can find the column fields and the row fields.

If i understand how this works,

There is a root PivotField for the row and column, the subsequent PivotFields in each are then added as a child PivotField and so on. please correct me if i'm wrong.

I do not seem to be able to get the DataFields, "xlDataField" is there something special I must do to access these fields.

At the moment I am trying to crawl the pivot table so that I may be able to record what fields are in what Orientation. I am doing this so that I may create a PivotTable definition, which can be used at a later time to recreate the PivotTable.

Best regards,

re: Creating a PivotTable Programmatically

Alexandre Brisebois — Sat, 02 Aug 2008 15:23:50 GMT

for those who have the same questions as I've been having.

re: Creating a PivotTable Programmatically

andreww — Sat, 02 Aug 2008 21:54:33 GMT

Alexandre - the data, row, column etc fields are not exactly children of the PivotField. Rather, they are all PivotFields that form subsets of the PivotFields collection in the PivotTable. The simplest approach is to use the corresponding RowFields, DataFields etc collection properties of the PivotTable itself. For example:

StringBuilder builder = new StringBuilder();

builder.AppendLine("PivotFields:");

Excel.PivotFields pivotFields = (Excel.PivotFields)this.pivotTable.PivotFields(missing);

for (int i = 1; i <= pivotFields.Count; i++)

{

Excel.PivotField pivotField = (Excel.PivotField)this.pivotTable.PivotFields(i);

builder.AppendLine(pivotField.Name);

}

builder.AppendLine();

builder.AppendLine("ColumnFields:");

Excel.PivotFields columnFields = (Excel.PivotFields)this.pivotTable.get_ColumnFields(missing);

for (int i = 1; i <= columnFields.Count; i++)

{

Excel.PivotField columnField = (Excel.PivotField)this.pivotTable.get_ColumnFields(i);

builder.AppendLine(columnField.Name);

}

builder.AppendLine();

builder.AppendLine("RowFields:");

Excel.PivotFields rowFields = (Excel.PivotFields)this.pivotTable.get_RowFields(missing);

for (int i = 1; i <= rowFields.Count; i++)

{

Excel.PivotField rowField = (Excel.PivotField)this.pivotTable.get_RowFields(i);

builder.AppendLine(rowField.Name);

}

builder.AppendLine();

builder.AppendLine("DataFields:");

Excel.PivotFields dataFields = (Excel.PivotFields)this.pivotTable.get_DataFields(missing);

for (int i = 1; i <= dataFields.Count; i++)

{

Excel.PivotField dataField = (Excel.PivotField)this.pivotTable.get_DataFields(i);

builder.AppendLine(dataField.Name);

}

MessageBox.Show(builder.ToString());

re: Creating a PivotTable Programmatically

shreyas — Tue, 12 Aug 2008 09:44:35 GMT

How do i make this functionality work for SSAS OLAP Cube

re: Creating a PivotTable Programmatically

shreyas — Tue, 12 Aug 2008 16:58:40 GMT

Hi, I tried your code posted as “Creating a PivotTable Programmatically”, it works perfectly. I need to get exactly similar functionality with OLAP Cube. Below is the code I tried but I get error at line marked as (*****), when i try to add Pivot Table. I get exception as “Exception from HRESULT: 0x800A03EC”.

Microsoft.Office.Interop.Excel.Application app;

app = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook wkbk = (Microsoft.Office.Interop.Excel.Workbook)app.Workbooks.Add(Type.Missing);

Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)wkbk.ActiveSheet;

string connection = @"OLEDB; Provider=msolap; Integrated Security=SSPI ; Datasource=localhost; Initial Catalog=MSLMKTG_DemoCube; UID = Administrator; Password = cybage@123";

string command = "CubeUserMSLMKTG";

Microsoft.Office.Interop.Excel.PivotCache pivotCache;

pivotCache = wkbk.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlExternal,Type.Missing);

pivotCache.Connection = connection;

pivotCache.MaintainConnection = true;

pivotCache.CommandType = Microsoft.Office.Interop.Excel.XlCmdType.xlCmdCube;

pivotCache.CommandText = command;

Microsoft.Office.Interop.Excel.PivotTables pvtTables = (Microsoft.Office.Interop.Excel.PivotTables)sheet.PivotTables(Type.Missing);

(*****)Microsoft.Office.Interop.Excel.PivotTable pvt = pvtTables.Add(pivotCache,sheet.Cells[1,1], "PivotTable1", true, Type.Missing);

Please help urgently

Thanks,

Shreyas

re: Creating a PivotTable Programmatically

shreyas — Tue, 12 Aug 2008 17:16:55 GMT

Hi,

once again mailing you.

In my previos code if i change my adding pivot code to

Microsoft.Office.Interop.Excel.PivotTable pvt = pivotCache.CreatePivotTable(sheet.Cells[8, 3], "PivotTable1", Type.Missing, Type.Missing);

I get error something as OLE DB error saying problem in opening dialog box and the second time execute the same code i get the error as "Exception from HRESULT: 0x800A03EC"

re: Creating a PivotTable Programmatically

amrut — Sun, 17 Aug 2008 03:51:16 GMT

Im not sure if this was answered in the previous codes as I am new to VB. If i were to try to access all the datafields in my pivotfield to make changes to formatting how could i do that?

Also I want to remove the "sum of" out of each datafield. So it says Billpayers instead of Sum of Billpayers. Is there a way to do that to all datafields at once?

re: Creating a PivotTable Programmatically

Shreyas — Mon, 18 Aug 2008 12:36:50 GMT

Has any one done this.......see the above threads :)

Thanks,

Shreyas

re: Creating a PivotTable Programmatically

Teme — Fri, 22 Aug 2008 15:43:36 GMT

Thanks for this blog post!

To Shreyas.

It can be used to create connection to OLAP cube.

Connection string I used in test case was:

"OLEDB;Provider=MSOLAP.1; Location=ServerName;Connect Timeout=60; Initial Catalog=Analysis Services ProjectTest;Client Cache Size=25";"

Properties:

pivotCache.CommandText = "name of the cube";

pivotCache.CommandType = XlCmdType.xlCmdCube;

And assignment of fields:

pivotTable.CubeFields["Name of cube dimension"].Orientation = XlPivotFieldOrientation.xlRowField;

re: Creating a PivotTable Programmatically

Teme — Fri, 22 Aug 2008 15:43:59 GMT

Thanks for this blog post!

To Shreyas.

It can be used to create connection to OLAP cube.

Connection string I used in test case was:

"OLEDB;Provider=MSOLAP.1; Location=ServerName;Connect Timeout=60; Initial Catalog=Analysis Services ProjectTest;Client Cache Size=25";"

Properties:

pivotCache.CommandText = "name of the cube";

pivotCache.CommandType = CmdType.xlCmdCube;

And assignment of fields:

pivotTable.CubeFields["Name of cube dimension"].Orientation = XlPivotFieldOrientation.xlRowField;

re: Creating a PivotTable Programmatically

Gaby — Thu, 28 Aug 2008 20:21:00 GMT

Hello Andrew,

I'm having a couple of issues with a PivotTable:

1) I get the following error when setting the Orientation property of the fifth PivotField: "Unable to set the Orientation property of the PivotField class". It seems that the error is related to the number of fields, not to the type of orientation being used.

2) I don't know how to put the data fields as columns (as when you right-click the "Data" cell, select Order and choose "Move to Column"). Can this be accomplished programatically?

Thanks!

re: Creating a PivotTable Programmatically

Alexandre Brisebois — Fri, 29 Aug 2008 10:07:10 GMT

Dear Gaby,

I was facing the exact same problem a few weeks ago.

This is what I found:

You need to create the pivot table in steps. The first of these steps is add all the data fields:

if (orientation.Equals("xlDataField"))

{

string source = field.Attribute("SourceName").Value;

string function = field.Attribute("Function").Value;

pivot.AddDataField(pivot.PivotFields(source), name, SelectFunction(function));

}

Then set the orientation of the PivotFields. Be sure not to set the orientation of the "Data" named PivotField.

Once all the fields have been set, you may then set the orientation of the "Data" field. You need to do this last since by placing PivotFields the value PivotField is automatically generated.

Please correct me if I'm wrong.

Now you can set the Position and Caption of the fields.

This is how i went about it. Let me know if this works.

Best regards,

Alexandre Brisebois

re: Creating a PivotTable Programmatically

Gaby — Sat, 30 Aug 2008 01:01:31 GMT

Hello Alexandre,

It didn't work. It seems that error # 1 appears because there are too many rows. I'll try to sort that out later.

Do you have any idea on how to accomplish # 2 ("Move to Columns" programatically)?

Regards,

Gaby

re: Creating a PivotTable Programmatically

Gaby — Sat, 30 Aug 2008 01:38:04 GMT

I made it work with this line

table1.PivotFields("Data").Orientation = Excel.XlPivotFieldOrientation.xlColumnField

before setting the RowColumn that caused the problem with too many rows.

Thanks.

re: Creating a PivotTable Programmatically

Teme need furhter help — Mon, 01 Sep 2008 10:15:07 GMT

Hi Teme,

Thanks...i tried your suggested code.

I am getting error when assigning dimensions to the pivot table. The error is "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"

pvt.CubeFields["Data Source Name"].Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField;

here "Data Source Name" is the name of the dimension but it comes under dimension Dim Source, it is a hierachy under this dimension.

Would be helpful if you can send me your working code

My mail id is bhagatshreyas@gmail.com

Thanks,

Shreyas

re: Creating a PivotTable Programmatically

Teme...finally it worked — Tue, 02 Sep 2008 07:50:05 GMT

Thanks Teme....finally it worked :)

Thanks,

Shreyas

re: Creating a PivotTable Programmatically

Teme..need help — Tue, 02 Sep 2008 13:17:39 GMT

Hi Teme,

I am done with the implementation but when i deploy it on IIS server, nothing is getting generated in the excel.Please share your contact information.

Thanks,

Shreyas

re: Creating a PivotTable Programmatically

Alexandre Brisebois — Tue, 02 Sep 2008 13:32:50 GMT

Shreyas,

Why are you generating these on IIS, this might turn out to be very resource hungry?

Have you installed the VSTO components on the server ? Haven you Installed Excel 2007 on the server ?

Best regards,

Alexandre Brisebois

re: Creating a PivotTable Programmatically

Alexandre Brisebois — Tue, 02 Sep 2008 13:35:24 GMT

I have been working with PivotCharts,

and I'm trying to wrap my head around getting the chart layout. I have only found the ApplyLayout [ ApplyLayout(1,Type.Missing); ] method of the Chart Object. Is if possible for me to retrieve this integer value ?

Best regards,

Alexandre Brisebois

re: Creating a PivotTable Programmatically

Microsoft.Office.Interop.Excel help — Tue, 02 Sep 2008 16:50:59 GMT

Hi,

when i run my code in Visual studio it works perfectly fine...but when i deploy it on IIS server then my excel sheet doesn't show anything.How do get this done.Some where i read about Microsoft.Office.Interop.Excel which IIS does not support.Please let me know the detail steps.

Thanks,

Shreyas

re: Creating a PivotTable Programmatically

Teme — Wed, 03 Sep 2008 09:45:34 GMT

Sorry haven't had the time to check replies. It can be done on IIS. Have you included all the dlls that needed to Bin folder of website? I had Microsoft.Office.Interop.Excel.dll and office.dll.

For first time I tried it on IIS I ran to the language version bug. Before using Excel operations set your culture to US.

System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

You must disable all popup questions of Excel because IIS runs it without console. So no one can reply to question -> process hangs.

_exelApp.DisplayAlerts = false;

Have you set user rights for folders that are used?

-Teme

For detailed questions:

teme1011@gmail.com

re: Creating a PivotTable Programmatically

andreww — Tue, 09 Sep 2008 05:37:14 GMT

Alexandre - I don't believe there is any way to retrieve the chart layout. When you call ApplyLayout, the integer you pass as the first parameter is an identifier into the list of possible layouts for this chart type (you can see these values in the Design tab of the Ribbon). These layouts affect one or more values (such as the existence and/or position of the Legend) - not necessarily just a single property.

re: Creating a PivotTable Programmatically

Alexandre Brisebois — Tue, 09 Sep 2008 10:17:57 GMT

Andrew,

after some time i decided on setting the '1' layout for all my charts and have the user customize it before printing it out.

Thanks for the response. I do think that this would be an interesting addition to future versions.

re: Creating a PivotTable Programmatically

Helen — Thu, 18 Sep 2008 16:11:16 GMT

Hi,

If I already have a dataset with the data, how can I create the pivot table?

re: Creating a PivotTable Programmatically

Helen — Fri, 19 Sep 2008 12:41:54 GMT

I tried the code and works but when I tried the line:

Excel.PivotTable pivotTable = pivotTables.Add(

pivotCache, Globals.ThisAddIn.Application.ActiveCell, "PivotTable1",

missing, missing);

the programs shows me an SQL connection windwos ask me for a databaseName, id an password?

Why??

re: Creating a PivotTable Programmatically

andreww — Tue, 23 Sep 2008 20:06:56 GMT

Helen - the code I posted specifically uses a SQL connection, so if you use this code, the PivotCache is set up to use a SQL database. Are you saying that you have the data in an Excel Range, and that you want to use that data for your PivotTable? If so, you can do so very simply. For example:

Excel.PivotCache pivotCache =

this.Application.ActiveWorkbook.PivotCaches().Add(

Excel.XlPivotTableSourceType.xlDatabase,

(Excel.Range)sheet.get_Range("A1", "C17"));

re: Creating a PivotTable Programmatically

Connie LeMaster — Tue, 18 Nov 2008 02:28:09 GMT

How can I get to the PivotItems of the individual PivotFields objects? I am trying to convert some VBA logic to C# .NET. In the VBA code I could loop through the PivotItems that were created for each PivotField and make them Visible or not depending on the caption. I cannot seem to find the right object/method to get to those same PivotItem objects in .NET VSTO.

re: Creating a PivotTable Programmatically

andreww — Tue, 18 Nov 2008 03:16:34 GMT

Connie - you can simply iterate through the collection of PivotItems in C# in pretty much the same way you can do it in VBA - after all, you're still using the same exposed Excel object model. For example:

StringBuilder builder = new StringBuilder();

builder.AppendLine("PivotItems:");

Excel.PivotItems items = (Excel.PivotItems)

((Excel.PivotField)this.pivotTable.PivotFields("2004")).PivotItems(missing);

for (int i = 1; i < items.Count; i++)

{

Excel.PivotItem item = (Excel.PivotItem)items.Item(i);

builder.AppendLine(String.Format("{0}", item.Value));

}

MessageBox.Show(builder.ToString());

re: Creating a PivotTable Programmatically

Connie LeMaster — Wed, 19 Nov 2008 00:14:52 GMT

Thanks so much! That worked like a charm. I did end up using it as a foreach (PivotItem item in items) but the principle is the same. The collections that are available in the Excel .NET tools feel a little different than the normal .NET collections. I always forget to try to use the Type.Missing object in place of an index.

Thanks again!

re: Creating a PivotTable Programmatically

Dirk — Fri, 28 Nov 2008 02:06:39 GMT

Andrew - great article!

I like to create a Pivot table from a SSAS cube and wonder if you could post the complete code here. I suppose you have it anyways.

Also I was able to get the MDX scripts that Excel generates and saved those. Now, what would I have to do to recreate a Pivot table with that. Would that be the COMMAND text?

Your help in this matter is greatly appreciated.

Thanks, Dirk

re: Creating a PivotTable Programmatically

Dirk — Fri, 28 Nov 2008 02:08:56 GMT

Dirk again. I missed to mention that I have created an Excel 2007 Add-in that I like to use to create these Pivot tables based on the MDX queries saved beforehand.

re: Creating a PivotTable Programmatically

andreww — Fri, 28 Nov 2008 21:30:42 GMT

Dirk - I'm afraid I don't know the answer to your question, although I suspect your best bet would be to use an AdomdCommand from the AnalysisServices library.

Have you looked at the OLAP PivotTable Extensions utilities on codeplex?

http://www.codeplex.com/OlapPivotTableExtend

re: Creating a PivotTable Programmatically

Dirk — Fri, 28 Nov 2008 23:06:04 GMT

Andrew -

thanks for the quick feedback. It appears that Excel was not build to support my idea.

Thanks,

Dirk

re: Creating a PivotTable Programmatically

Dirk — Sat, 29 Nov 2008 16:59:41 GMT

Andrew -

could you please take a quick look at my code and tell me why it is not working. I all it from within an Excel Add-in.

I really would appreciate your help.

Thanks in advance,

Dirk

Sub CreateOLAPPivotTable()

'Declare variables

Dim objMyPivotCache As Excel.PivotCache

Dim objMyPivotTable As Excel.PivotTable

Dim App As Excel.Application

App = Globals.ThisAddIn.Application

'Create PivotCache

objMyPivotCache = App.ActiveWorkbook.PivotCaches.Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlExternal)

'Retrieve data

With objMyPivotCache

.Connection = "OLEDB;Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Data Source=server;Initial Catalog=DuPont_EMEA"

.CommandText = "server cube FreeMining"

.CommandType = Excel.XlCmdType.xlCmdCube

End With

'Create PivotTable

objMyPivotTable = App.ActiveSheet.PivotTables.Add( _

PivotCache:=objMyPivotCache, _

'I believe the RANGE is crashing?

'Exception from HRESULT: 0x800A03EC

TableDestination:=App.Range("A1"), _

TableName:="OLAP PivotTable")

objMyPivotTable.CubeFields("[REVIEWPERIOD]").Orientation = Microsoft.Office.Core.XlPivotFieldOrientation.xlDataField

objMyPivotTable.CubeFields("[DMDUNIT]").Orientation = Microsoft.Office.Core.XlPivotFieldOrientation.xlRowField

End Sub

re: Creating a PivotTable Programmatically

andreww — Tue, 02 Dec 2008 10:28:34 GMT

Dirk - I've looked at your code and I can't see anything wrong with it. TBH you might be better off posting OLAP questions to one of the Excel team's blogs, for instance: http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx

re: Creating a PivotTable Programmatically

Boris — Fri, 05 Dec 2008 06:15:49 GMT

Hi,

How can I create the pivotcache from DataSet?

the dataset is created in the code, not from the external database.

Thanks,

Boris

re: Creating a PivotTable Programmatically

John — Tue, 09 Dec 2008 04:16:26 GMT

How do I programatically change the SQL Server connection used by an existing pivot table?

re: Creating a PivotTable Programmatically

John — Wed, 10 Dec 2008 05:23:54 GMT

The way you programatically change the connection is like this:

http://support.microsoft.com/kb/327572

The support article only updates the server name. If you want to change the whole string, it must be in the following format. Otherwise you'll get a cryptic 0x800A03EC exception.

ODBC;DRIVER=SQL Server;SERVER=<<yourserver>>;DATABASE=<<yourdatabase>>;Network=DBMSSOCN;Trusted_Connection=Yes

re: Creating a PivotTable Programmatically

John — Wed, 10 Dec 2008 05:27:09 GMT

The above connection string was truncated. Here it is again.

ODBC;DRIVER=SQL Server;SERVER=<<your server>>;DATABASE=<<your database>>;Network=DBMSSOCN;Trusted_Connection=Yes

re: Creating a PivotTable Programmatically

naren — Fri, 26 Dec 2008 10:34:01 GMT

how to remove/hide datafields from a pivot table using vba code??

re: Creating a PivotTable Programmatically

andreww — Sat, 27 Dec 2008 04:36:38 GMT

Boris - you can create a PivotCache from an ADO Recordset, using the PivotCache.Recordset property. So, if you're starting from an ADO.NET DataSet, you can convert that to a Recordset first. See here for details: http://support.microsoft.com/kb/316337

re: Creating a PivotTable Programmatically

andreww — Sat, 27 Dec 2008 05:39:16 GMT

naren - to show/hide pivot fields, you can simply use the Visible property exposed by the Excel object model. For example:

Excel.PivotFields rowFields = (Excel.PivotFields)this.pivotTable.get_RowFields(missing);

for (int r = 1; r <= rowFields.Count; r++)

{

Excel.PivotField rowField = (Excel.PivotField)this.pivotTable.get_RowFields(r);

Excel.PivotItems items = (Excel.PivotItems)rowField.PivotItems(missing);

for (int i = 1; i < items.Count; i++)

{

Excel.PivotItem item = (Excel.PivotItem)items.Item(i);

string s = item.Value;

if (s.StartsWith("J"))

{

item.Visible = false;

}

re: Creating a PivotTable Programmatically

Sayantan Samanta — Tue, 10 Feb 2009 16:59:13 GMT

How do i publish a pivot by code to a sharepoint site.In excel 2007

re: Creating a PivotTable Programmatically

andreww — Tue, 10 Feb 2009 22:24:38 GMT

Sayantan - this is really a question about usage of Excel and SharePoint, not really related to programmatic development of pivot tables. For the best answer, please post your question to one of the Excel and/or SharePoint forums: http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.excel.programming&lang=en&cr=US

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.excel&cat=en_US_e064c9ec-14bf-4ef7-ba07-6a1970559f64&lang=en&cr=US

re: Creating a PivotTable Programmatically

adolf garlic — Wed, 08 Apr 2009 17:31:42 GMT

Does this work ok with excel 2003?

Can the same technique be used for non pivot query tables?

re: Creating a PivotTable Programmatically

andreww — Thu, 09 Apr 2009 04:58:49 GMT

adolf - yes, the general behavior is the same, although ome specific features have obviously changed from Excel 2003 to Excel 2007. For example, Excel 2003 did not provide ShowTableStyleRowStripes or TableStyle2 properties, so you'd have to use the similar TableStyle property instead, eg:

//this.pivotTable.ShowTableStyleRowStripes = true;

//this.pivotTable.TableStyle2 = "PivotStyleLight1";

this.pivotTable.TableStyle = "PivotStyleLight1";

re: Creating a PivotTable Programmatically

ronaldfer747 — Thu, 16 Apr 2009 18:46:53 GMT

Ho do I use a table from a Dataset as a source data in a pivot table properties?

re: Creating a PivotTable Programmatically

andreww — Fri, 17 Apr 2009 08:17:34 GMT

ronaldfer747 - see my previous reply to Boris, viz:

You can create a PivotCache from an ADO Recordset, using the PivotCache.Recordset property. So, if you're starting from an ADO.NET DataSet, you can convert that to a Recordset first. See here for details: http://support.microsoft.com/kb/316337

re: Creating a PivotTable Programmatically

Matt — Wed, 29 Apr 2009 01:21:08 GMT

Did anyone have a solution for shreyas HRESULT: 0x800A03EC error? I'm having the same problem and have been unable to solve it. I believe the error has to do with the second parameter in PivotTables.Add() which is the tableDefinition. Application.ActiveCell doesn't seem to work and any other explicit cell location also doesn't work.

Thanks!

re: Creating a PivotTable Programmatically

Artur — Fri, 29 May 2009 15:10:53 GMT

Hi!

Great article! I habe build a Cube PivotTable i can browse any PivotItem. Thats fine byu i'm not able to set any PivotFilter and also not able so set some attributes visible false. Has anyone an idee what i'm doing wrong.

I got every time the same HRSEULT Error like Matt.

Thx for any help

re: Creating a PivotTable Programmatically

Christophe T. Chavey — Sun, 31 May 2009 15:37:22 GMT

Hi, thank you for your great article.

Someone know how can I hide the connection string from the "user's eyes"? The PivotTable continue working, updating data from database etc. but I must hide the connection string (or encrypt it) because the user can't see it (the credentials used to connect to database). If he go to the datasource's properties he can see what user and password was used to connect to the database, or if I can denied access to this property's dialog?

Thanks for any help.

re: Creating a PivotTable Programmatically

R. Keeton — Thu, 25 Jun 2009 15:39:00 GMT

I have a pivot table that is populated from an Oracle database thru a rather cumbersome query. I would like to use the raw data in the pivot table in a custom function to summarize the data. The majority of the time, the data population exceeds 65,536, which makes it impossible to show the detailed records. Does anybody know how to access the data from the pivot table memory?

re: Creating a PivotTable Programmatically

andreww — Thu, 25 Jun 2009 21:09:42 GMT

R.Keeton - sorry, I don't know the answer, and if you don't get a response from anyone else on this blog, I suggest you re-post to the Excel team's blog, eg:

http://blogs.msdn.com/excel/archive/tags/PivotTables/default.aspx

re: Creating a PivotTable Programmatically

joeaxe — Mon, 13 Jul 2009 12:58:11 GMT

Thanks andreww for this great article! It helped me to build a pivot table programatically on my local machine but when I publish the project on my server and try to do the same from server side it gives me a blank excel file. Is this due to MSQuery permissions or what? Did anybody had the same issue?

Thanks in advance for any help you can provide me!

re: Creating a PivotTable Programmatically

Rolando Granera — Wed, 15 Jul 2009 00:50:39 GMT

Hello, you help me?

andreww

I need not show options in PivoteTable; Data; connection properties to prevent the connection string can not be viewed by the user. You might tell me how to do

re: Creating a PivotTable Programmatically

joeaxe — Wed, 22 Jul 2009 19:31:10 GMT

Solved! The problem was due to the lack of permissions. The solution was to define local impersonation of the published site and voilá!