0

I have a VBA script that when the user executes it, it opens and writes new data to another file on a Network share drive. Occasionally the script can't access this file to write new data to it because someone else is simultaneously trying to open it (via read only through a VBA script that they have).

This particular file opening error is not caught in the error handling code and a box comes up and asks the user to End or Debug. Is there a way to bring up an alternate popup box that says something like "Could not process request. Please wait a few seconds and try again."

Community
  • 1
  • 1
Josh
  • 265
  • 6
  • 11
  • 20
  • 2
    [THIS](http://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open-using-vba/9373914#9373914) will get you started. – Siddharth Rout Feb 24 '14 at 13:54
  • Yes there is. Show some code sho we can help. – simpLE MAn Feb 24 '14 at 13:55
  • The problem isn't when it's already opened. It's when it's trying to be opened at the exact same time by two different people. There's a collision going on. – Josh Feb 24 '14 at 13:58
  • Sids suggestion will work, as 2 files cannot be opened at EXACTLY the same time. physically impossible. 2 users sitting beside eachother, 1 user with 2 mice, just cant do it. one user would inevitably open the file first, and the other would then get the error that the file had already been opened. having said that, maybe mine just feels better to you. take your pick – user1759942 Feb 24 '14 at 14:08

1 Answers1

0

yes. put in error handling around the file opening code. like this: 

on error goto FileError
    'file opening code
on error goto 0


exit sub
FileError:
    msgbox("Could not process request. Please wait a few seconds and try again")

basically, the OnError tells everything after that statement to do whatever you specify in the event of an error. in this case I've put goto FileError so if an error is encountered anywhere after the onError processing will stop and go to the line marked FileError.

then the OnError goto 0 disables that error handle. that way if other code generates an error it wont be handled by the FileError handle. the other option for onerror is OnError Resume Next which makes the program skip over code that generates an error. so it wont throw the error, and it will just continue processing.

Note the: Exit Sub before the FileError label, that way the code wont go into the FileError label unless it is sent there by an error being generated.

user1759942
  • 1,322
  • 4
  • 14
  • 33