none
Unexpected behavior when refreshing internal data connections in Excel RRS feed

  • Question

  • Hi, guys.

    I have faced a big and inconvenient problem with Excel spreadsheets that have internal data connections (queries that selects ranges of the own workbook) in them.

    I wiil start by showing the problem e exposing how to reproduce it.

    Problem: if I have some internal connetion and another instance (pay attention, instance, not other workbook) was already open, when refreshing that connection, the same workbook (which have the internal connection) is instantly opened in readonly mode in another window...

    Why this occurs? The problem is that I'm developing a .NET automation application for Excel and this behavior breaks some process flow (because the readonly file stays open e sometimes it causes a non refresh in the original workbook)

    To solve this problem I began to use the same instance if it is already open, but this is not the best solution because I need to handle the concurrency when running several VBA macros in the same instance via .NET Interop.

    I hope someone knows how to solve this problem because I've been searching for this several days...

    Thanks,

    Obs.: The Office version is 2013

    Saturday, September 12, 2015 3:53 PM

All replies

  • Hi diegocpcruz,

    >> if I have some internal connetion and another instance (pay attention, instance, not other workbook) was already open, when refreshing that connection, the same workbook (which have the internal connection) is instantly opened in readonly mode in another window

    Could you share us why you start a new excel instance? If you operate in the same instance, will this issue still happen?

    Based on your description, I could not think out why the workbook will open when you refresh connection. As my manually test, I start a new excel instance, and refresh connections (Data->Connections->Refersh all), I did not get any new workbook. Did you refresh connection manually or with code? It would be helpful if you could share us related steps or code to reproduce your issue at our side.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, September 14, 2015 7:40 AM
  • Hi, Edward.

    Thanks for answering.

    I open a new istance by pressing ALT when clicking in the Excel icon in my taskbar. If I open it in the same instance no problem occurs.

    To reproduce this error just follow this steps:

    Create a workbook with some internal data connections (created in MS Query, where the source is the own workbook). Close the workbook. So open a blank one and keep this open. Then open the workbook with the connections in another instance (by pressing ALT, as I said, and selecting it in the "open" tab). Refresh the connection. A new window will appears with the workbook in readonly mode.

    Here a workbook to test this issue (just change the string connection inserting the file path in your computer):

    https://dl.dropboxusercontent.com/u/72476080/InternalQueryTest.xlsx

    --
    Diego Cruz

    • Edited by diegocpcruz Monday, September 14, 2015 2:01 PM
    Monday, September 14, 2015 1:59 PM
  • Hi diegocpcruz,

    >> To reproduce this error just follow this steps

    I have made a test with your excel file and steps, but I failed to reproduce your issue.

    Based on your description, it seems that you are developing automation application for excel, do you develop an excel vsto? I assume that it was caused by your excel add-ins.

    To make a further troubleshooting, I suggest you remove all of the add-ins, and then test it again.
    For information about removing add-in, you could refer the link below:
    # Add or remove add-ins
    https://support.office.com/en-gb/article/Add-or-remove-add-ins-64d3d147-98fb-4b82-8833-709d54e3ace1

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, September 15, 2015 5:34 AM
  • Hi,

    I removed all addins and the issue remains...

    Do you changed the filepath and directory path in the connection string? I tested this issue in three computers, they all exhibit tha same behavior.

    I made a video to show you the issue with more details. Please, take a look:

    https://www.youtube.com/watch?v=rdxCe5VAdp4

    Thanks,

    --
    Diego Cruz

    Tuesday, September 15, 2015 2:23 PM
  • Hi Diego,

    With your steps and file, I reproduced your issue. To be honesty, I am not sure why it happened.

    Based on your requirement, you need to open the file in a new instance, am I right? I suggest you use command line to create a new instance instead of using “Alt”. As my test, it would be happen if you use the command line below to open a new instance.

    excel.exe /x 

    # Command-line switches for Excel
    https://support.office.com/en-in/article/Command-line-switches-for-Excel-c6f24136-e80c-4de9-a0ca-d5c68792cc6f

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, September 16, 2015 8:02 AM
  • Actually, my ultimate goal is to run tasks in Excel via a .NET application. For this, I instantiate a Excel application with

    new Microsoft.Office.Interop.Excel.Application()

    and do what I want to do (run VBA macros, refresh tables, etc). The problem is that for this type of connections this issue occurs... If exists only one open instance of Excel, everything runs OK, else this readonly workbook appears and bad things occurrs (for example, tables are not refreshed).

    At the moment in my code I just verify if there is some Excel instance already open and if so I use it. The problem is that Excel is single thread, so I need to handle concurrency when multiple workbook tasks are running at the same time. At the momment, I just catch the COM exception "0x800AC472" and retry (with a sleep of X seconds) until the running task has ended. However, it's not a very robust solution, several errors occurs and some deadlock issues appears...

    Manually, I did what you said (open a new instance running the command 'Excel.exe /x') and the same error occurs...

    It's a very strange issue and incredibly I can not find any references for this problem in any forum... It's very curious that no one has ever faced it sometime.

    Do you have any sugestion on what can I do to at least handle it?

    --
    Diego Cruz


    • Edited by diegocpcruz Wednesday, September 16, 2015 1:51 PM
    Wednesday, September 16, 2015 1:50 PM
  • Hi diegocpcruz,

    >> Do you have any sugestion on what can I do to at least handle it?

    To be honesty, I did not know why it happened. I tried to find a workaround, but I failed. What I could suggest is to store the data in other workbooks. In other words, get the data from external workbook instead of the same workbook. As my test, it will not generate read-only workbook.

    Sorry for the inconvenient.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, September 17, 2015 8:29 AM