• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Check for any open workbooks

G

granville

New Member
I have a spreadsheet that is updated by over 30 workbooks at the press of a button.


The problem is that if any of those workbooks are open the code crashes.


What I'd like is for a piece of code that will loop through the folder containing the workbooks and display a message if it finds an open file warning staff not to update. I don't necessarily need to know which workbooks are open, I just need to know if any workbook is open.


I can find code to check if a specific file is open, but can find nothing to check for any open file.


Does anyone have any ideas?
 
Hi Granville ,


Going by the logic which you have mentioned , the issue is not whether any workbook is open ; you need to check whether any of the 30 relevant workbooks is open. The simplest way would be to loop through a pre-defined list of workbooks and check whether any of them is open , and if so , exit without updating. Only if every workbook is verified to be not open , proceed with the update.


Narayan
 
Thanks for your reply Narayan.


The problem is the workbooks within th folder are constantly changing between each update. There will be a maximum of 30 but there could be as few as 7 each with a unique filename. There is no set list of files.


What I need is to be able to loop through the folder and have the code stop if it finds an open file.


It's driving me nuts.
 
Hi Granville ,


OK ; but given the conditions you have mentioned , why does the code crash if a file is open ? Is this supposed to happen ?


Narayan
 
Hi, granville!

Whenever Excel (Office programs in general) open a file, in the same folder they create a temporary file that begins with "~$" (unquoted) and followed by the filename. You can't open it, it isn't created with shared access, but you can certainly test if it exists or not.

Tried checking that?

Regards!
 
SirJB7, thanks for the help. Unfortunately I'm not good with vba. I can code enough to put a formula in a cell, but thats about it.

.

I haven't really explained myself well (I'm typing on a phone and trying to be too quick.


Basically, I've inherited a system that checks a specific folder and copies certain info from them onto a master. The number of files in the folder can vary from update to update. I said before that the system crashes which wasn't entirely accurate. What happens is that if an update is attempted and a file is open the system will either continue with the update and ignore the open file, meaning data is lost, or it stops and won't update (without issuing a warning that it has done so).


I've already had a critical update happen that didn't catch all the data and I was just trying to avoid that happening again. The guy that created the system used to update it late at night, so no files were open. I can't do that, and that guy died last year so I can't even ask him for help. my skills with vba are severely limited.
 
Hi, granville!

Like to upload your file so as to see in detail its structure?

Here're the guidelines.

http://chandoo.org/forums/topic/posting-a-sample-workbook

You say you're typing on your phone, I hope that just for this messages and not for related file. In which environment are you using it? Operative system, Excel version, ...

Regards!
 
You must log in or register to reply here.
Back
Top