locked
Excel ChangeLink method is giving me an error RRS feed

  • 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

    Thursday, March 22, 2012 5:33 PM

Answers

  • 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.


    Friday, March 23, 2012 12:00 AM

All replies

  • 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

    Thursday, March 22, 2012 7:33 PM
  • 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

    Thursday, March 22, 2012 7:37 PM
  • 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

    Thursday, March 22, 2012 8:15 PM
  • Hi Joel,

    It didn't work.

    Thanks again.

    Gerry

    Thursday, March 22, 2012 8:16 PM
  • 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.


    Friday, March 23, 2012 12:00 AM
  • I think that if OldFile were not defined a defined variable set elsewhere, that line would give a 1004 run time error.

    HTH, Bernie

    Friday, March 23, 2012 1:24 PM