search

cristianbuse / VBA-FileTools

Useful methods for interacting with the file system
MIT License
81 stars 23 forks source link

GETREMOTEPATH issue #24

Closed cdavies07 closed 3 months ago

cdavies07 commented 3 months ago

Hi. Thanks for your outstanding work with FileTools. A couple of months ago I tested your GETREMOTEPATH function and it worked flawlessly. I have this week returned to implement the function at scale into my workbook but the function is no longer working for me, it does not return the OneDrive URL, just an empty string into my cell (i'm working in Excel, Microsoft 365).

I cannot fathom what has changed or what I’m doing wrong. I have my local file path in cell A1, ‘C:\Users\Test\OneDrive\ONE DRIVE VIDEO TEST.mp4’. In cell B1 I have the formula =HYPERLINK(getremotepath(A1),"click here") which returns the empty string. Strangely if I copy this formula down yet have empty cells in A2, A3 and so on I get a working ‘http://d.docs.live.net/...’ link that leads to nothing in OneDrive but it at least returns a link. Am i missing something obvious here?

Kind regards

GETREMOTEPATH TEST
cristianbuse commented 3 months ago

Hi @cdavies07 ,

Please try the latest version. I just pushed ad1b4e2bc39c387eefc8217cbea1ebdeced4ae11 as it seems there is a new edge case when parsing SqLite bytes

cdavies07 commented 3 months ago

Thanks for the reply. I've tried the update but unfortunately without success. I think it is something at my end but I can't figure it out. As a note, when I type "=" and start to type "GETREMOTEPATH" I don't get a prompt to autofill the function. Could that be related to my issue?

Thanks

cristianbuse commented 3 months ago

You could remove the Option Private Module from the top of the LibFileTools module but I added that because the module should be consumed by VBA code only.

If you want to call it from an Excel cell then add this function to a new standard code module:

Public Function REMOTE_PATH(ByVal localPath As String) As Variant
    Application.Volatile False
    REMOTE_PATH = GetRemotePath(localPath)
    If LenB(REMOTE_PATH) = 0 Then REMOTE_PATH = CVErr(xlErrNA)
End Function

This will swallow up any unhandled errors while also returing #N/A if the remote path is not found. Moreover, you do not want volatile functions which calculate all the time like TODAY or OFFSET hence the Application.Volatile False line.

For User Defined Functions (UDFs) Excel does not give you the parameter list but you can press Ctrl+Shift+A after you typed =REMOTE_PATH( in the cell. Alternatively, you can use the fx button. Of course, this method only has one parameter so not really useful here.

If you need a version of the UDF that handles multiple paths in one go (spill) then let me know.

Many thanks!

cdavies07 commented 3 months ago

Thanks for that. Unfortunately still no luck. I cannot get the OneDriveURL's for my local file paths. I confirm using the =REMOTE_PATH function that I get "N/A", so perhaps there could be a permissions issue?

remote path test
cristianbuse commented 3 months ago

I just pushed a new commit. Could you please:

Update

I have now pushed a UDF_FileTools module for use as Excel UDFs. It only has 4 functions for now:

The nice thing is that these work with spill in Office 365 - meaning you can pass an entire range of paths instead of just one value.

cdavies07 commented 3 months ago

Hi Cristian,

Thanks for your help today. I'm not an experienced VBA user so i need a little guidance, sorry!

How do I run the CreateODDiagnosticsFile method?

Best regards, Chaz

From: Cristian Buse @.> Sent: 19 June 2024 5:07 PM To: cristianbuse/VBA-FileTools @.> Cc: cdavies07 @.>; Mention @.> Subject: Re: [cristianbuse/VBA-FileTools] GETREMOTEPATH issue (Issue #24)

I just pushed a new commit. Could you please:

— Reply to this email directly, view it on GitHubhttps://github.com/cristianbuse/VBA-FileTools/issues/24#issuecomment-2178935910, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BJJLGX3WWHDMW4ELLDIV7YDZIGNDFAVCNFSM6AAAAABJRMJIJ2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNZYHEZTKOJRGA. You are receiving this because you were mentioned.Message ID: @.***>

cristianbuse commented 3 months ago

How do I run the CreateODDiagnosticsFile method?

Download the latest LibFileTools module and replace the one you have. Then open the code module in VBA, navigate to the bottom of the module, click within the method and then press F5 or the Run button in the VBA IDE (VBE).

cdavies07 commented 3 months ago

Thankyou. Please find the diagnostics file attached.

Best regards, Chaz

From: Cristian Buse @.> Sent: 19 June 2024 7:30 PM To: cristianbuse/VBA-FileTools @.> Cc: cdavies07 @.>; Mention @.> Subject: Re: [cristianbuse/VBA-FileTools] GETREMOTEPATH issue (Issue #24)

How do I run the CreateODDiagnosticsFile method?

Download the latest LibFileTools module and replace the one you have. Then open the code module in VBA, navigate to the bottom of the module, click within the method and then press F5 or the Run button in the VBA IDE (VBE).

— Reply to this email directly, view it on GitHubhttps://github.com/cristianbuse/VBA-FileTools/issues/24#issuecomment-2179216190, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BJJLGX7ZJLICEQVQAPSKO3LZIG53JAVCNFSM6AAAAABJRMJIJ2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNZZGIYTMMJZGA. You are receiving this because you were mentioned.Message ID: @.***>

Win VBA7 x64

Accounts found: 1

Name: Personal ID: 70d7aed0f50e770d Has DAT: False Is Valid: True

Providers found: 1

Base Mount: ODB Is Business: False Is Main: False Mount Point: ODB\ Sync ID: Web Path: https://d.docs.live.net/70d7aed0f50e770d/

cristianbuse commented 3 months ago

I think I figured out what the problem is.

Go to folder: C:\Users\[Your User]\AppData\Local\Microsoft\OneDrive\settings\Personal Replace [Your User] in the above or directly use: %localappdata%\Microsoft\OneDrive\settings\Personal

There should be a file called 70d7aed0f50e770d.ini. Open it. The first line should look like this: library = 1 4 2711A14D8F76D7DF!113 2801274321 "SkyDrive" Me personal "C:\Users\CBuse\OneDrive" 1 63ae273f-7f81-4127-9f8e-08dd65c35108 - 1135899917012955 1794154389 00000000-0000-0000-0000-000000000000

I suspect yours looks slightly different and I need to add some extra logic. Could you please give me that first line? Thanks!

cdavies07 commented 3 months ago

Good morning Cristian,

Yes, it is looking a bit different to what you provided. Here is my line:

libraryScope = 0 b7bed0d57e2a4cd4bd2bb77ba5051664 5 "MySite" "ODB" 2 "https://my.microsoftpersonalcontent.com/personal/70d7aed0f50e770d" "84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa" 81c30d972ac242d39d8c98a6b052cc8b c1f7be3ba9a141c68f6d559503a871a5 29cd7e585ccc42f6bca8473c2a4a0370 1693908762 "C:\Users\Test\OneDrive" 1 d15e5444-7caa-403a-9395-92f68e8b9135 - 844424930233491 2085292469 00000000-0000-0000-0000-000000000000

Kind regards, Chaz

From: Cristian Buse @.> Sent: 20 June 2024 9:45 AM To: cristianbuse/VBA-FileTools @.> Cc: cdavies07 @.>; Mention @.> Subject: Re: [cristianbuse/VBA-FileTools] GETREMOTEPATH issue (Issue #24)

I think I figured out what the problem is.

Go to folder: C:\Users[Your User]\AppData\Local\Microsoft\OneDrive\settings\Personal Replace [Your User] in the above or directly use: %localappdata%\Microsoft\OneDrive\settings\Personal

There should be a file called 70d7aed0f50e770d.ini. Open it. The first line should look like this: library = 1 4 2711A14D8F76D7DF!113 2801274321 "SkyDrive" Me personal "C:\Users\CBuse\OneDrive" 1 63ae273f-7f81-4127-9f8e-08dd65c35108 - 1135899917012955 1794154389 00000000-0000-0000-0000-000000000000

I suspect yours looks slightly different and I need to add some extra logic. Could you please give me that first line? Thanks!

— Reply to this email directly, view it on GitHubhttps://github.com/cristianbuse/VBA-FileTools/issues/24#issuecomment-2180030164, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BJJLGX3TWWHPMJKYXXDZW5TZIKCALAVCNFSM6AAAAABJRMJIJ2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCOBQGAZTAMJWGQ. You are receiving this because you were mentioned.Message ID: @.***>

cristianbuse commented 3 months ago

Good Morning Chaz,

Please try the latest commit. It should work now.

cdavies07 commented 3 months ago

Hi Cristian,

Awesome, thank you! I've just run a quick test and seems to be back to working fine.

Out of interest, was it an Excel update that made it stop working?

Thanks so much.

Kind regards, Chaz

From: Cristian Buse @.> Sent: 20 June 2024 12:19 PM To: cristianbuse/VBA-FileTools @.> Cc: cdavies07 @.>; Mention @.> Subject: Re: [cristianbuse/VBA-FileTools] GETREMOTEPATH issue (Issue #24)

Good Morning Chaz,

Please try the latest commit. It should work now.

— Reply to this email directly, view it on GitHubhttps://github.com/cristianbuse/VBA-FileTools/issues/24#issuecomment-2180324761, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BJJLGXYVE2TLZHIKTCLJ2OLZIKUBPAVCNFSM6AAAAABJRMJIJ2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCOBQGMZDINZWGE. You are receiving this because you were mentioned.Message ID: @.***>

cristianbuse commented 3 months ago

Hi Chaz,

Out of interest, was it an Excel update that made it stop working?

It was a OneDrive update. All the files you see in %localappdata%\Microsoft\OneDrive\settings are being used by the OneDrive app. All my code does is to read those files for specific info.

Your use case is actually simple. But, the code I wrote in collaboration with Guido is actually capable of reading other mappings like:

The code relies on the files in the mentioned folder and if Microsoft decides to make changes then all I can do is to update the code to accomodate those changes.

Thank you for your feedback and your help!

cristianbuse commented 3 months ago

Don't forget to download the UDF_FileTools module as well. It's spill capable in Excel