I created an Excel file that contains a column whose cells are links to HTML files in a subdirectory of the directory in which the Excel file resides. The links use a relative path structure, ie SubDir\UniqueID.html. When I use the Excel file on my computer, the linked HTML file opens up in my browser with no problem. Each HTML file is only a few kilobytes and loads data from remote sources.
My intention was to share the Excel file with multiple people via OneDrive, as I usually do with files that are frequently updated and must be edited by multiple users. I wanted everybody to be able to view and edit the spreadsheet as well as access the HTML files by clicking on the links. It also contains self-updating statistics, charts, etc. It's a very dynamic workbook that I was excited to share for live collaboration. So I uploaded and shared the entire directory, containing the Excel file and the subdirectory which contains the HTML files. However, the links in the Excel file do not work when using the file online via OneDrive. I suspect this is because of the relative paths in the links.
Each cell in the link column has a formula of the following format:
=HYPERLINK("SubDir\UniqueID.html", "Link to HTML file"). Each cell in the column links to a different file, and the formula for each cell was written by a Python script but they could also be formed using a unique value stored in each row, ie:
=HYPERLINK(CONCAT("SubDir\",A2,".html"), "Link to HTML file"). This is beside the point.
Everything works as expected except for the links, which are a new feature I'm eager to add to my workbooks. I use Python to automate the creation of these Excel files, so it would be ideal if I didn't have to mess with OneDrive's item ID-based addressing or anything but I would be willing to do so if that could make these links work. The code for each HTML file is generated in Python as well, so if there's any way to embed HTML code directly into Excel that would be fine. That's probably way outside the realm of possibility.
If there's no way to make this work as desired, what alternatives should I consider? I have Office 365, SharePoint, Microsoft Teams, etc etc.