The code is less than ideal, and is bit dirty, but turns MDX-based cellsets into pivottables: - Uses selected pivot to extract MDX and connection (you can replace with your server-side stuff) - Uses your GetDataTable() to transform cellset to data table - Uses found code to turn datatable into ADODB recordset - Creates pivotchache and then pivot…

CellsetToPivotTable.cs

The code is less than ideal, and is bit dirty, but turns MDX-based cellsets into pivottables:

- Uses selected pivot to extract MDX and connection (you can replace with your server-side stuff)
- Uses your GetDataTable() to transform cellset to data table
- Uses found code to turn datatable into ADODB recordset
- Creates pivotchache and then pivottable

        private void InsertPivotAsRecordset_Click(object sender, RibbonControlEventArgs e)
        {
            //down and dirty----
            //get mdx from selected pivot
            //execute cellset
            //flatten
            //transform to datatable
            //set to recordsource of pittable

            try
            {
                CellSet cs = GetPivotValues();
                System.Data.DataTable dt = getDataTable(cs);
                ADODB.Recordset rst = ConvertToRecordset(dt);

                //create pivot table
                Workbook currWorkbook = (Workbook)Globals.ThisAddIn.Application.ActiveWorkbook;
                Microsoft.Office.Interop.Excel.Worksheet sht = (Microsoft.Office.Interop.Excel.Worksheet)currWorkbook.Sheets.Add(Missing.Value,Missing.Value,Missing.Value,Missing.Value);
                Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)sht.Cells[1, 1];

                InsertPivotTable(rst, rng);
            }
            catch(Exception ex)
            {
                Forms.MessageBox.Show(ex.Message);
            }
        }

        public CellSet GetPivotValues()
        {
            //gets location of cell 
            Range rng = Globals.ThisAddIn.Application.ActiveCell;
            CellSet cs = null;

            try
            {
                //tests if pivot cell 
                string cellTypeTest = rng.PivotCell.PivotCellType.ToString();
                if (cellTypeTest.Contains("Pivot"))
                {

                    PivotTable pvt = rng.PivotTable;

                    string MdxString = pvt.MDX;
                    string connection = "Data Source=ServerName;Initial Catalog=CubeName;Integrated Security=SSPI"; ; //pvt.PivotCache().Connection.ToString();
                    connection = connection.Replace("ServerName", ControlPanel.ReaderCollection[0].Host);
                    connection = connection.Replace("CubeName", ControlPanel.ReaderCollection[0].CubeName);

                    //get cellsset
                    AdomdConnection connADOMD = new AdomdConnection(connection);

                    try
                    {
                        connADOMD.Open();

                        AdomdCommand cubeCommand = new AdomdCommand(MdxString, connADOMD);
                        cs = cubeCommand.ExecuteCellSet();
                    }
                    catch
                    {
                    }
                }
            }
            catch (System.Runtime.InteropServices.COMException ex)
            {
                Forms.MessageBox.Show(String.Format("Cursor must be a pivot data cell: {0}", ex.Message));
            }
            catch (Exception ex)
            {
                Forms.MessageBox.Show(String.Format("Unknown Error: {0}", ex.Message));
            }

            return cs;
        }

        private System.Data.DataTable getDataTable(CellSet cs)
        {
            if (cs.Axes.Count < 2)
                return new System.Data.DataTable();

            System.Data.DataTable dt = new System.Data.DataTable();
            TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;
            foreach (Tuple tc in tuplesOnColumns)
            {
                foreach (Member m in tc.Members)
                {
                    if (!dt.Columns.Contains(m.LevelName))
                        dt.Columns.Add(m.LevelName);
                }
            }
            TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;
            foreach (Tuple tr in tuplesOnRows)
            {
                foreach (Member m in tr.Members)
                {
                    if (!dt.Columns.Contains(m.LevelName))
                        dt.Columns.Add(m.LevelName);
                }
            }
            dt.Columns.Add("MEASURE", Type.GetType("System.Decimal"));
            DataRow def = dt.NewRow();
            for (int row = 0; row < tuplesOnRows.Count; row++)
            {
                Tuple tr = tuplesOnRows[row];

                for (int col = 0; col < tuplesOnColumns.Count; col++)
                {
                    Tuple tc = tuplesOnColumns[col];
                    DataRow dr = dt.NewRow();
                    dt.Rows.Add(dr);
                    foreach (Member m in tc.Members)
                    {
                        def[m.LevelName] = dr[m.LevelName] = m.Caption;
                    }

                    foreach (Member m in tr.Members)
                    {
                        def[m.LevelName] = dr[m.LevelName] = m.Caption;
                    }

                    foreach (DataColumn dc in dt.Columns)
                    {
                        dr[dc.ColumnName] = def[dc.ColumnName];
                    }

                    if (cs.Cells[col, row].Value == null)
                        dr["MEASURE"] = System.DBNull.Value;
                    else
                        dr["MEASURE"] = cs.Cells[col, row].Value;
                }
            }

            return dt;
        }

        private ADODB.Recordset ConvertToRecordset(System.Data.DataTable inTable)
        {
            ADODB.Recordset result = new ADODB.Recordset();
            result.CursorLocation = ADODB.CursorLocationEnum.adUseClient;

            ADODB.Fields resultFields = result.Fields;
            System.Data.DataColumnCollection inColumns = inTable.Columns;

            foreach (DataColumn inColumn in inColumns)
            {
                resultFields.Append(inColumn.ColumnName, TranslateType(inColumn.DataType), inColumn.MaxLength, ADODB.FieldAttributeEnum.adFldIsNullable, null);
            }

            result.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic,-1);

            foreach (DataRow dr in inTable.Rows)
            {
                result.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);

                for (int columnIndex = 0; columnIndex <= inColumns.Count - 1; columnIndex++)
                {
                    resultFields[columnIndex].Value = dr[columnIndex];
                }
            }

            return result;
        }

        private ADODB.DataTypeEnum TranslateType(Type columnType)
        {
            switch (columnType.UnderlyingSystemType.ToString())
            {
                case "System.Boolean":
                    return ADODB.DataTypeEnum.adBoolean;

                case "System.Byte":
                    return ADODB.DataTypeEnum.adUnsignedTinyInt;

                case "System.Char":
                    return ADODB.DataTypeEnum.adChar;

                case "System.DateTime":
                    return ADODB.DataTypeEnum.adDate;

                case "System.Decimal":
                    return ADODB.DataTypeEnum.adCurrency;

                case "System.Double":
                    return ADODB.DataTypeEnum.adDouble;

                case "System.Int16":
                    return ADODB.DataTypeEnum.adSmallInt;

                case "System.Int32":
                    return ADODB.DataTypeEnum.adInteger;

                case "System.Int64":
                    return ADODB.DataTypeEnum.adBigInt;

                case "System.SByte":
                    return ADODB.DataTypeEnum.adTinyInt;

                case "System.Single":
                    return ADODB.DataTypeEnum.adSingle;

                case "System.UInt16":
                    return ADODB.DataTypeEnum.adUnsignedSmallInt;

                case "System.UInt32":
                    return ADODB.DataTypeEnum.adUnsignedInt;

                case "System.UInt64":
                    return ADODB.DataTypeEnum.adUnsignedBigInt;
            }

            return ADODB.DataTypeEnum.adVarChar;
        }

        private void InsertPivotTable(ADODB.Recordset rst, Range SelectedCell)
        {
            Workbook currWorkbook = (Workbook)SelectedCell.Application.ActiveWorkbook;
            PivotCache pvtCache = default(PivotCache);
            PivotTable pvtTable = default(PivotTable);

            try
            {
                pvtCache = currWorkbook.PivotCaches().Create(XlPivotTableSourceType.xlExternal,Missing.Value, XlPivotTableVersionList.xlPivotTableVersion12);
                pvtCache.Recordset = rst;

                pvtTable = pvtCache.CreatePivotTable(SelectedCell, "Temp", Missing.Value, XlPivotTableVersionList.xlPivotTableVersion12);
            }
            catch(Exception ex)
            {
                Forms.MessageBox.Show(ex.Message);
            }
            finally
            {
            }
        }