Converting external links to values

Topic

New Reply

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.

Reply | Quote

Replies

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/excel-help/find-external-references-links-in-a-workbook-HP010205611.aspx

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

JL,

Unless I’m missing something why not just copy/paste special values? HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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…

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

thanks Geek

Reply | Quote

Aletrnately, this would achieve the same result.

Code:

Public Sub zxZX()
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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Cells.Clear ’clear any entries on sheet first

Reiterating RG’s Words of Wisdom, “Remember to ALWAYS test on a COPY of your workbook!”

Reply | Quote

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

Reply | Quote

Ah, yes….thanks for the clarification

Maud

Reply | Quote