Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ChangeLink (Excel 2002)

    I have a spreadsheet (A) that opens another one ([img]/forums/images/smilies/cool.gif[/img] which handles links of 10 other spreadsheets. When B opens, the links for the 10 other spreadsheets are pasted into a column. This then becomes the links that the model will use. Now all this works fine if the "pack" of models stays in its default location. If you move them elsewhere you get the following error message: "Run-time error 1004: Method chnage link of object _workbook failed". The code is as follows:

    Sub RESET()

    Application.EnableCancelKey = xlDisabled
    Dim LINK_IS As String
    Dim LINK_WILLBE As String

    LINK_IS = ActiveCell.Offset(0, -3)
    LINK_WILLBE = ActiveCell

    ActiveWorkbook.ChangeLink Name:=LINK_IS, NewName:= _
    LINK_WILLBE, Type:= _
    xlExcelLinks

    End Sub

    It is the changelink line that is highlighted when it debugs, though all the paths are the correct ones i.e if you put your curser on Link_Is or Link_Will_Be etc.

    Any ideas on how I can fix this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ChangeLink (Excel 2002)

    If LINK_IS is the path+filename currently used in the link formulas, and LINK_WILLBE the path+filename where the link file currently resides, the code should work OK. However, if LINK_IS does not correspond to the link formulas, you'll get the error you mention. Ditto if LINK_WILLBE does not correspond to the current location of the link file. Check VERY carefully that both are exactly correct.

  3. #3
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ChangeLink (Excel 2002)

    Hans,
    Link_IS is the default path of the 10 spreadsheets (loops through), Link_WILLBE is the path that it should change to i.e. the updated path. Since both sets of paths are stored in columns in a spreadsheet, I can't see how there would be a difference in the variables path name, and as far as I can tell, there is not (other than the path being contained in speach marks in the variable)? Yet I still get the error message. I guess that when this file is in its default location the Link_IS and Link_WILLBE are the same path i.e both columns of path info are the same. Where as when you move the files to a different location LinK_Is stays as it was and Link_WILLBE changes to the new paths.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ChangeLink (Excel 2002)

    Could you post a stripped down copy of the spreadsheet? Take out stuff that is not important for this problem, and remove sensitive data. The attached file should be less than 100 KB; if you can't get it that small, zip it and attach the zip file.

  5. #5
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ChangeLink (Excel 2002)

    Hans,

    As requested please see attached Winzip file. If you unzip all these files winzip will put the correct folder structure in place. Everything (from a user point of view) is run from BookA. BookA has a Paths sheet, where you will need to put the new paths to the files when you move them (in real life this is done automatically as part of a procedure). As you will see the pack works fine in its default location, but when you move it you get problems. Also when you move the files called Other 1 & 2 you will need to open them and save them to refresh their path info.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ChangeLink (Excel 2002)

    The moment you open BookB.xls, the link formulas already point to the Others subfolder of the folder where BookB.xls resides (Cocuments and Settings... on my PC), not to the original location (D:testOthers). So if you try to change the link from the original location to something else, you get an error message, because the formulas do not refer to this original location any more.

    Instead of using a fixed location as LINK_IS, determine it dynamically from the link formulas.

  7. #7
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ChangeLink (Excel 2002)

    Thanks Hans,

    Could you suggest a way of doing this?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ChangeLink (Excel 2002)

    The name of the workbook is enclosed in square brackets. If the workbook referred to is closed, the path is included.

    You can also use LinkSources, e.g. ActiveWorkbook.LinkSources(xlExcelLinks)(1) is the path/filename of the first linked Excel file. Look up LinkSources in the VBA help.

  9. #9
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ChangeLink (Excel 2002)

    I don't really understand? Are you advising me to put the links in square brackets? Re making this more dynamic etc, I'd like to do this, but I need to allow the user to open BookB and change the Excel models that are linked there as they may have an updated version that they want to link in instead of the "standard" one that is currently linked etc. Any further thoughts?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ChangeLink (Excel 2002)

    What I meant is that you can use the position of the square brackets within a link formula to extract the path+filename. A link formula could look like this:

    ='C:Excel[Test.xls]Sheet1'!$D$11

    You can extract the pth+filename from this using InStr, looking for ]. But is might be easier to use LinkSources.

  11. #11
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ChangeLink (Excel 2002)

    Hans,

    I don't understand how this helps me? In BookB the first column contains the "standard" links and is the default location of the linked files. I have a procedure which moves these files to a location that the user specifies i.e. it could be anywhere. During this process I capture the new paths of those files and paste them into the Paths sheet of BookA. When this procedure has finished the user sees BookA opened, they then go in and change variables etc, when they are happy they press calculate, it is at this point that (among other things) that we get the problem with the links as BookA pastes the Paths into the final column of BookB and then tries to "reset the links". So in the first instance, this is the problem that needs a fix. But also later on the user could manually change the linked files by using Excels Edit, Links function, so I need to keep this functionality as well. Does that make things more difficult?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ChangeLink (Excel 2002)

    What I have been trying to say is that storing the current paths in a range of cells is useless: it is not dependable. If you move a bunch of workbooks to another location, Excel may update the paths in the link formulas automatically, so that the paths stored in workbook cells are not valid any more. So you should not rely on paths stored in cells, but determine the path actually used in the link formulas. I pointed out two ways of doing that: parsing the link formulas or looking at the LinkSources property of the workbook. If you use that, it doesn't matter if the user changes the links through Edit | Links...

  13. #13
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ChangeLink (Excel 2002)

    Hans,

    Ok I understand what you are saying, but need help using the Linksource function (as usual VBA help leaves a lot to be desired). How do I even start putting this together? By using link source do we need BookB at all for instance?

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ChangeLink (Excel 2002)

    Not sure entirely what you are after or what code you currently use. Since you seem to want a list of of the "current source link" in a column, this will put it in col A of the active sheet.

    <pre>Option Explicit
    Sub ListLinks()
    Dim vLinks
    Dim i As Integer

    vLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If IsEmpty(vLinks) Then
    MsgBox "There are no Excel links"
    Else
    For i = 1 To UBound(vLinks)
    Cells(i, 1) = vLinks(i)
    Next i
    End If
    End Sub</pre>


    You could also use the array vLinks to do other things with it.

    Hope this helps,
    Steve

  15. #15
    Lounger
    Join Date
    Jan 2005
    Location
    Gwent, Wales
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ChangeLink (Excel 2002)

    Thanks for that, yes it does help. I understand that this shows me what my current linked paths are, but how do I use this to dynamically hold my links etc (as the default path and current (new) paths are held in cells at the moment. The reason that I ask is that these linked files will be moved to a user location (could be anywhere) and the current changelink procedure has a problem as Hans has described earlier. So I guess what I'm asking is how do I write something that holds the default file paths and allows me to automatically update to the new path of the files? The new paths will be text within cells, so I guess that this could be used and asigned to variables etc. It also needs the flexibility for the user to manually change the links by using edit, links etc. As you can see I'm struggling with this one....help!

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •