Count Workbooks that are open; Prevent another from opening
 

Im running the code from he
http://support.microsoft.com/kb/291295/EN-US/

That works great if I get into the Sub and hit F5! Now, Im trying to think
of a way I can modify this slightly so as to prompt a user that the WB is
already open if they try to open the file again. The file will be saved back
to SharePoint and I want to make sure there is ONE SINGLE incidence of this
Excel WB open at a time (its fine if several other WBs are open).
Basically, I dont want someone to open the file, do some work, walk away,
forget that its open, and try to open it again (from SharePoint).

I think Im going to need something like:
workbook_open (in thisworkbook)

or

workbook_activate (in thisworkbook)

or

AUTO_OPEN (in a module)

I think, for this to work, Im going to have to count the number of WBs open
(with this WB name, right). So,

If count <=1 Then workbook_open
Else call TestFileOpened
End if

Thanks!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Count Workbooks that are open; Prevent another from opening
 

I modified the code a bit and am now using the version below:
Sub MyMacro()
Dim i As Long
Dim wbk As Workbook

For Each wbk In Workbooks

i = Application.Workbooks.Count
If i.wbk.Name 1 Then
If IsFileOpen("I:\Ryan\Copy of Book2.xls") Then
MsgBox "File already in use!"
Else
End If
End If
Next wbk

End Sub

' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns False.
Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next
filenum = FreeFile()
Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0

Select Case errnum
Case 0
IsFileOpen = False
Case 70
IsFileOpen = True
End Select
End Function

I get an error on this line:
If i.wbk.Name 1 Then

I guess i.wbk.Name is an invalid qualifier. What Im trying to do is count
the number of Workbooks open and if that number 1 then prompt the user that
the Workbook is already open €“ another Workbook will NOT open.

I think this code is pretty close, but I cant seem to count the number of
Workbooks of a specific name. If a user has other Workbooks open (other
names), I dont mind. I just dont want multiple versions of THIS Workbook
open.

Thanks!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:

Im running the code from he
http://support.microsoft.com/kb/291295/EN-US/

That works great if I get into the Sub and hit F5! Now, Im trying to think
of a way I can modify this slightly so as to prompt a user that the WB is
already open if they try to open the file again. The file will be saved back
to SharePoint and I want to make sure there is ONE SINGLE incidence of this
Excel WB open at a time (its fine if several other WBs are open).
Basically, I dont want someone to open the file, do some work, walk away,
forget that its open, and try to open it again (from SharePoint).

I think Im going to need something like:
workbook_open (in thisworkbook)

or

workbook_activate (in thisworkbook)

or

AUTO_OPEN (in a module)

I think, for this to work, Im going to have to count the number of WBs open
(with this WB name, right). So,

If count <=1 Then workbook_open
Else call TestFileOpened
End if

Thanks!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Count Workbooks that are open; Prevent another from opening
 

Dim i As Long
........
i = Application.Workbooks.Count
........
I get an error on this line:
If i.wbk.Name 1 Then

The variable 'i' is Dim'med as a Long... it can't have any "dotted"
parameters following it. Normally, I would say you meant to type this...

If wbk.Name 1 Then

however, that won't work unless the workbooks is named as numbers (all
digits), which seems unlikely. Did you, perhaps, mean to use the Count
property you had just calculated in the line above?

If i 1 Then

--
Rick (MVP - Excel)



"ryguy7272" wrote in message
...
I modified the code a bit and am now using the version below:
Sub MyMacro()
Dim i As Long
Dim wbk As Workbook

For Each wbk In Workbooks

i = Application.Workbooks.Count
If i.wbk.Name 1 Then
If IsFileOpen("I:\Ryan\Copy of Book2.xls") Then
MsgBox "File already in use!"
Else
End If
End If
Next wbk

End Sub

' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
False.
Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next
filenum = FreeFile()
Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0

Select Case errnum
Case 0
IsFileOpen = False
Case 70
IsFileOpen = True
End Select
End Function

I get an error on this line:
If i.wbk.Name 1 Then

I guess i.wbk.Name is an invalid qualifier. What Im trying to do is
count
the number of Workbooks open and if that number 1 then prompt the user
that
the Workbook is already open €“ another Workbook will NOT open.

I think this code is pretty close, but I cant seem to count the number of
Workbooks of a specific name. If a user has other Workbooks open (other
names), I dont mind. I just dont want multiple versions of THIS
Workbook
open.

Thanks!
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:

Im running the code from he
http://support.microsoft.com/kb/291295/EN-US/

That works great if I get into the Sub and hit F5! Now, Im trying to
think
of a way I can modify this slightly so as to prompt a user that the WB is
already open if they try to open the file again. The file will be saved
back
to SharePoint and I want to make sure there is ONE SINGLE incidence of
this
Excel WB open at a time (its fine if several other WBs are open).
Basically, I dont want someone to open the file, do some work, walk
away,
forget that its open, and try to open it again (from SharePoint).

I think Im going to need something like:
workbook_open (in thisworkbook)

or

workbook_activate (in thisworkbook)

or

AUTO_OPEN (in a module)

I think, for this to work, Im going to have to count the number of WBs
open
(with this WB name, right). So,

If count <=1 Then workbook_open
Else call TestFileOpened
End if

Thanks!

--
Ryan---
If this information was helpful, please indicate this by clicking
''Yes''.