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

Return to the page.

Showing just the changes made in the edit by Tom Counsell at 2014-05-20 15:44:01 UTC

See all changes since this wiki was released

2014-05-20

2014-04-30

Title: Macro to locate formulae with links to external files

Content: 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').

h2 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&amp;tm=1301177444768 <this used to search for a '[', but this doesn't work with the 2050 Calculator, as it makes use of structured table references which also use square brackets.  So I changed this to search for '.xls', which seemed to do the trick '

<script src="https://gist.github.com/tamc/dc15127a0b7475689e4e.js"></script>



User: Tom Counsell

Picture updated at: 

Signed off by: 

Signed off at:
Title: Macro to locate formulae with links to external files

Content: 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').

h2 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&amp;tm=1301177444768 <this used to search for a '[', but this doesn't work with the 2050 Calculator, as it makes use of structured table references which also use square brackets.  So I changed this to search for '.xls', which seemed to do the trick '

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



User: Tom Counsell

Picture updated at: 

Signed off by: 

Signed off at: