PDA

View Full Version : How to define workbook is open in which process



levanduyet
04-21-2010, 06:49 PM
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
04-21-2010, 07:51 PM
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 (http://www.vbaexpress.com/kb/getarticle.php?kb_id=468)

Regards,
Vladimir

levanduyet
04-22-2010, 07:04 AM
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
04-22-2010, 03:39 PM
...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
04-22-2010, 04:31 PM
Thank ZIV,

I have tested. It does not work.

Le Van Duyet

ZVI
04-22-2010, 04:57 PM
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
04-23-2010, 11:15 AM
Hi ZVI,

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

Le Van Duyet

levanduyet
04-26-2010, 09:43 AM
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
04-26-2010, 11:39 AM
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
04-26-2010, 11:29 PM
levanduet.
Try this.
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

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

levanduyet
04-27-2010, 12:46 AM
Hi Omnibuster,
This code, I know. But it's can not check in difference process.
(Ctrl + Alt + Delete)

Le Van Duyet

GTO
04-27-2010, 12:28 PM
@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, If UCase(s) Like "MICROSOFT*" Then Debug.Print s
right below s = Left$(s, Len(s) - 1)

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

After getting less than stellar results, I substituted Debug.Print s

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...
If UCase(s) Like "*" & WbName Then
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
04-27-2010, 01:35 PM
Thanks GTO.
I was testing different ways: msgbos(s), UCase(s) e.g say WbkName right, but function says =false(notopened).

If Application.Caption = "Microsoft Excel – Wbkname” Then

AppCaption Works only if Wbk is visible and activated.
But need control when Application.visible=0