Design Table Linking to External Excel File

Wondering if anyone can provide an explanation and ideally a solution to this problem.

I have a externally linked excel spreadsheet to my model in solidworks as a design table. it is saved as a .xls (97-2003 Worksheet) as i have a Macro module with the spreadsheet required to determine the some of the results that form part of the design table.

Every time i save the solidworks part it says it is saving linked design table, however it saves a separate file in the same location with _new at the end of the linked file name.

E.g if my file was test.xls it would save a new file test_new.xls

This file is corrupted and can't be opened. i have tried saving without the macro module and this works fine so i am sure this is what is causing it. Does anyone have a work around for this ?

Lastly I'm on solid works 2011, is this still the case in newer versions of solidworks?

thanks

sam

SolidworksConfigurations design Tables