View Single Post
 
Old 01-25-2025, 03:02 PM
MartinGM MartinGM is offline Windows 11 Office 2021
Competent Performer
 
Join Date: May 2023
Location: England
Posts: 108
MartinGM is on a distinguished road
Default Problems with Excel VBA and OneDrive

I wonder if anyone else has spotted these issues, and whether thay have found better solutions than my clumsy efforts ?

I have a reliable and reasonably fast internet connection: download 60mbps, upload 15mbps, ping times around 20ms.

1. Opening a downloaded file

I have a procedure which downloads a file from the internet, loops to see if it is there - then opens it when it is detected.

I do this so that I can "time-out" the procedure if it loops more than a defined number of times. This is necessary as the file isn't always available and I didn't want to get stuck in a VBA loop.

This worked fine until I moved to Excel 365 and put my files into the OneDrive folders. At this point VBA sometimes crashed running my download procedure.

I eventually diagnosed this to VBA being too quick (or OneDrive being too slow) - it detects the file before it is fully downloaded and then cannot open it.

I solved this by putting my download folder outside the OneDrive area and normal service was resumed.

2. Saving and closing a file

Recently I have been having problems with a simple "save and close" procedure in a different workbook. It had been working OK but now, although it successfully saves the workbook locally, OneDrive often comes up with an error "This file is in use by another user" which it certainly isn't.

I have solved this, I think, by putting a delay betwen the save and the close sections of the code. So far so good.

Question

My solutions seems rather clumsy and awkward and I was wondering if there's a more elegant way to manage the opening and closing of files stored in the OneDrive area ?

I don't like putting fixed delays into procedures because I never know just how slow the offending transaction might be - hence the loop to detect a downloaded file.

Thanks
Reply With Quote