I am actually at the end of my rope here, I'd really appreciate any insight however small. The scenario is as follows. My company hosts the majority of its data on SharePoint. For config management, the current solution is a macro-heavy Excel file (let's call it control centre, or CC) with a GUI that employees interact with to edit other Office files, Excel and otherwise, hosted on the same SharePoint server. In order to access these other files, I need to provide a local path, as the majority of VB functions will obviously not take SharePoint URLs as valid input.

Now, with File Collaboration enabled, .Path will not return the local path of the file anymore, instead returning the URL. This is where the trouble begins.

My current brute-force solution is to convert that URL directly into a local path by making several assumptions about the sync location of everyone's OneDrive, which works, but barely, and requires constant adjustments that eat into my time.

I have attempted several solutions that would more generally address this issue but none of them work. Here's some attempts:

  1. Execute the Get-Location Powershell command from VBA directly to read the path. Doesn't work, always returns system default, which is completely useless.
  2. Write an external Powershell script that can read and return its location, place it in the same folder as CC, and execute it from the CC. This works in so far that manually running the script returns the correct address, but actually calling it from the CC is impossible for the same reason that reading the CC's own path is. If I know the script's path, I no longer need the script.
  3. An alternative solution would be a brute force search for the executable script in the company sync folder, but this would end up also making assumptions about the folder names, which runs the risk of being equally as unstable as the current solution.

Any ideas as to how this problem could be reliably solved would be appreciated.

  • When you say, "edit other Office files, Excel and otherwise, in the database" which database are you referring to? Jun 9, 2022 at 10:36
  • @CallumCrowley I just meant that they're hosted on the same SharePoint server, that's all. Sorry for the unclear phrasing! Jun 9, 2022 at 10:48
  • Honestly, it sounds as though you have outgrown this architecture and need a better solution overall. The files are stored in SharePoint, so of course the actual location is their URL not the location of synchronised files on a user's machine. Have you looked at the Power Platform and other M365 applications? Jun 9, 2022 at 10:59
  • Unfortunately, maintaining the system is not my main assignment and I had a lot of trouble securing time and resources to invest in its development over other concurring projects in the past. I don't think adopting a completely different architecture is possible at this time, though I doubtlessly would love to explore those options. If I can't figure out a method of accessing local sync folder information, the current solution will have to remain in place. Jun 9, 2022 at 11:19
  • A reliable solution for this issue now finally exists here.
    – GWD
    Oct 4, 2022 at 11:50


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.