+ Reply to Thread
Results 1 to 9 of 9

Store all external links in an array and then replace all links with an empty string

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Store all external links in an array and then replace all links with an empty string

    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

  2. #2
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Store all external links in an array and then replace all links with an empty string

    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.

  3. #3
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Store all external links in an array and then replace all links with an empty string

    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
    Please Login or Register  to view this content.
    I need to keep the formula to be like that
    Please Login or Register  to view this content.
    Thanks for help

  4. #4
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Store all external links in an array and then replace all links with an empty string

    =+'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.

  5. #5
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Store all external links in an array and then replace all links with an empty string

    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

  6. #6
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Store all external links in an array and then replace all links with an empty string

    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.

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Store all external links in an array and then replace all links with an empty string

    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
    Attached Files Attached Files
    Last edited by YasserKhalil; 01-17-2016 at 10:13 AM.

  8. #8
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Store all external links in an array and then replace all links with an empty string

    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?

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,882

    Re: Store all external links in an array and then replace all links with an empty string

    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
    Please Login or Register  to view this content.
    Should be
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replace External Links with Values inside Formulas
    By k64 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-22-2015, 03:01 PM
  2. Completely remove/delete all the external links (Break Links)
    By SunOffice in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-23-2014, 05:31 AM
  3. External Links
    By aaslezak in forum Excel General
    Replies: 1
    Last Post: 11-13-2012, 06:38 PM
  4. Replies: 2
    Last Post: 01-18-2012, 05:08 AM
  5. Replies: 0
    Last Post: 12-07-2008, 01:25 PM
  6. External Links and Add ins
    By MSweetG222 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-05-2006, 12:15 PM
  7. [SOLVED] External Links
    By Jeff in forum Excel General
    Replies: 2
    Last Post: 02-08-2006, 05:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1