Excel and VBA, how to use local path instead a onedrive url?

Question

I have at both my office PC and Home PC y personal Onedrive

Office has Office 2016 while hoe has Microsoft 365

This code, at office return correct local path, but at home it return an URL (https://d.docs.live/...)

Ruta(0) = Me.Path & "" & "Listado unidades.xls"

At both computers I have onedrive set to store localy all files

Of course my problem is I need local path (at office PC can do it but at home no) as later at my code need use that variable for other function

Answer 1

MyPath = Me.Path

if Left(MyPath,6) = "https:" then MyPath = "C:\Whatever"

Ruta(0) = MyPath

Answer 2

Hi leo3487,

Thanks for visiting Microsoft Community.

I tested the scenario you described. I will explain why this happens and provide you with a solution:

When you save a file directly to OneDrive, it is uploaded to the cloud rather than saved locally (meaning you won't find it in your local OneDrive folder). As a result, when you run any VBA code that retrieves the current file's path, it displays a web address starting with https://d.docs.live/. You can confirm this by hovering over any recently edited file in Excel's File-Home-Recent section, where you'll see if the file is actually stored locally.

To resolve this issue, you need to first save the file using the "Save As" option, browse to any folder on your local machine (including your local OneDrive folder), and successfully save it there. After doing so, you can use code to retrieve the local path.

I hope this information is helpful. If you have any further questions, please feel free to ask.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 3

But at Onedrive I have set to save local at both PC

Then at Office 2016 (at office pc) I get local path, but at Microsoft 365 (home PC) I get URL

Answer 4

Hi leo3487,

Thank you for your reply.

I understand the situation. The Excel file on which you're running the VBA macro on your Home PC is not the local copy but the one stored in OneDrive, even though both files have the same content. These are considered two separate files. You need to ensure that the file you're using to run the macro on your Home PC is the one saved locally. To check this, in Excel, click on the filename at the very top, and make sure that the "Location" indicates a local folder on your computer rather than a folder within OneDrive (for example, something like "OneDrive-Personal>Documents").

If you have any further questions, please feel free to ask.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 5

ok, I should use other folder out of Onedrive

AS I have not the option to save to pc (as 2nd image of @Jonathan.Z