Get Local Path for OneDrive synced file

**Coley356** · 03-02-2022, 03:15 PM

I am simply shocked that I have not been able to find a simple solution to this. Excel and OneDrive are both Microsoft products so you would think they would work better together.

I am looking for a simple way to get the path of the VBA file (activeworkbook) and use it to open another file in the same folder. The problem is when the file is stored and synced in a onedrive folder.

METHODS I HAVE TRIED:
METHOD 1
I tried CurDir, but this has a tendency to not be correct. I dont really understand what this is supposed to be retrieving because it is not the current directory of the active file. It seems to use the directory of the last location that was selected from within Excel. So if i open my file from within excel, then I am good to go until i open another file location from within excel, then the CurDir will be that location until the next one, and so on.

METHOD 2
Similar results with this most recent method i tried. i was thinking i finally found something that seemed to work, but later realized that it seemed to be behaving the same as CurDir

Please Login or Register  to view this content.

METHOD 3
I recently considered using the following to stop/start onedrive during the process, but not knowing the system of any given user, i thought it best to not use this method.

Please Login or Register  to view this content.

METHOD 4
I also looked for a way to swap the path from OneDrive sharepoint path to local synced path. so if ThisWorkbook.Path is a URL, then replace the "https://[sharepoint]/personal/[user]/Documents/" with the users local path of their onedrive, like C:\Users\[user]\[OneDrive]\ (and of course swap the remaining "/" to "\") but i have not been successful in figuring this method out.

Does anyone here know of a way that will easily accomplish what i am trying to do?
I have come across some posts that claim to do this for varying purposes, but they all have been very long code that I cant follow so I wouldnt know how to adapt to my needs.
Hoping someone has a simple creative solution to this.

**dangelor** · 03-02-2022, 06:36 PM

Possibly...

Please Login or Register  to view this content.

**Coley356** · 03-03-2022, 10:21 PM

Thank you dangelor! This helped me to make METHOD 4 work (at least for now).

I am currently accomplishing this using formulas on a worksheet.
Perhaps you could help to simplify it in vba code ... without using range formulas like i have here???

Please Login or Register  to view this content.

ALSO, wondering if you could possibly answer another question which pertains to the next step in my routine.
now I am searching localpath for a workbook that starts with "Unity Plots *".

Please Login or Register  to view this content.

I have this currently working to:

if it IS NOT found:
- msgbox
if it IS found:
- msgbox if it is already open
- open the workbook

but what i have not figured out is how to verify that there is only one workbook in that path that has a filename that starts with "Unity Plots *".

**Sintek** · 03-04-2022, 01:22 AM

Perhaps this will help...Not sure of your actual requirement...

Please Login or Register  to view this content.

**dangelor** · 03-04-2022, 08:02 AM

Not quite sure what you're trying to accomplish (would need entire code and workbook) but to get the full path and the full name of a file, whether it is local or on OneDrive, use...

Please Login or Register  to view this content.

On my system it returned the path as :
"https://xxxxxxx-my.sharepoint.com/personal/xxxxxxxxxxxx/Documents"

and the full name as :
"https://xxxxxxx-my.sharepoint.com/personal/xxxxxxxxxxxx/Documents/TestFile.xlsm"

**Coley356** · 03-04-2022, 11:59 AM

Originally Posted by sintek

Please Login or Register  to view this content.

Thank you sintek,
This looks a lot simpler than what I got going on.
I notice that you use " 5)(4)," when " 7)(6)," is what seemed correct on my machine.
I was concerned that hard coding this number might become a problem depending on a users system.
Is there a way that you know of to determine the correct split location on any given machine? or should they all be at the same location (if using windows)?

also, i am not very familiar with using boolean...can you explain what the Found = False is meant for on the above routine?

As for the next routine...

Originally Posted by sintek

Please Login or Register  to view this content.

essentially, what I am looking for is to find out if there is more than 1 file in the directory where File.Name Like "Unity Plots*" is true.
If there is more than 1 - then give MsgBox and exit sub
If there is only 1 - then first check if it is already open. if it IS open, then MsgBox. if its NOT open, then open it and assign it Dim inputWB As Workbook
If file is not found - then MsgBox, like you already show.

**Coley356** · 03-04-2022, 12:06 PM

Originally Posted by dangelor

Not quite sure what you're trying to accomplish (would need entire code and workbook) but to get the full path and the full name of a file, whether it is local or on OneDrive, use...

Please Login or Register  to view this content.

dangelor,
Thanks again for all your help!

**Sintek** · 03-04-2022, 12:26 PM

I notice that you use " 5)(4)," when " 7)(6)," is what seemed correct on my machine.

Has always been stable for me...

with using boolean...can you explain what the Found = False is meant for on the above routine?

The Boolean variable is used for True | False logic

For your explanation...Something like this perhaps...

Please Login or Register  to view this content.

**Coley356** · 03-07-2022, 05:43 PM

Thank again sintek
This is exactly what i needed it to do.
As for the potential issue on the hardcoding of the split location for the onedrive path, I guess I will just get a few users to test to see if there are any discrepancies between machines...and go from there.

**JEC.** · 03-07-2022, 05:55 PM

I think you can also get the local path by selecting with application.filedialog.
Users can sync files/folders differently, which leads to different paths.

Please Login or Register  to view this content.

**Coley356** · 03-08-2022, 04:16 PM

Thanks for the feedback JEC.
that is the method i was using previously...this post was my way to see if I could avoid that.

**Sintek** · 03-09-2022, 10:33 AM

Check out this link here

Get Local Path for OneDrive synced file

LinkBack

Thread Tools

Rate This Thread

Display

Get Local Path for OneDrive synced file

Re: Get Local Path for OneDrive synced file

Re: Get Local Path for OneDrive synced file

Re: Get Local Path for OneDrive synced file

Re: Get Local Path for OneDrive synced file

Re: Get Local Path for OneDrive synced file

Re: Get Local Path for OneDrive synced file

Re: Get Local Path for OneDrive synced file

Re: Get Local Path for OneDrive synced file

Re: Get Local Path for OneDrive synced file

Re: Get Local Path for OneDrive synced file

Re: Get Local Path for OneDrive synced file

Thread Information

Users Browsing this Thread

Similar Threads

Open file that synced from sharepoint to onedrive

Opening Excel Macro File from OneDrive not synced with Local PC - Path Issue

Find path to local synced sharepoint folder

VBA Code to copy Files From Onedrive Folder To Local Path

[SOLVED] Dynamic File path for OneDrive

How to link to a shared OneDrive file (not synced but online) in Power Query

[SOLVED] Get OneDrive online shared file and saved it to your local disk

Bookmarks

Bookmarks

Posting Permissions