Edit Links - "Error: Source not Found" with certain .xlsm files

[k425518]

I am working with Excel 2010.

There are certain .xlsm files in our system (some have macros and others don't) which generate "Error: Source not Found" in the Edit Links dialog box when the Check Status button is pushed. However, the source location is correct, as you can click on Open Source and it will open the correct file.

For simplicity of explaining my issue, consider the following scenario. In my workbook "Corn Production Summary.xls_" I link to 2 source files: "Iowa.xlsm" and "Nebraska.xlsx" (Note: The extension of the summary file doesn't matter. This error happens regardless of what the summary is.)

When I open "Corn Production Summary" I am prompted to update values. I select Update and the file refreshes and recalculates appearing to have updated all values (i.e. no error messages). However, the values from Iowa.xlsm did NOT update and there was no error message indicating that the values didn't update.

To confirm this, I go to Data>Edit Links>Check Status in "Corn Production Summary" and I see that Iowa.xlsm has the "Error: Source not Found" error. I click on Open Source and once the file is open, my data in the summary file will update.

One work around that the help desk has found was to do the following process (with the summary file closed):
1) Iowa.xlsm and Save As Iowa.xlsx (removing macros if needed)
2) Save As Iowa.xlsx as Iowa.xlsm (overwriting the file that is currently there and readding the macros)
3) Open the summary file and both Iowa and Nebraska work just fine now.

There are a lot of files like this, and because Excel doesn't prompt us that it isn't really getting the values from these certain .xlsm files we would have to search in "Edit Links" for each file to make sure that they really are updating. I've been charged with the task of finding out a better way to fix this. I was hoping that if I found the cause of the error, that might help with the fix.

Has anyone experienced this or does anyone have some sort of explanation as to why this would be happening?

[s_andrews]

I am new to this forum. Having the same problem of linking to xlsm files, I see MANY similar postings all with one thing in common. Namely, there is no explanation nor solution.

Recently upgrading from 2003 to 2010, we have many xlsm workbooks. Some will have cells which reference cells of another workbook. Such a cell will show up as "#REF!" and upon going through "Edit Links", the external files will show "ERROR: Source File not Found". The only way I have found around this is to save all workbooks as type xls. To do this, however, must prevent you from taking advantage of the 2010 version fully since we are using the 2003 format.

What's more scary is that you can leave the values of the external reference (as of the last time the file was saved and both files were opened) by using the "Save external link values" option. But if you do not have the source workbook open and its values have changed, these new values will not be what is shown. Rather than the link being updated, it will simply show what values were there before with no message or warning that the values shown may not be current. So when can we trust those values to be correct?

Does anyone know whether this is truly an accepted deficiency of the 2010 version which will never be fixed? Can we have external links to xls files but not to xlsm files? Thank you in advance for any input.

Steve Andrews

[arlu1201]

Steve,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

[s_andrews]

OK, will do.

Other forums I have been on have no such rule. If twenty people have the same problem, many of their concerns (and solutions) would be posted on a few threads. This rule will result in twenty separate threads requiring one to search more extensively to see if any of them got a solution posted. If that is the way this forum chooses to operate, I will go by the forum rules and enter a new posting.