Local/Remote Path Conversion Bug

[SophiaS94]

I did not run the DiagnosticsFile as I use this code on my business laptop and want to avoid unknown sources as much as possible. But I think the error should be obvious and my description hopefully detailed enough to solve it.

Describe the bug Hi, I have the following issue with the GetLocalPath-function:

I want to get the local Path (C:-Path) for my quote template document by using your GetLocalPath-function with the "ThisWorkbook.FullName"-Property that delivers a https://-Path. Please note that the path to this quote template is constantly changing, so I don't use the URL directly, but rather use the “ThisWorkbook.FullName” property instead:

  QuoteFilePath = GetLocalPath(ThisWorkbook.FullName)

I used the GetLocalPath-function of the latest version (Jul 8). But in the line where the keys are added I get an error message due to duplicate "r"-paths.

In this code line of the GetLocalPath-function I get the error: If InStr(1, Path, r, 1) = 1 Then bp.Add Key:=d(2), Item:=Replace(Replace(Path, r, i, , 1), "/", ab)

Following error message occurs: "This key is already assigned to an element of this list."

I have the checked with MsgBox which "i"-paths and "r"-paths I get and if a duplicate is occuring as indicated by this error message (see attached screenshot):

It shows that the last two r-paths are the same resulting in this error. I know that the "Team-CAT-PAT - General" and the "Team-CAT-PAT - VAS" part of the path is probably the source of this problem but I cannot change the name of these folders as these are automatically generated folder paths due to Microsoft Teams. For explanation, the "Team-CAT-PAT" is a Microsoft Teams Team with two channels called "General" and "VAS".

I hope you can help me with this problem as I am very new to Excel VBA and this is my first project with it. Please let me know if you need more information or if I still have to run the DiagnosticsFile for more details.

To Reproduce Steps to reproduce the behavior:

1. Create a MS Teams Team "Team-CAT-PAT" -->Create to channels within the team called "General" (per default available) and "VAS"
2. Save an excel file under the "VAS" channel of the "Team-CAT-PAT"-Team (saved automatically in the sharepoint)
3. Try to get via excel vba in the excel file the local path by calling within the "ThisWorkbook" section:

   Private Sub Workbook_Open()
   Dim QuoteFilePath
   QuoteFilePath = GetLocalPath(ThisWorkbook.FullName)
   End Sub

4. Check if the GetLocalPath-function works
5. See error as described above

Additional context N/A

[cristianbuse]

Hi @SophiaS94

The code you are showing in your screenshot is not part of this library, neither is any of the code snippets you wrote. So, I cannot identify what the problem is from those.

Anyway, have you syncronized your Sharepoint folder to OneDrive? If you did not then it makes sense it does not work. Open your desired Sharepoint folder (or a parent folder) in the web browser (e.g. Chrome) and then press Sync.

Once a folder is syncronized then the GetLocalPath will work.

[SophiaS94]

Hi @cristianbuse,

Thanks for the swift response.

Apologies for not showing the code soucre. I copied it from the page from Guido: https://gist.github.com/guwidoe/038398b6be1b16c458365716a921814d

Yeah, I have synchronized both folders - "VAS" and "General"

[cristianbuse]

@SophiaS94

Still, the code in your screenshot is not in Guido's gist either. Are you sure you are using the latest version?

[SophiaS94]

@cristianbuse

I checked in parallel and it seems that I really used an older version by mistake, since I could not find the former mentioned code section "If InStr(1, Path, r, 1) = 1 Then bp.Add Key:=d(2), Item:=Replace(Replace(Path, r, i, , 1), "/", ab)" in Guido's latest code version.

Nevertheless, I tryed the new code version now and it seems that the GetLocalPath-Function still doesn't work as I get the same https:-URL back as it was (unmodified). As described by Guido's comments I think the path is not found by the GetLocalPath-Function and thus the former URL is given back. I ensured synchronization of the Sharepoint folder to OneDrive.

[cristianbuse]

@SophiaS94

Try this repo instead of Guido's code and see if that works for you. Download the zip then extract and import the LibFileTools module into your project. The name of the function is the same, just make sure you remove Guido's code so that my version runs instead. Test again and let me know if it works.

[SophiaS94]

@cristianbuse

Thanks for your help so far.

I imported the LibFileTools module and changed the function calling to this:

Unfortunately, it seems that the GetLocalPath-function provides an empty path back:

Here the result of the CreateODDiagnosticsFile-function:

[cristianbuse]

@SophiaS94

I can see why it's not working. The last web path from your last screenshot is: https://south32.sharepoint.com/sites/Team-CAT-PAT/Shared Documents/V / but it should be like: https://south32.sharepoint.com/sites/Team-CAT-PAT/Shared Documents/VAS/

In your %localappdata%\Microsoft\OneDrive\settings\Business1 folder, you should see a file called f72ab001...ff71.ini. Please open it and see what line(s) is related to your VAS folder (could be libraryScope, libraryFolder or AddedScope) . Then please paste the line(s) here as text, and of course replace any sensitive names (e.g. username) with the X character.

[SophiaS94]

@cristianbuse

Alright, please see as follows:

libraryScope = 0 48f9f7d3402743f3bf8f8534240a2cc9 5 "MySite" "ODB" 2 "https://**X1**-my.sharepoint.com/personal/**X2**_**X3**_de" "f7914515-8e8a-4335-af8c-4d3b19c10d5d" 440f886f198c4ef4b68220fa83565e05 07b93cbb4b44456a833ce757e6f51eeb 20533f222682487b8150a495f98a0183 1715669418 "C:\Users**X1\OneDrive - X3**" 1 5677c274-d50c-4a92-b217-575c41353d04 - 562949953607302 1013400387 00000000-0000-0000-0000-000000000000

libraryScope = 1 1af24deddbc44fadbf18f8cf1c4cc17d+1 5 "BUEZ" "Documents" 4 "https://**X1**.sharepoint.com/sites/BUEZ" "f7914515-8e8a-4335-af8c-4d3b19c10d5d" e418cfe482e041bbb72f252aa77d43ee 3be5c10659464a2ebf1f3e6b7de24bfb 8b4ee34de5854bf9a252ede5bea82e4e 0 "" 1 00000000-0000-0000-0000-000000000000 - 0 0 00000000-0000-0000-0000-000000000000

libraryScope = 2 539136c305474c7cbd48fe709b344194+2 5 "Team-CAT-PAT" "Dokumente" 4 "https://**X1**.sharepoint.com/sites/Team-CAT-PAT" "f7914515-8e8a-4335-af8c-4d3b19c10d5d" 1f4d3eacc2af441ba58bf4152b4794b4 bdeba73af7cf427a9b5d623ae16d4058 53206355b1de497ea6171c8ef912cb28 0 "" 1 00000000-0000-0000-0000-000000000000 - 0 0 00000000-0000-0000-0000-000000000000

libraryFolder = 0 1 4885e34201294813aafe7bd8e795bee8+1 1719382344 "C:\Users**X2\X3**\BUEZ - General" 1 "General" 17a62535-ec9f-438c-aef9-b8309b860609 5629499534389751 1013400387 00000000-0000-0000-0000-000000000000

libraryFolder = 1 2 ee852d711fc6485ea3f2e5ca64fa01c8+2 1719575090 "C:\Users**X2\X3**\Team-CAT-PAT - General" 1 "General" 43ea4736-b00f-42cf-bbfa-9e38e30824c2 7036874417962489 1013400387 00000000-0000-0000-0000-000000000000

libraryFolder = 2 2 3e872e5b277f49a296f141b44080cf74+2 1721984678 "C:\Users**X2\X3**\Team-CAT-PAT - VAS" 1 "VAS" e5fa1399-011c-480a-b3e3-b317c1bc8f85 562949953714171 1013400387 00000000-0000-0000-0000-000000000000

installID = 1 originatorID = ecb86a37-c63b-4599-928c-727f30b4a932 lastRefreshTime = 1724825680 requestsSent = 7 bytesTransferred = 86066 uploadLimitKbPerSec = 0 downloadLimitKbPerSec = 0 uploadSpeedAutoLimited = false edpManaged = false edpManagedSince = 0 needsPlaceholderTransition = false pendingPermissionInheritanceChange = None OfficeOriginatorID = 43361779-aedd-44a9-bbfd-04b57b735f51 lastKnownOSVersion = 10.0.22631 Subscription = 1 539136c305474c7cbd48fe709b344194+2 0e4f86da-959f-4a1a-a0c1-082e4ddd32bf Subscription = 1 48f9f7d3402743f3bf8f8534240a2cc9 cbe5c966-a83c-49ae-9760-813f8bf1eeae Subscription = 1 1af24deddbc44fadbf18f8cf1c4cc17d+1 29c34a54-9020-4692-a4a8-e04625126286

[cristianbuse]

Thanks @SophiaS94 ,

Based on the above, you should have a file called: ClientPolicy_53206355b1de497ea6171c8ef912cb281f4d3eacc2af441ba58bf4152b4794b4.ini in the same folder. If you open this file, you should see a row starting with DavUrlNamespace = https://.... What is the URL on that row?

[cristianbuse]

This should be the final thing I ask from you.

In the same folder there is a file called: SyncEngineDatabase.db

This file contains the folder/file names syncronized by OneDrive, under a SqLite database file. It seems that both my and Guido's code is not parsing the file correctly, in your case. If the folder/file names are not confidential then please email me the db file (archived as zip or 7z) to cristian.buse@yahoo.com Once I figure out the issue I will delete the file anyway.

OR

If you have any concerns to share the file then please do the following steps - this should give me everything I need to fix the issue: 1) Open a web browser (e.g. Chrome) 2) Go to hexed.it 3) Drag your db file into the middle area 4) Paste the following ID in the 'Search for' box: 3e872e5b277f49a296f141b44080cf74+2 5) Make sure only the 'Text' and 'Little-endian' boxes are ticked 6) Click 'Find next' So far, it should look like this: 7) If the text found is preceded by zeroes as in the below screenshot (I searched for a different text of course, as an example) then make a screenshot of each section where you find that text (with some extra rows at the top and bottom) 8) Repeat steps 6-7 until the text is not found anymore 9) Give me the screenshot(s)

Based on your db file hexadecimal values I will try to figure out the issue. Thanks!

[SophiaS94]

Thanks @SophiaS94 ,

Based on the above, you should have a file called: ClientPolicy_53206355b1de497ea6171c8ef912cb281f4d3eacc2af441ba58bf4152b4794b4.ini in the same folder. If you open this file, you should see a row starting with DavUrlNamespace = https://.... What is the URL on that row?

@cristianbuse Here it is: DavUrlNamespace = https://X.sharepoint.com/sites/Team-CAT-PAT/Shared Documents/

[SophiaS94]

This should be the final thing I ask from you.

In the same folder there is a file called: SyncEngineDatabase.db

This file contains the folder/file names syncronized by OneDrive, under a SqLite database file. It seems that both my and Guido's code is not parsing the file correctly, in your case. If the folder/file names are not confidential then please email me the db file (archived as zip or 7z) to cristian.buse@yahoo.com Once I figure out the issue I will delete the file anyway.

OR

If you have any concerns to share the file then please do the following steps - this should give me everything I need to fix the issue:

1. Open a web browser (e.g. Chrome)
2. Go to hexed.it
3. Drag your db file into the middle area
4. Paste the following ID in the 'Search for' box: 3e872e5b277f49a296f141b44080cf74+2
5. Make sure only the 'Text' and 'Little-endian' boxes are ticked
6. Click 'Find next' So far, it should look like this:
7. If the text found is preceded by zeroes as in the below screenshot (I searched for a different text of course, as an example) then make a screenshot of each section where you find that text (with some extra rows at the top and bottom)
8. Repeat steps 6-7 until the text is not found anymore
9. Give me the screenshot(s)

Based on your db file hexadecimal values I will try to figure out the issue. Thanks!

I have sent you an email accordingly.

[SophiaS94]

@cristianbuse

I tryed the GetLocalPath function in my file again and it seems that it is now working! It's really strange and I do not know why it is working now as I did not change anything (and full synchronization was also shown before).

Please see here the newest diagnostic file:

[cristianbuse]

Thanks @SophiaS94

I can see in the new screenshot, the name is now correct i.e. VAS and not V like in the previous diagnostic data.

My guess is that you either got an OneDrive automatic update in the meanwhile, or, because you syncronized other files, the order of the file names has changed in the db file and the code now picks the correct one. I just emailed you as well.

Thanks!

[SophiaS94]

@cristianbuse

Yeah, it seems so! Thanks for your help here.

In parallel, I have sent you the missing screenshots via e-mail.

BR Sophia