Results 1 to 4 of 4

Thread: Erase values linked to external files
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2015
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Erase values linked to external files

    Hi all,

    I wonder if there is a code for doing the following:

    I have a WB called WB1 with Sheet1 with links to another WB, called WB2, Sheet2.

    Is there a possibility to delete all the values that came from this external file (WB2)? I mean erase all of them quickly.

    Thanks in advance!

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Erase values linked to external files

    This solution assumes that references to other file are not created using INDIRECT function
    - test on a copy of your workbook first

    VBA opens WB1 and searches cells in Sheet1 for strings containing [WB2.xlsx]Sheet2
    Cells containing that string are added to range Clr (which inititally is set to the last cell in column A to make coding simpler)
    Cells in range Clr are cleared in one hit

    To test
    - place VBA below in a module in a new workbook
    - amend the 3 constant values to match your own
    - run the macro

    For testing purposes (to allow you to see if only the VBA is finding the correct cells)
    - line added to colour the cells RED
    - cell contents are not cleared
    - WB1 is not closed

    After successful test
    - remove the line colouring cells red and remove the leading apostrophe on the other 2 lines

    Option Explicit

    Code:
    Sub ClearValues()
        Const lookFor = "[WB2.xlsx]Sheet2"                          'string to find that identifies required link
        Const Wb1 = "C:\folder\subfolder\WB1.xlsm"                  'full path and name of WB1
        Const Sh1 = "Sheet1"                                        'name of sheet in WB1
       
        Dim Srch As Range, Cel As Range, Clr As Range, Addr As String, Wb As Workbook
        Set Wb = Workbooks.Open(Wb1)
        Set Srch = Wb.Sheets(Sh1).Cells
        Set Clr = Srch(Srch.Rows.Count, 1)
        
        Set Cel = Srch.Find(lookFor)
        If Cel Is Nothing Then
            MsgBox "Not found"
            Exit Sub
        End If
        Addr = Cel.Address
        Do
            Set Clr = Union(Clr, Cel)
            Set Cel = Srch.FindNext(Cel)
        Loop While Addr <> Cel.Address
        Clr.Interior.Color = vbRed                                  'delete after testing
        'Clr.ClearContents                                          'remove leading apostrophe after testing
        'Wb.Close False                                             'remove leading apostrophe after testing
    End Sub

  3. #3
    Board Regular
    Join Date
    May 2015
    Posts
    100
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Erase values linked to external files

    Hi and thanks for the reply!

    Im going to try this code, but in the meantime, I would like to know if it is possible to delete all the values that came from any external link.

    Thanks!


    Quote Originally Posted by Yongle View Post
    This solution assumes that references to other file are not created using INDIRECT function
    - test on a copy of your workbook first

    VBA opens WB1 and searches cells in Sheet1 for strings containing [WB2.xlsx]Sheet2
    Cells containing that string are added to range Clr (which inititally is set to the last cell in column A to make coding simpler)
    Cells in range Clr are cleared in one hit

    To test
    - place VBA below in a module in a new workbook
    - amend the 3 constant values to match your own
    - run the macro

    For testing purposes (to allow you to see if only the VBA is finding the correct cells)
    - line added to colour the cells RED
    - cell contents are not cleared
    - WB1 is not closed

    After successful test
    - remove the line colouring cells red and remove the leading apostrophe on the other 2 lines

    Option Explicit

    Code:
    Sub ClearValues()
        Const lookFor = "[WB2.xlsx]Sheet2"                          'string to find that identifies required link
        Const Wb1 = "C:\folder\subfolder\WB1.xlsm"                  'full path and name of WB1
        Const Sh1 = "Sheet1"                                        'name of sheet in WB1
       
        Dim Srch As Range, Cel As Range, Clr As Range, Addr As String, Wb As Workbook
        Set Wb = Workbooks.Open(Wb1)
        Set Srch = Wb.Sheets(Sh1).Cells
        Set Clr = Srch(Srch.Rows.Count, 1)
        
        Set Cel = Srch.Find(lookFor)
        If Cel Is Nothing Then
            MsgBox "Not found"
            Exit Sub
        End If
        Addr = Cel.Address
        Do
            Set Clr = Union(Clr, Cel)
            Set Cel = Srch.FindNext(Cel)
        Loop While Addr <> Cel.Address
        Clr.Interior.Color = vbRed                                  'delete after testing
        'Clr.ClearContents                                          'remove leading apostrophe after testing
        'Wb.Close False                                             'remove leading apostrophe after testing
    End Sub

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,055
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Erase values linked to external files

    I would like to know if it is possible to delete all the values that came from any external link
    Assuming links are to Excel files and are NOT created using INDIRECT function

    Try replacing ...
    Code:
    Const lookFor = "[WB2.xlsx]Sheet2"
    with ...
    Code:
    Const lookFor = "[*.xl*]"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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