The UK 2050 Calculator Web Flash Excel Wiki
Register or sign in
This is a wiki Anyone can contribute, therefore we don't vouch for its accuracy.

Macro to locate formulae with links to external files

The process to convert the 2050 calculator spreadsheet to the C version required for the webtool doesn't work if the spreadsheet contains links to other spreadsheets. There's a macro below that makes a list of the cells that contain them, so that they can be easily located and removed. Please note the requirement to first created a worksheet called 'LinksList', as commented at the top of the code.

(N.B.It is also necessary to review the 'Names Manager', accessed from within Excels 'Formula's' menu, to ensure that none of the named cells/ranges/tables are located in exernal files. The macro below will not identify these, so these must be located manually by scrolling down the entries in the name manager.)

Paste the following code into a new VBA module (from Excel press Alt+F11, to open the VBA editor, then select 'Insert', then 'Module').

the code:

'Adapted from http://answers.microsoft.com/en-us/office/forum/office_2007-excel/workbook-links-cannot-be-updated/b8242469-ec57-e011-8dfc-68b599b31bf5?page=1&tm=1301177444768

The code is here: https://gist.github.com/tamc/dc15127a0b7475689e4e