Is a file open 1

[bpeirson]

I am trying to determine if an Excel workbook is open through VBA code.

What I want is when workbook "A" is opened it checks to see if workbook "B" is open. These 2 workbooks contain similar information so as changes are made to "A" it automatically updates "B".

The problem I currently have is that if "B" is open before "A" and someone updates "A" the changes are not made to "B" and there is no warning. I have shared the workbook in the past but we have found through painful experience that shared workbooks are prone to corruption in certain conditions.

I have found a function WbOpen in these forums but it does not seem to work for us unless the file is open on the same work station. We are saving files to a server and different people access them from different work stations.

All help is appreciated.

 

[PHV]

Have you tried to test the Err object after trying to open "B" ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

Hey there,

How have you been? Hope all is well.

I recommend using a function for this, which returns a boolean true or false result. I do not recommend setting an object, as it can be determined by trying to read a property of a file. This is what I use...

Function IsWbOpen(wbName as string) as boolean
on error resume next
iswbopen = len(workbooks(wbname).name)
end function

Then when you want to test a workbook, you can use it like this...

Dim wb as workbook, strWbName as string, strWbPath as string
strwbname = "Book1.xls"
strwbpath = "C:\"
if iswbopen(strwbname) = true then
set wb = workbooks(strwbname)
else
set wb = workbooks.open(strwbpath & strwbname)
end if

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[PHV]

Zack, how your function can return a True value if the workbook is open by another user on another workstation ?

 

[firefytr]

Hmm, thought I put this note in the post, half of it is missing..

For networked computers, check out Ivan Moala's website for two methods to check for files being open...

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

HTH (and sorry about the mispost)

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[PHV]

Thanks Zack for this helpful link.

 

[bpeirson]

Thanks, Zack. I had already used your function but during testing I found that it did not check other computers. The link you sent led to a solution.

I used the VBA code found there but I had to change a "Private Function" to a "Function" because the code used to check the other file was placed in the "ThisWorkbook" object to do the check when workbook "A" opens.

Thanks to all.

 

[bpeirson]

Final word.

I had to use your code, Zack as well as the code from your link. Your code allows me to check if the file is open on the local workstation, if so there is no need to reopen it or to check if it is open on the network.

Thanks again.