CODEWITHSUNDEEP
vbaexcel
John Hartley
John Hartley

Reputation: 483

Updating a pasted a link to an external spreadsheet in VBA

I am trying to intercept the process of pasting an external link. I am wanting to change the source of the link to an internal web site.

The formula looks like: 

='[filename.xlsx]Sheet1'!$B$7

Which I want to change to:

='http://server/site/[filename.xlsx]Sheet1'!$B$7

This code does the trick:

Dim 

s As String s = ActiveCell.FormulaR1C1  
If Len(s) < 3 Then Exit Sub 
Dim i As Integer i = InStr(s, "[") 
If i > 0 Then
        s = "='X:\directory\[" + Mid(s, i + 1)
        ActiveCell.FormulaR1C1 = s
End If

I have tried to add before the above

ActiveSheet.Paste Link:=True

But it does not past a link but a formula is pasted instead. I have looked at extracting the link from the clipboard but have not had any luck with Application.ClipboardFormats.

Any ideas would be appreciated.

Upvotes: 0

Views: 69

Answers (1)

Bernard Saucier
Bernard Saucier

Reputation: 2270

Try changing :

ActiveCell.FormulaR1C1 = s

to

ActiveCell = s
ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell, Address:=s

This way, you only put the value you want to see, and then you add the hyperlink to where you want it to point! No reason to struggle with the VERY picky Excel .Paste or .PasteSpecial.

Upvotes: 1

Related Questions