Changelink doe not work

Hi,

I use the code below to change the links with other sheets.

    NewLink = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File", , False)
    OldLink = ws.Range("B2").Value
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ThisWorkbook.Changelink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks
    Set ws = ThisWorkbook.Worksheets("Sheet2")
    ThisWorkbook.Changelink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks

For Sheet1 it works, but not for Sheet2.
What's wrong?

Tx
sonmicAsked:
Who is Participating?
 
jppintoConnect With a Mentor Commented:
Before this line:

OldLink = ws.Range("B2").Value

Do you have ws set already? You're setting Set ws = ThisWorkbook.Worksheets("Sheet1") after this OldLink = ws.Range("B2").Value...

You should do like this:

NewLink = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select File", , False)
Set ws = ThisWorkbook.Worksheets("Sheet1")
OldLink = ws.Range("B2").Value
ThisWorkbook.Changelink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks
Set ws = ThisWorkbook.Worksheets("Sheet2")
OldLink = ws.Range("B2").Value
ThisWorkbook.Changelink Name:=OldLink, NewName:=NewLink, Type:=xlExcelLinks

This is how it makes sense to me...

jppinto
0
All Courses

From novice to tech pro — start learning today.