check if file is in use

[[email protected]]

Good day, Gurus.

I have a script used by several users that copies information to a workbook stored on the network. If another user has the file open when they try to copy their data, it won't copy because the network file is opened as read only.

What kind of error handler can I put in place to chekc if the file is in use?

Thanks in advance.

[VBA Noob]

See if links help

http://www.xcelfiles.com/IsFileOpen.html

http://www.ozgrid.com/VBA/IsWorkbookOpen.htm

http://www.vbaexpress.com/kb/getarticle.php?kb_id=468

VBA Noob

[[email protected]]

How's it going, Noob?

The instructions at xcelfiles.com had a fundtion to check if the file was open on the network, rather than just on the host computer, which is what I needed. I tested the macro, and it worked IF the filename was a constant. When I tried to change the filename to a variable I got an error, "Compile Error: Constant Expression Required". Then if I try to Dim the statement "strFileToOpen" to either a workbook or a string, I get the error "Complie Error: Object required."

Here's the code as I'm trying to use it, I annotated out the oritginal code at the top.

Please Login or Register  to view this content.

Any suggestions on how I can modify this routine for my purposes?

[[email protected]]

Although I would rather check if the workbook is open on the network, and never open is if it is, I haven't been able to figure out how to do that on a file with a variable filename.

As an alternate solution, I have the following code, that opens the workbook, checks the read only status, and closes it immediately if read only is true.

Please Login or Register  to view this content.

The problem is, by default, the workbook opens with notification when the workbook becomes available, so when the workbook is closed the user gets a message "...Workbook is now available for editing." How can I disable this alert, so that the workbook just closes with no further activity required by the user?

By the way, I'm still interested in not taking the time to open the workbook if it is in use, if we can find a way to do it with the variable filename used above.

Thanks in advance!

[Andy Pope]

the variable is a simple string variable. No need to use the set statement

Please Login or Register  to view this content.

[[email protected]]

Thanks, Andy. That's all it took.

Have a good one!