Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

We have an Excel spreadsheet that uses data from another workbook.

We use Dropbox to share the files.

When I open the spreadsheet on another Mac or PC the links need relinking.

Is there a way to make the links reference just the immediate folder?

Here's the problem explained further.

Workbook contains a link to: ~my-mac/dropbox/work/source.xlsx

It doesn't work when opened on: ~ãnother-mac/dropbox/work/source.xlsx

I want Excel 2011 for Mac just to look for ~/dropbox/work/source.xlsx

I've read several help pages on Absoulte links and relative links and UNC links. It seems it is possible to link workbooks using the short link, however, I am unable to make it work.

Is it a Mac thing? Is it a me thing?

Any potshots from hotshots most welcome!

J

share|improve this question
    
excel stores relative links only when the path is on the same drive. see this for further info –  Sean Cheshire Oct 1 '13 at 12:23

1 Answer 1

This VBA code will update the links to the immediate folder, provided your file only links to one other book:

Sub DropBox()

    Dim vOldLink As Variant, sOldLink As String
    vOldLink = ActiveWorkbook.LinkSources(xlExcelLinks)
    sOldLink = vOldLink(1)

    Dim vFilename As Variant, sFilename As String
    vFilename = Split(sOldLink, "\")
    sFilename = vFilename(UBound(vFilename))

    Dim sPath
    sPath = ThisWorkbook.Path & "\"

    ActiveWorkbook.ChangeLink Name:=sOldLink, NewName:=sPath & sFilename, Type:=xlLinkTypeExcelLinks

End Sub

I am not sure about Mac though...

share|improve this answer
    
Thanks for that. Will try it out but unfortunately the workbook links to 3 other workbooks. –  James Fisher-Martins Feb 8 at 15:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.