How to define workbook is open in which process

[levanduyet]

I have open two Excel process (Ctrl + Alt + Delete, and will see 2 process)
I can not use the following code to check if I open one workbook in other process.

Function bWorkbookIsOpen(rsWbkName As String) As Boolean 
On Error Resume Next   
bWorkbookIsOpen = CBool(Len(Workbooks(rsWbkName).Name) > 0) 
End Function 

Le Van Duyet

[ZVI]

Hi Le Van Duyet,
The code below helps to determine if the file is open with exclusive access in other process or by other users:

Function IsFileOpen(FilePathName As String) As Boolean
  Dim FN%
  FN = FreeFile
  On Error Resume Next
  Open FilePathName For Random Access Read Write Lock Read Write As #FN
  Close #FN
  IsFileOpen = Err <> 0
End Function

Sub Test()
  Dim MyFile As String
  MyFile = "C:\Test.xls"
  MsgBox "IsFileOpen = " & IsFileOpen(MyFile), , MyFile
End Sub

See also: Check If a File Is Already Open

Regards,
Vladimir

[levanduyet]

Hi ZVI,

Thank for your fast reply.
In my case that I just want to check one workbook, it means that I pass the workbook name to this function only (not the full path).

Le Van Duyet

[ZVI]

...In my case that I just want to check one workbook, it means that I pass the workbook name to this function only (not the full path)

To check the presence of workbook name in window text of each Excel instance the API solution can help:

' ZVI:2010-04-23 - API solution to find workbook name in text of any Excel window
Private Declare Function EnumChildWindows Lib "user32" (ByVal hWndParent As Long, ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long
Private Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
Private Declare Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" (ByVal hwnd As Long) As Long
Dim hwnd As Long, IsOpen As Boolean, WbName As String

' Testing subrotine
Sub Test()
  Const Wb = "Test.xls"
  MsgBox "IsWorkbookOpen = " & IsWorkbookOpen(Wb) & vbLf & WbName, , Wb
End Sub

' IsFileOpen = True if WorkbookName is found in text of any Excel window
Function IsWorkbookOpen(WorkbookName As String) As Boolean
  hwnd = 0: IsOpen = False: WbName = UCase(WorkbookName)
  EnumChildWindows hwnd, AddressOf EnumChildProc, ByVal 0&
  If IsOpen Then IsWorkbookOpen = True Else WbName = ""
End Function

' Aux function for API callback
Private Function EnumChildProc(ByVal hwnd As Long, ByVal lParam As Long) As Long
    Dim s$
    s = Space$(GetWindowTextLength(hwnd) + 1)
    GetWindowText hwnd, s, Len(s)
    s = Left$(s, Len(s) - 1)
    If UCase(s) Like "MICROSOFT EXCEL *" & WbName & "*" Then
      WbName = s
      IsOpen = True
      Exit Function
    End If
    EnumChildProc = 1
End Function

Replace "MICROSOFT EXCEL *" by "*" to include XLA Add-Ins project names in the search.

Vladimir

[levanduyet]

Thank ZIV,

I have tested. It does not work.

Le Van Duyet

[ZVI]

Thank ZIV,

I have tested. It does not work.

Le Van Duyet

Could you please describe your testing steps?
This can be localization issue, try to replace "MICROSOFT EXCEL *" by "*" and test it again.
Exclude the file extention from the passed workbook name in case of workbook is created but not saved.

[levanduyet]

Hi ZVI,

If you test the workbook in the same session, it's not correct.

Le Van Duyet

[levanduyet]

Dear All,
Anyone have another solution?
If the user that they change the window caption text, or there are some workbooks in one process?

Tks,

LVD

[ZVI]

Hi ZVI,

If you test the workbook in the same session, it's not correct.

Le Van Duyet

Dear Le Van Duyet,

The proposed in post #4 code works with windows in the same Excel session as well as in other sessions.
It’s still not clear for me why you say it doesn’t.
Just add new workbook to Excel session and try to check it out.
Could you post your testing code you have stuck in?

Nothing is said in your previous posts about changing of the window caption.
And yes, it is possible by API code to find Excel windows even if its caption is modified.
But without clearing of your initial (previous) task it has not sense for me.

Vladimir

[omnibuster]

levanduet.
Try this.
[vba]Private Sub Workbook_Open()
Dim Wb As Workbook
For Each Wb In Application.Workbooks
If Not Wb.Name = ThisWorkbook.Name Then
MsgBox Wb.Name
Wb.Close
End If
Next Wb[/vba]

ZVI.
You code #4 dont work for me to??
Just says:IsWorkbookOpen =false

[levanduyet]

Hi Omnibuster,
This code, I know. But it's can not check in difference process.
(Ctrl + Alt + Delete)

Le Van Duyet

[GTO]

@ZVI:

Greetings Vladimir,

I am afraid this message may seem 'choppy', but technical difficulties (site went off once, my POL locked, server at work went down...ERGH!!!) have meant that this is either my third or fourth try... (and I ain't a fast typist)

Onward and Upward:

I tested in xl2000 and 2003, both WIN/XP.

Initial criteria:

I added, [vba]If UCase(s) Like "MICROSOFT*" Then Debug.Print s[/vba]
right below [vba]s = Left$(s, Len(s) - 1)[/vba]

I also rem'd (commented out) the Exit Function.

After getting less than stellar results, I substituted [vba]Debug.Print s[/vba]

Tests were with:

One instance of Excel
Two workbooks open, the wb with the code (hereafter: codewb) and the wb to 'find.'

First tests with workbook windows maximized:

With codewb active, msgbox reports False.

I noted the following returns for 's' that seemed of interest:

Microsoft Excel - vbax31648#4_Vladimir_IsWorkbookOpen.xls (<---codewb)

Test Referencing Another Sheet.xls

Microsoft Visual Basic - Test Referencing Another Sheet.xls [running] - [Module1 (Code)] (<---even though VBIDE was closed)

vbax31648#4_Vladimir_IsWorkbookOpen.xls

With 'lookfor' wb being active, SUCCESS, msgbox returned True, returns for 's'...
Microsoft Excel - Test Referencing Another Sheet.xls

vbax31648#4_Vladimir_IsWorkbookOpen.xls

Microsoft Visual Basic - Test Referencing Another Sheet.xls [running] - [Module1 (Code)]

Test Referencing Another Sheet.xls


With the wb windows sized normal:

With codewb active, msgbox reports False.

Returns for 's' of interest:

Microsoft Excel

Test Referencing Another Sheet.xls

Microsoft Visual Basic - Test Referencing Another Sheet.xls [running] - [Module1 (Code)]

vbax31648#4_Vladimir_IsWorkbookOpen.xls


With 'lookfor' wb being active, FAILURE, msgbox reports False, 's' returns:

Microsoft Excel

vbax31648#4_Vladimir_IsWorkbookOpen.xls

Microsoft Visual Basic - vbax31648#4_Vladimir_IsWorkbookOpen.xls [running] - [Module1 (Code)]

Test Referencing Another Sheet.xls



Hopefully the testing is of some help. I admit that the only thing I know about API is what little I have been able to glean from posts/examples such as yours here.

That said, it would seem to me that the wb.Name is being found in a caption, but that maybe we should change the Like test a bit. Would...
[vba] If UCase(s) Like "*" & WbName Then[/vba]
be worthy of consideration? I only briefly tested (I am afraid I should have been to bed several hours ago, but this is interesting), but it seems to work in the same instance, in another instance, both regardless of window status.

Mark

[omnibuster]

Thanks GTO.
I was testing different ways: msgbos(s), UCase(s) e.g say WbkName right, but function says =false(notopened).
[vba]
If Application.Caption = "Microsoft Excel – Wbkname” Then
[/vba]
AppCaption Works only if Wbk is visible and activated.
But need control when Application.visible=0