A tip about using Excel VBA with OneDrive

[MartinGM]

Windows 11, Excel 365 and OneDrive



I have some workbooks that use files temporarily stored in a OneDrive folder on my PC

By "use" I mean files are downloaded to the OneDrive folder then opened, data extracted, the file is deleted etc etc etc

Lately my code, which has worked fine for a couple of years, has been playing up - it mostly just fails to execute the commanded file operations without any error messages.

I have finally tracked this down to OneDrive which seems to introduce delays, for instance when a file has been successfully downloaded and the code wants to open it but OneDrive is uploading a copy to the cloud

That's when the code fails. I have VBA checks to see if the file is "there" but haven't found a way to check if the file is actually usable

I've tried several other fixes but none of them work reliably, so I now use a folder outside OneDrive for these operations and all is well again

I have a very fast internet connection but have noticed that OneDrive synch is taking longer than it used to - maybe Microsoft is overloaded. Whatever the reason, I'm so pleased to have functioning VBA again.

Hope this helps someone

[ArviLaanemets]

In my experience, VBA can't change the contents of files on OneDive - probably the security issue.

The workaround is to store all such files on shared local network drive instead.

[MartinGM]

Originally Posted by ArviLaanemets

In my experience, VBA can't change the contents of files on OneDive - probably the security issue.

The workaround is to store all such files on shared local network drive instead.

I *can* do file operation in OneDrive folders with Excel VBA - but I have to build in significant delays.

The absence of error messages is a pain.