VBA to Access a OneDrive file as a data source

I know that this question has been asked several thousand times. I've only read 15 of them this morning.

Most of the answers involve SHAREPOINT. What does onedrive have to do with sharepoint. I do not have a business account, so I do not have sharepoint.

Has anyone actually done this from a microsoft 365 home account?

The information is NOT proprietary. Here is a link anyone can open in a browser: https://1drv.ms/x/s!Ago3hUKUPQQ8gaI6...rA47w?e=xmZVlJ.

Mu client wants to be able to use the application from any of his devices. I'll give him a "front end" on the device and I want to use this sheet as a data source.

Ideally I would like to download this data directly, but if I can copy it locally and then use that file as the data source, I'm OK with that. I will also have to write the data back after he updates the information. I can simply let the onedrive update do that.

Too bad onedrive doesn't allow VBA. The problem would be solved.

The third option is to download a copy to the local drive. The only issue with this is that I have to depend on him downloading it to the proper folder. Is there anyway to automate this?

I will take any advice people have on this issue. I'm not committed to any particular solution.

Originally Posted by dflak

What does onedrive have to do with sharepoint.

SharePoint files are often (always?) stored in OneDrive folders so can be accessed through OneDrive if you have the path.

Too bad onedrive doesn't allow VBA.

I don't understand what you mean by that. OneDrive is just a cloud file storage system and is agnostic about what applications or languages you use to access the files on it. OneDrive stores files on a server, and you create a OneDrive folder on your local drive that replicates files between your local drive and the server. You can definitely use VBA on them. The only problem I have had is that if you use VBA to open a file using the locally mapped OneDrive folder, and then you try to query the path of that file with ThisWorkbook.Path, you will get the URL of the server location rather than your locally mapped folder. (Dropbox has a similar operations concept but does not have this problem. I haven't tried it with Google Drive.)

You could refer to a web url if you have access to the file. You have to make sure that you use the correct path.
Open your Excel file in desktop -- > File --> Info --> Copy Path (somewhere on top as button)

If you put that in a workbooks.open .....

I tired using workbooks.open and I get prompted to enter my password. When I do nothing happens.

My url looks like https://1drv.ms/x/s!Ago3hUKUPQQ8o1vt...kCpbe?e=of9XfH

The URLs on all the other "answers" I looked at look like "https://xxx-my.sharepoint.com/personal/Sidxxx/Documents/test.xlsx"

Even though they are answering a question about onedrive, they all have a URL to sharepoint.

So my question is, how do I get the correct URL? When I say copy link I get one like the one I showed.

I modified some code I found and it does download (or creates) a file in the target directory. But any file I download seems to have the wrong file extension.

Please Login or Register  to view this content.