Excel-VBA-Diary
commented
11 months ago Thank you for your prompt and thorough report.
There are so many solutions to this problem, but I have been frustrated that none of them work, especially when it comes to OneDrive synced to SharePoint.
As already mentioned, each solution has its prerequisites, and as you point out, not all barriers have been removed.
I have tried the VBA code you posted on GitHub, and since I don't have a MAC environment, my attempts are in a Windows environment, but I can confirm that it works very well.
I hadn't thought of the idea of focusing on OneDrive setting files, for example, a folder like:
C:\Users\
guwidoe
This will be a longer issue since I have spent hundreds of hours on this problem and am always excited to see new and creative solutions like the ones presented here.
First of all, thank you very much for sharing these solutions! I think they are very interesting and innovative approaches and I did not expect to find such novel approaches to this problem anymore, as there are already so many attempts at solving this available online.
Solution 1 (via recently used files):
This is a very creative and great solution for this problem which I haven't seen before. In my testing, it worked quite reliably, but it does have some drawbacks, one of which you already pointed out in your README.md which is that "Show Recently Opened Items" needs to be enabled.
Issue 1:
It also does not work on Workbooks that are opened with the
Workbooks.Openmethod. Theoretically, this method offers an optional parameter that lets the user decide whether to add the opened Workbook to the list of recently used files (AddToMru), however unfortunately the default value isAddToMru:=False.Even more unfortunately, there seems to be a bug in the
Workbooks.Openmethod so that even if specifyingAddToMru:=True, the shortcut in the recently used files list was not created in my testing, makingGetThisWorkbookLocalPath1unsuitable for Workbooks opened via this method.Issue 2:
There seems to be an issue with the
fso.FileExists()method. When I tested the function on a file with a local path like this:the logic worked correctly until the line
If Not fso.FileExists(filePath) Then Exit Function, where even thoughfilePathcontained the correct path, the function was exited and nothing was returned.Suggestion 1:
I think you could even consider removing this line completely, because while it is possible that
filePathcontains the wrong path,If Not fso.FileExists(filePath) Then Exit Functioncan not always prevent you from returning that wrong path.Issue 3, (consequence of Issue 1)
Consider the following example:
You open a file called
test.xlsmmanually from the locationC:\users\username\OneDrive\folder1\test.xlsm. There will now be a link calledtest.xlsmin the recent items folder.Now you close that file, and open another file called
test.xlsmbut this time from a different locationC:\users\username\OneDrive\folder2\test.xlsmAND by using theWorkbooks.Open()method directly from VBA.Now, running
GetThisWorkbookLocalPath1from the VBA project of that second file will return the wrong pathC:\users\username\OneDrive\folder1\, because the shortcut in the recent items folder has not been overwritten due to the usage ofWorkbooks.Open(). Obviously in this caseIf Not fso.FileExists(filePath) Then Exit Functionalso wouldn't prevent you from returning the wrong path.Moreover, if
GetThisWorkbookLocalPath1is used correctly, keeping in mind its restrictions which is that it does not work on Workbooks opened using theWorkbooks.Openmethod, and if the second file would therefore have been opened manually, then the link would have been replaced and the correct path would be returned, regardless ofIf Not fso.FileExists(filePath) Then Exit Function.Solution 2 (via
Environ()orShell.Application.Windows)The solution using
Environ()I have seen countless times already, and there are countless issues with it, some of which you try to overcome by using a method I haven't seen before as an alternative. But first things first:1)
Case LCase(ThisWorkbook.Path) Like "https://d.docs.live.net/????????????????"This should work as implemented. I overlooked something in the code at first and therefore pointed out an issue that wasn't there. This case should deal with files in the personal OneDrive folder that are not located in any subfolder, which is a pretty niche use case.
2)
Case LCase(ThisWorkbook.Path) Like "https://*.sharepoint.com/personal/*microsoft_com/documents"This case should deal with files in the personal library of a business OneDrive account that are also not located in any subfolder, which is again a bit of a niche use case.
This case will never be triggered for me because my business account email address doesn't end with "microsoft.com", and as far as i can tell, the "microsoft_com" is a part of the account email address. Maybe the line should be
but of course, this could then also trigger for subfolders of the personal library of a business OneDrive account that are called "documents", so this case could lead to wrong return values. Maybe it would be best to ditch this
Caseentirely, as it is only a fairly niche case anyway and it might as well be handled by theCase Elsepart.3)
Case ElseFor this case, this is again a very creative solution that I have not seen before, kudos!
Again, there are drawbacks, some of which you already pointed out in your README.md, like that the folder must be open in the Windows Explorer.
Another drawback you didn't mention is that the logic can potentially return the wrong path if multiple explorer windows to multiple different folders with the same name are open.
In this case, because you included the
If Not fso.FileExists(strPath & "\" & ThisWorkbook.Name) Then Exit Functioncheck, it can likely happen that nothing is returned.However if multiple of these folders also happened to contain a file that has the same name as the current workbook, the function might return a wrong path. I do not see a way of overcoming this issue, unfortunately.
Issues with the provided implementation of the
DecodeURLfunction:1) The function uses early binding and requires a reference to the Microsoft HTML Object Library, maybe this should be mentioned in the documentation. Alternatively it would be even better to use late binding for portability.
2) Even with the reference added, this function causes an automation error for me at this line:
even for some very simple inputs like
DecodeURL("%F6"). I don't know why that is, because for exampleDecodeURL("%20")works without issues but I suggest using a completely different implementation ofDecodeURL.Solution 3 (using SendKeys):
Unfortunately, I could not properly test this solution because it was flagged as dangerous by my antivirus, and calling
GetThisWorkbookLocalPath3led my antivirus to immediately terminate the entire Excel process.However, I know that using
SendKeysis a viable option, and it is actually not necessary to use PowerShell orCreateObject("Wscript.Shell").Runat all. For a detailed description of how this can be achieved in a better way, by using the built-inApplication.SendKeysplease have a look here.More General Remarks
In your README.md, you write:
This is not quite true. You just need a lot more information about the OneDrive setup than what is available in the environment variables or the registry. However, there is enough information to make this work for every single case in the OneDrive settings files. On Windows, they are located in
%localappdata%\Microsoft\OneDrive\settings.The exact string processing process and information extraction from the settings files is unfortunately extremely complicated and goes FAR beyond the scope of this issue.
However, there are already two solutions that use this approach available under the MIT license, and I encourage you to try them out. More information on both of these can be found here: https://stackoverflow.com/a/73577057/12287457
Final Remarks and Questions
Of your solutions, I think the first one is the best, as its limitations and drawbacks are the most manageable.
However, the solutions presented here: https://stackoverflow.com/a/73577057/12287457 have none of these drawbacks and in my opinion even this solution which uses the registry has less drawbacks than any of the solutions presented in this repository.
Considering this and since obviously a lot of thought went into the solutions from this repository, I have some questions:
Did you look for solutions online before coming up with these approaches?
If yes did you find the ones I linked? Specifically these two:
Did you try these solutions and did they work?
If not, can you share the case where they didn't work?
If yes, why did you come up with your alternative solutions?
Finally, sorry for the very long issue. Nonetheless, I hope you find it helpful. If you have any questions about any of the things I mentioned in this issue, please don't hesitate to ask and I will do my best to clarify!