none
Error 1004 when updating Powerpivot source connection through vba RRS feed

  • Question

  • Hi,

    I have a Powerpivot workbook (Excel 2013) which connects to an atom data source. In this specific workbook, I need to pass a parameter in the feed url.

    I am using VBA to dynamically change the connection string url for the feed. It works flawlessly if the connection was created outside of the PowerPivot addin.

    The issue is, when the connection is created inside of the PowerPivot addin or if I change it afterwards, then the exactly same code pops me error 1004. I suppose it is somewhat related to a message Excel appends to the connection "Some properties cannot be changed because this connection was modified using the PowerPivot Add-In"

    I'd like to know if there is any workaround for this situation.

    Here's the code I'm using 

    Sub ModifyConnectionString()
    
        Dim Mdl As ModelTable
        Dim cString As String
        
        Set Mdl = Application.ActiveWorkbook.Model.ModelTables("SomeTable")
        
        cString = Mdl.SourceWorkbookConnection.DataFeedConnection.Connection
        cString = Replace(cString, "Data Source=http://server/page.atom?parameter=", "Data Source=http://server/page.atom?parameter=123")
        
        Mdl.SourceWorkbookConnection.DataFeedConnection.Connection = cString
        
    End Sub
    

    And the error, which occurs in the last line of code when trying to assign the new connection string:

    Run-time error '1004': Application-defined or object-defined error

    Thank you in advance

    Thursday, July 25, 2013 5:00 PM

All replies

  • Did you get an answer for this issue. We are finding exactly the same issue. We need to change the connection string on our excel / powerpivot workbooks dynamically to deploy to different environments. However the connection string is greyed out in the excel front end and there is text in the connection string stating that"Some properties cannot be changed because this connection was modified using the PowerPivot add in". 
    This is a major issue for our project!
    Sunday, May 18, 2014 1:41 PM
  • Hello,

    I have the same problem, someone find the answer for this issue. I lost a lot of time trying to update my connection 


    LilianaB

    Wednesday, June 11, 2014 6:10 PM
  • I have the same problem.

    Im trying to change Connection of DataFeedConnection, but keep getting the runtime error.

    Is there any other way to change Data Source of existing connection?

    Thank you


    Ondra Plánička

    Monday, July 7, 2014 10:23 AM
  • Did you ever figure it out?  I am also having the same issue.
    Wednesday, January 6, 2016 5:58 PM