Finding file status (open or not)
You can use these methods (similar approaches) to checking if a file is
open. Look at the filesearch object in Excel VBA help for sample code on
how to get a list of files from a directory:
http://support.microsoft.com/support/kb/articles/Q138/6/21.asp
XL: Macro Code to Check Whether a File Is Already Open
http://support.microsoft.com/support/kb/articles/Q184/9/82.ASP
WD97: VBA Function to Check If File or Document Is Open
Regards,
Tom Ogilvy
MVP Excel
Quote:
>James:
>Thanks for the tip. Unfortunately, this won't work for other types of
files
>(MS Word, AutoCad, etcc....). I was hoping there was a command that would
>check the handle of all open files and compare their name against the file
>names in the subdirectory I am interested in, and let me know if any of
>those files are currently open.
>Your suggestion will come in handy for another project I am working on,
>though..
>Bob Holz
>> Put this in your VBA code if you want to know that file you are working
>on.
>> Dim MyFileName as string
>> MyFileName = activeworkbook.name
>> Application.StatusBar = "Current file is " & MyFileName
>> It should do the trick.
>> >Good day, everyone!
>> >I have a Excel VBA routine that zips up all of the files within a
certain
>> >subdirectory. In order for it to work properly, all of the files need
to
>> be
>> >closed. These files can be of many types (Excel, Word, AutoCad, etc..).
>> >My question is to how to poll the status of all of the individual files
>to
>> >make sure they are not open. I wasn't sure if this was an option in VBA
>> >itself, or if this is the job for an API call.
>> >Any help would be appreciated!
>> >Robert Holz