Solved

Changelink doe not work

Posted on 2011-03-22
1
373 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:sonmic
1 Comment
 
LVL 33

Accepted Solution

by:
jppinto earned 500 total points
ID: 35187878
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
increment numbers by 10 11 31
Most Consistent Performer 4 22
Help with Excel formula 6 38
Excel 2016 loop through 6 0
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now