none
Changing ODC file in Excel RRS feed

  • Question

  • I know this isn't the best forum for this question, but social.msdn.microsoft.com for Office, answers.microsoft.com, and other traditional Office sources don't seem to be of any help.

    I have an Analysis Services cube that my users connect to from Excel using an ODC file.  We use multiple environments (cubes), such as demo, test, and production, each with a separate ODC file for convenience.  I want to be able to change the ODC file of a given Excel report so that it references a different environment, but then Excel will "lose" the reference to the new ODC file, embedding the connection information instead.

    Steps to Reproduce:

    1. Build an Excel report connecting to an Analysis Services cube through an ODC file.

    2. Go to Data, Connections, Properties, Definition tab of the Connection Properties dialog, then Browse for a different ODC file.  In my case, it's almost identical to the ODC file used in Step 1 except the Initial Catalog varies.  Sometimes the server may be different.

    3. Clock Open then OK on the Connection Properties dialog.

    I get the dialog box stating the following:

    The connection in this workbook will no longer be identical to the connection defined in the external file located at '<SomeFileName>.odc'.

    The link to that external file will be removed.

    This is frustrating because I want it to update the link to a different external file, not remove it.  I'm 90% confident this behavior worked in Excel 2010.

    Any direction you can give me would be helpful.  I've spent hours looking at other Microsoft and Excel forums.


    • Edited by John Keller Friday, February 3, 2017 10:33 PM
    Thursday, February 2, 2017 5:19 PM

Answers

  • Hi John,

    I reproduced your issue. If we change the ODC file in connection properties, the link to that ODC file will be removed as the warning message said. Next you edit the connection properties, the ODC file path is always greyed so that you can't directly update the link. But you can still change it to other ODC file.

    In Excel 2013:

    However, this issue doesn't exist on Excel 2010.

    In Excel 2010:

    It seems to be Excel issue. I suggest you submit a feedback for Excel on: http://connect.microsoft.com/

    Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by John Keller Thursday, February 16, 2017 5:08 PM
    Friday, February 3, 2017 7:04 AM
    Moderator

All replies

  • Hi John,

    I reproduced your issue. If we change the ODC file in connection properties, the link to that ODC file will be removed as the warning message said. Next you edit the connection properties, the ODC file path is always greyed so that you can't directly update the link. But you can still change it to other ODC file.

    In Excel 2013:

    However, this issue doesn't exist on Excel 2010.

    In Excel 2010:

    It seems to be Excel issue. I suggest you submit a feedback for Excel on: http://connect.microsoft.com/

    Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by John Keller Thursday, February 16, 2017 5:08 PM
    Friday, February 3, 2017 7:04 AM
    Moderator
  • Thank you, Simon.  connect.microsoft.com does not have Excel or Office products.  I would like to submit this bug, but haven't found the best location to do that yet.

    Friday, February 3, 2017 10:29 PM
  • Hi John,

    You can submit this issue on UserVoice:

    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application

    Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, February 13, 2017 10:03 AM
    Moderator