Hello everyone
I have a sheet with a lot of formulas linked to another workbook .. I need a way to store the external links in an array then delete these links
I know that I can break these links .. but in fact I need to keep formulas.. I don't need to have values only
Hope it is clear
< ----- Please click the little star * next to add reputation if my post helps you
Visit Forum : From Here
You have not provided a lot of information.
Do want to save all formula in workbook or just all on one sheet etc?
Please Login or Register to view this content.
But the principle is that you need to loop through the cells holding the formulae that you want to keep, and then save the formula to a 2 dimensional array, together with the cell address (otherwise useless!)
The code below saves the formula from B1 to B20 in the active sheet, to array named formArr and also the cell addresses.
The message box shows what is held in the array for each entry
Adapt to suit your particular requirements
Please Login or Register to view this content.
Thanks a lot for reply Mr. Kevin
In fact I need to deal with external links ..
Here's an attachment to make it clearer
In workbook named "Destination" in "DestinationSheet" there are formulas that are linked to Target workbook
I need to keep formulas and break these links
For example: In A2
I need to keep the formula to be like thatPlease Login or Register to view this content.
Thanks for helpPlease Login or Register to view this content.
=+'D:\Documents\ABC\[LinksBook.xlsx]LinkSheet'!$B$1 =linking formula,would go in array
"I have a sheet with a lot of formulas linked to another workbook .. I need a way to store the external links in an array then delete these links"
Q1 Please confirm that the contents of the array are exactly what you want
In post#3 you ask for cell"A2" which currently looks like:
='C:\Users\Future\Desktop\Delete Links Keep Formulas\[Target.xls]Name'!A1
To be amended to:
=Name!A1
So in total you asking for the VBA to
1 copy the formulae to the array(as per above subroutine) AND
2 overwrite cells to leave only the bare formula (no links) behind
3 Everything is in worksheet DestinationSheet in workbook Destination
Q2 Have I understood correctly?
Q3 What are the last active column and last active row number in DestinationSheet?
thanks
Last edited by Kevin#; 01-16-2016 at 07:28 PM.
Yes Mr. Kevin
That's exactly what I need to say ..to deal with the formulas in the DestinationSheet and get rid of the external links ...
Thanks a lot
Right let's try to sort out the rest of your thread next, and then we can pull it all together.
I have tried to work out what we need to strip out from the cells in sheet "Destination" to leave you with the bare formula.
The external link is always wrapped in single quotes ' '
- the macro splits each cell every time it encounters character '
- dumps that bit of string into an array
- removes the string if it contains a " \ " (which is present in all links)
- joins all the pieces back together again
- removes any !
- writes resultant string to 2 new worksheets
- FormulaSheet should contain what you are looking for
- TextSheet allows you to see the formula without going into individual cells
So test as follows
- create a test copy of your file (just in case!!)
- copy the VBA below into Module1
- run it and check to see if it is what you are looking for
Please Login or Register to view this content.
Mr. Kevin
I tested your code which creates two sheets and store formulas in a sheet and stores sheet names that are linked to these cells
But it seems that I forgot to attach the file in my previous post
Here it is
How can I break these links to the other workbook but keeps the exisitng formula as you mentioned to get rid of the part between ' '
and add the sheet that should be added before !
Hope it is clear after attaching the sample
Last edited by YasserKhalil; 01-17-2016 at 10:13 AM.
Thanks for the workbook - I can now test using your file - much better.
I will add in the sheet name for you and tailor the previous macro later today
Can you check to see if the formulae in the cells all look ok to you.
Is the sub-routine giving you anything you do not like?
As for Destination Workbook . Sheet named Old ... It should be named Age.
Sorry for uploading incorrect attachment
Sheets("Old") should be Sheets("Age") to be similar to the Target Workbook
As for formulae it should be linked to the sheet name in the same workbook ..but to remove the link to the Target Workbook So
Should bePlease Login or Register to view this content.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks