adodb connection quirks

One part of my macro script will access another excel file on a shared server via ADODB jet engine. Everything seems to be working smoothly, except one small quirk. I can't seem to find any documentation of this so I am posting here to see if someone has come across this and found a solution.

When the target file, the excel file on shared server, is not open for editing, my script will access it and transfer the necessary data over to my macro script. However, if it is ever opened by someone at the time of me opening an ADODB connection to it, my macro excel session will open the target file in as "readonly" file. In other words, my macro script will lose its focus and a new file is activated.

This behaviour is largely unexpected and it causes my macro to fail.

Please Login or Register  to view this content.

Does someone know if there is any option or tricks that I can set in order that my macro script session will NEVER try to open the target file in its session if someone has that excel target file opened somewhere???

Originally Posted by nathansav

You can add some code to check to see if the workbook is open.

Does this help

http://stackoverflow.com/questions/9...open-using-vba

?

Well, it helps in a way but it defeats the purpose of having the target file opened as READONLY file sharable by multiple access by my script. I would have set to mode to Exclusive access to achieve the same effect.

My original design is to let various instances of the macro to access the target file simultaneously and draw its data across the network. If I set the file access mode to exclusive it is exactly same as checking if fileopen suggested by you.

The reason is that as long as there is an ADO connection, the check will remain true so other instances cannot access the target file.

Somehow the default behaviour of Excel is to open the workbook in a local session corresponding to the target file if it is opened by someone somewhere. I am asking for an ADOconnection which is supposed to be silent if the file remained closed.

I suppose if the target file is something other than an Excel database then it will not do the same thing. If only I could find a way to change its default behaviour!