Linking files from Excel (with HYPELINK() function)

Question

Hi,

(BTW, this is not a problem with my Excel formula. It has to be something to do with the way file paths are created in DropBox...)

I am trying to create dynamic links within an excel spreadsheet to PDF files. All files (including the excel worksheet) are stored in the same Dropbox directory. The Excel workbook is a shared resource, that needs to be accessed by several users using Macs. No matter what I do, I cannot get the links to work, at least not initially. Here's what's weird: My hyperlinks throw an error in excel, unless I open the linked file manually, after which the link will work?!? I have no idea how that is even possible. It's as if opening the file stores its filepath in memory somewhere that excel has access to, but that without that, excel can't access the file on its own... Anyway, that's not a work around. These hyperlinks are generated dynamically, and if you have to open the file manually first, well then the hyperlink itself is kinda pointless, eh!

I notice that when you right-click on the locally saved version of a file and select Copy Dropbox link, the resulting link has an extra directory with a 15 character code name (https://www.dropbox.com/s/XXXXXXXXXXXXXXX/[filename]?dl=0). Excel doesn't know that coded filename. Is that what's rendering this impossible? And if so, how do I get around it? I can't be the first person in history to be trying to do this!! Any help much appreciated!

ColinAtInspire · Answer

Hi Walter,

Thank you for your reply. I have read through the linked resources, and unfortunately they are not addressing exactly the same problem. I am having trouble with the links on my own machine (haven't even gotten to the problems with other users trying to access them yet!) I am trying to link resources in my local file structure. All resources are in the same folder (true for my colleagues as well) so relative paths should work fine.

Now, I can ******-code hyperlinks from my excel sheet to the resources, which work. So the resources are accessible - it's not a permissions issue. When I build links dynamically using the HYPERLINK() formula, however (even though I know how to use this formula and the paths are correct), I get the "Cannot open this resource" error. It doesn't matter if I build the links as relative or absolute - same problem. I have also tried using back and forward slashes (I am working on a Mac) and that makes no difference.

BUT (this is the weird bit), these same links will suddenly start working mysteriously if I create a ****** link to the same resource in another cell, and use it once (to open the target doc). After that, the link constructed with HYPERLINK() will work... even though I have not altered it in any way. But I would have to do that individually for each linked resource - i.e. open it manually - before my formula-constructed links would work (which reders the whole exercise pointless). I have never seen behaviour like this and am quite perplexed as to what is going on.

A colleauge suggested linking directly to dropbox online, rather than to the local file structure. A constructed link using HYPERLINK() straight to https://dropbox does indeed work, but that's where I struck the issue with the 15 character codes in the filepath. It looks as if DropBox stores each file in its own directory with a randomly generated codename that I cannot reconstruct in my Excel formulas, meaning I cannot dynamically create links to these resources, which is the whole point...

(The dl=0/dl=1 parameter is not a problem. The linked files are pdfs so they render fine in a browser.)

Walter · Answer

Hi @ColinAtInspire; welcome to our Community!

I've extensively discussed this with some other users in the past so I'd recommend that you started from there:

* https://www.dropboxforum.com/t5/Dropbox-files-folders/Hyperlink-not-working-in-Excel/td-p/302754

As per you last concern, even though I'm not sure if it'd work as I guess Excel would still have issues rendering it, but you can force your shared links to download instead of rendering them in a browser by changing the last part of the link(s).

To force a browser to download the contents of a link rather than display it, you can use dl=1 as a query parameter in your URL instead of dl=0.

I hope this helps and please let me know if you have any follow up questions.