Excel ChangeLink method is giving me an error

Question

All -

I have  Excel 2007.  I am trying to update a workbook with formulas.  The worksheets are not protected and the cells with formulae are not locked either.  when I try to run the following code I get an error of "Application-defined or object-defined error".

I am not sure what the issue is.  Can you share your thoughts?

Sub GetNewLink()

   Dim strOldFile       As String
   Dim strNewFile     As String
   Dim oCurrWksh    As Worksheet
   Dim oPriorWksh    As Worksheet
    

   On Error GoTo ErrHandle
   
   Set oPriorWksh = Worksheets("Prior")
   Set oCurrWksh = Worksheets("Current")
   

   'I store the full path info into the two variables below

   strOldFile = oPriorWksh.Range(OldFile).Value
   strNewFile = oPriorWksh.Range(Newfile).Value
   
   oCurrWksh.Activate
   UnProtectSheet

   'the below line is giving me an error of "Application-defined or object-defined error"

   ActiveWorkbook.ChangeLink Name:=strOldFile, NewName:=strNewFile, Type:=xlExcelLinks

   ActiveWorkbook.UpdateLink Name:=strNewFile, Type:=xlExcelLinks
   
   ProtectSheet
   oPriorWksh.Activate
   Range("A1").Select
    
   Exit Sub
   
ErrHandle:
   Debug.Print Err.Description
    
End Sub



Thank you,

Gerry

Answer 1

I check in the Excel Object browser and found the following

ChangeLink is a member of workbook (not workbooks).

ActiveWorkBook and workbooks is a member of excel.application and ChangeLink will not work.

Try this

Dim abc As Workbook
Set abc = Workbooks(ThisWorkbook.Name)

abc.ChangeLink Name:=strOldFile, NewName:=strNewFile, Type:=xlExcelLinks

---

jdweng

Answer 2

Gerry,

There is nothing apparently wrong with your syntax - copied and pasted, it worked fine for me. And if one of the file names was incorrectly spelled or did not exist, I got a different error, that the method failed.

Does the method work manually, and does it record properly?

Bernie

---

HTH, Bernie

Answer 3

Bernie,

Both files are there.  When I paste the file file path into windows explorer's address bar and delete the last character of the full file path, windows explorer's intellisense pops up.

The two lines, below are basically recorded but I changed to the variable names instead.

   ActiveWorkbook.ChangeLink Name:=strOldFile, NewName:=strNewFile, Type:=xlExcelLinks

   ActiveWorkbook.UpdateLink Name:=strNewFile, Type:=xlExcelLinks

Thanks,

Gerry

Answer 4

Hi Joel,

It didn't work.

Thanks again.

Gerry

Answer 5

This looks kinda dicey to me

   'I store the full path info into the two variables below

   strOldFile = oPriorWksh.Range(OldFile).Value
   strNewFile = oPriorWksh.Range(Newfile).Value

I guess you are trying this?

OldFile and NewFile are named ranges? If not then you cannot pass a variable which has a full path as a 'Range' object

   strOldFile = oPriorWksh.Range("OldFile").Value
   strNewFile = oPriorWksh.Range("Newfile").Value

---

Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

Answer 6

I think that if OldFile were not defined a defined variable set elsewhere, that line would give a 1004 run time error.

---

HTH, Bernie