locked
.XLSX with external links does not open data sources RRS feed

  • Question

  • I am having a problem with a spreadsheet. It references cells in other spreadsheets. When the spreadsheet opens, it asks if we want to update the values and we reply yes. It then consistently errors saying that the data sources are not open. We have to open them manually, through the edit links dialog, in order for the values to update.

    Needless to say, this is a pain.

    The files are XLSX files but the error happens in both 2010 and 2003.

    Any ideas?

    Wednesday, November 24, 2010 6:47 PM

Answers

  • Hello,

    This could be any number of things.  One of the more obscure issues I've run across is: a file is linked to an XLSM and the modules within the XLSM file were corrupt causing the issue you are seeing.  You could test by exporting VBA modules(non "worksheet" or "ThisWorkbook") from the source workbook then deleting them from the workbook.

    The following is a KB that describes how links work in Excel: KB 328440 Description of link management and storage in Excel
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;328440


    Best Regards,
    Chad

     


    Chad Mathiason
    Monday, November 29, 2010 10:11 PM

All replies

  • Hello,

    This could be any number of things.  One of the more obscure issues I've run across is: a file is linked to an XLSM and the modules within the XLSM file were corrupt causing the issue you are seeing.  You could test by exporting VBA modules(non "worksheet" or "ThisWorkbook") from the source workbook then deleting them from the workbook.

    The following is a KB that describes how links work in Excel: KB 328440 Description of link management and storage in Excel
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;328440


    Best Regards,
    Chad

     


    Chad Mathiason
    Monday, November 29, 2010 10:11 PM
  • Thank you for your reply. I removed the VBA code (a module and a form) and it did not help the situation. It finds the source worksheets, it just doesn't open them.

    If you have any other ideas, I am open to them. I will also review the KB article and see if I can find anything relevant.

    Thanks again.

     

    Tuesday, November 30, 2010 10:29 PM