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

I had similar issues, (e.g for Excel converters which did read info e.g. from clients forecasts, converted the data to format acceptable by our ERP program, and then saved the result file into folder in our LAN), and for me the solution was to store all such Excel workbooks to shared resource in our LAN. Users opened their workbooks from this shared resource without any further problems.

Yes, that sounds like the same issue.

In the case of opening a dowloaded file, it looks as though the file is responding with a file length (which is how I check if it is there) before it is ready to be opened.

I wonder if there is a different check I could use which will give a positive response once the file can be opened ?