Programatically editing an Excel 2013 Powerpivot Datafeed Connection

Question

I have an excel 2013 powerpivot document... that connects to a wcfdataservice that i host... data connectivity works fine, all data is brought back and populated into my powerpivot charts/tables succesfully.

The data is hosted at Data Feed URL that looks like this.:

http://mydomain/Reporting/WcfDataService1.svc/

I have dozens of clients, that will each need to receive this same document... but with the DataFeedURL edited to point to their hosted data service... each one will have a different URL... such as

http://Client1/Reporting/WcfDataService1.svc/ 

http://Client2/Reporting/WcfDataService1.svc/

Long story short... I wish for clients to use a C# Windows Form application of mine, that will have a reference to the template PowePivot Report... and when the user gets done setting up various information regarding their company... i need to programatically save a version of the PowerPivot report that contains an updated Connection to their new URL that I generate.

I am attempting to do this as follows:

string workbookPath = String.Format("{0}\\{1}",      Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "PowerPivotSource.xlsx");
string workbookPath2 = String.Format("{0}\\{1}", Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "PowerPivotTestOut.xlsx");

foreach (Excel.WorkbookConnection connection in excelWorkbook.Connections)
{
    if (connection.Type == Excel.XlConnectionType.xlConnectionTypeDATAFEED)
    {
        Excel.DataFeedConnection dataFeedCur = connection.DataFeedConnection;

        dataFeedCur.Connection = dataFeedCur.Connection.Replace("mydomain", "Client1");
        break;
     }
 }

 excelWorkbook.Close(true);

I can read the contents of dataFeedCur.Connection, but attempting to edit it as shown here

dataFeedCur.Connection = dataFeedCur.Connection.Replace("localhost", "Client1");

throws the following exception:

Exception Message: "Exception from HRESULT: 0x800A03EC"
Exception Stack Trace: ""   at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)\r\n   at Microsoft.Office.Interop.Excel.DataFeedConnection.set_Connection(Object value)\r\n

There is some indication the WorkBookConnection.DataFeedConnection objet is Read Only... but I've heard people in other forums talk about being able to edit this connection string, but have seen no examples... any one know how this DataFeedConnection.Connection object can be edited?

Did you ever get this working? – Steve Lineberry Feb 23 '15 at 19:20 — Steve Lineberry, Feb 23 '15 at 19:20

score 0 · Answer 1 · edited May 23 '17 at 10:24

0

If the property is read-only, maybe you can use Reflection to still force it to be set. Let me know if this works:

Changing read only properties with reflection

edited May 23 '17 at 10:24

Community

1
1

answered Jul 02 '14 at 11:43

Taha Ahmad

559
4
16

Steve Lineberry · Answer 2 · 2015-02-26T16:56:49.890

I have been trying to get this working as well and it seems that since the data connections are actually stored in the SSAS cube embedded inside the excel file, that is why the error was occurring. I was however able to get this working with the help of SharePoint and it being setup properly with powerpivot support.

Here is my code:

using Microsoft.AnalysisServices;
using Microsoft.AnalysisServices.SPClient;
using Microsoft.AnalysisServices.SPClient.Interfaces;

string workbookUrl = "http://sharepoint/Shared%20Documents/spbook.xlsx";
using (IWorkbookSession workbookSession = ASSPClientProxy.OpenWorkbookModel(workbookUrl))
{
    bool hasEmbeddedModel = (workbookSession.Database != null);
    if (hasEmbeddedModel && workbookSession.WorkbookFormatVersion == WorkbookFileFormat.Excel2013)
    {
        using (Server ssas = new Server())
        {
            ssas.Connect("Data Source=" + workbookSession.Server);
            Database database = ssas.Databases.FindByName(workbookSession.Database);
            foreach (DataSource dataSource in database.DataSources)
            {
                dataSource.ConnectionString = dataSource.ConnectionString.Replace("parameter1", "parameter2");
                dataSource.Update();
                dataSource.Refresh();
            }
        }

        workbookSession.RefreshEmbeddedModel();
        workbookSession.Save();
    }
}

Programatically editing an Excel 2013 Powerpivot Datafeed Connection

2 Answers2