Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Changelink doe not work

Posted on 2011-03-22
1
377 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

791 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