Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Converting external links to values

    I have a file with > 50 external links .. What I would like to do is print all of these external links on a worksheet, and update them all to their values.

    Any ideas?

    Thanks in advance.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I don't know an easy way to do that, but here is the hard way, plus some ideas !

    http://office.microsoft.com/en-gb/ex...010205611.aspx

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    jlkirk,

    "I have a file with > 50 external links": What format is the file (Word, Excel, txt)? Can we assume that these are URL addresses or data links?

    "What I would like to do is print all of these external links on a worksheet": Print them to a worksheet (import) or from a worksheet to paper?

    "update them all to their values": Does this mean add hyperlinks to the text?

    Could you please post a sample?

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Martin, but that's not quite what I was looking for. I would like to list all of the links on a new spreadsheet, and then convert all of their links to their values.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The external links are data links to other Excel files;

    I would like to print them to a new worksheet in the file that has the links embedded;

    Update to all of their values = instead of a link, convert the linked data to their absolute values, so, when all is said and done, there should not be any more external data links in the file, but, instead, the values.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JL,

    Unless I'm missing something why not just copy/paste special values? HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Geek. I suppose I could, but that would take quite a while given > 50 links. I would think there would be some VBA guru out there that may have an answer...

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JL,

    Ok, here's some VBA that will operate on the currently active sheet and replace all links with the value.
    Code:
    Option Explicit
    
    Sub ReplaceLinkedCellsWithValue()
        
        Dim rngFound     As Range
        Dim zFirstFound  As String
        
        [A1].Select
        Set rngFound = Cells.Find(What:="[*]", _
                             After:=ActiveCell, _
                             LookIn:=xlFormulas, _
                             LookAt:=xlPart, _
                             SearchOrder:=xlByColumns, _
                             SearchDirection:=xlNext, _
                             MatchCase:=False, _
                             SearchFormat:=False)
                
        Do While (Not (rngFound Is Nothing))
          rngFound.Copy
          rngFound.PasteSpecial xlPasteValues
          Application.CutCopyMode = False
          Set rngFound = Cells.FindNext(After:=rngFound)
        Loop
        
    End Sub  'ReplaceLinkedCellsWithValue
    Remember to ALWAYS test on a COPY of your workbook!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks Geek

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Aletrnately, this would achieve the same result.

    Code:
    Public Sub ExtToValue()
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange
        If InStr(1, cell.Formula, "[", 1) > 0 Then cell.Formula = cell.Value
    Next cell
    End Sub

  11. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi jkirk

    These routines may help.

    The first assumes you have a sheet named [Links] in your workbook.
    This is used to list the links.
    Code:
    Sub listExcelLinks()
    
    zLinks = ThisWorkbook.LinkSources   'create array list of links
    
    If Not IsEmpty(zLinks) Then         'check that links are present
    Sheets("Links").Select              'switch to sheet to be used for listing
    Cells.Clear                         'clear any entries on sheet first
    [a1] = "Link Number"                'add a heading label in column [A]
    [b1] = "Link source"                'add a heading label in column [B]
    
    For I = 1 To UBound(zLinks)         'loop through all entries in the array
    Cells(I + 1, "A") = I               'put link reference number in col [A]
    Cells(I + 1, "B") = zLinks(I)       'put link source (filename and path) in col [B]
    Next I                              'process next array entry
    
    [A:B].EntireColumn.AutoFit          'set column widths to fit the entries
    
    End If                              'end of test for links
    
    End Sub
    Before you break all links, you may want to get the lastest values first.
    This routine will refresh all values
    Code:
    Sub updateAllExcelLinks()
    
    On Error Resume Next                'set error trap for next line..
    ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources
    On Error GoTo 0                     'reset error trap
    
    End Sub

    This routine will then remove all links, leaving the current values only.
    Code:
    Sub removeAllExcelLinks()
    
    zLinks = ThisWorkbook.LinkSources   'create array list of links
    For I = 1 To UBound(zLinks)         'loop through all entries in the array
    ThisWorkbook.BreakLink zLinks(I), 1 'break Excel link
    Next I                              'process next link
    
    End Sub
    zeddy
    Last edited by zeddy; 2014-11-26 at 11:09. Reason: updated code

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Cells.Clear 'clear any entries on sheet first
    Reiterating RG's Words of Wisdom, "Remember to ALWAYS test on a COPY of your workbook!"

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2014-11-26)

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Maud

    Very good advice.
    Now, as my note said, it assumes you have a sheet named [Links] in the workbook to be used for listing the workbook links. And, just to be sure, it makes sure that particular sheet [Links] is 'empty', before creating the list.
    A better solution would probably be to have vba add a new sheet, and then use that new sheet for the list of links. I thought my sloppy requirement for a pre-existing sheet named [Links] might tempt you to provide a more elegant version!

    Also, we could have a third column in the listing, say 'Status', which could be used to check whether the excel source file 'exists' or is 'missing' (using the vba Dir command to check etc etc)

    zeddy

  15. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2014-11-26)

  16. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Ah, yes....thanks for the clarification

    Maud

Posting Permissions

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