PDA

View Full Version : Solved: if thisworkbook.visible=true



gsouza
01-16-2006, 11:03 AM
Hi everybody, I have code on Private Sub UserForm_ACTIVATE()
:hi:
What I want to do is if I have a specific workbook open I want a condition to prevent this workbook from being open again when I activate the form again.
the code below does not work. Can anybody help.

If Application.ThisWorkbook("LR119 (GRL).xls").Visible = True Then
Exit Sub
Else
Set xlApp = CreateObject("Excel.Application")
Set wbExcel = xlApp.Workbooks.Open("C:\Documents and Settings\es69206\Desktop\LR119 (GRL).xls")
xlApp.Visible = True
Set wbExcel = Nothing
Set xlApp = Nothing
Application.WindowState = xlMinimized

end if
exit sub




or

If Application.Workbooks("C:\Documents and Settings\es69206\Desktop\LR119 (GRL).xls").Visible = True Then
Exit Sub

Killian
01-16-2006, 01:13 PM
I would say you either need to test for the ActiveWorkbook.Name, if you can be sure it will be the active one (ThisWorkBook doesn't have a paramater - it is what it is, if you get me) or go through the WorkBook collection and test for the nameDim wb As Workbook
Dim wbFound As Boolean
For Each ws In Application.Workbooks
If wb.Name = "whatever" Then
wbFound = True
Exit For
End If
Next ws
If Not wbFound Then 'open workbook

gsouza
01-16-2006, 02:15 PM
If it is not the activeworkbook but another workbook that is open. can't i have the first workbook to look to see if it is open and if it is open then exit sub. I could not get your code to work after editing it.

Ken Puls
01-16-2006, 04:23 PM
Hi gsouza,

Killian's code should work to test if the specified workbook is open, but you'll need to change the "whatever" to "workbookname.xls". I don't think it will work without teh .xls part. The workbook you're looking for IS in the current excel instance, correct? It isn't running in a second instance of Excel?

(I'm only asking this because we worked on on thread recently about opening another instance.)

Killian's code modded to do the Exit Sub part too:


Dim wb As Workbook
Dim wbFound As Boolean
For Each ws In Application.Workbooks
If wb.Name = "whatever.xls" Then
wbFound = True
Exit For
End If
Next ws
If Not wbFound Then Exit Sub

gsouza
01-17-2006, 05:06 AM
It is in another instance.

Ken Puls
01-17-2006, 11:17 AM
If it is not the activeworkbook but another workbook that is open. can't i have the first workbook to look to see if it is open and if it is open then exit sub. I could not get your code to work after editing it.

It is in another instance.

From XL-Dennis's forum, thread found here: http://www.excelkb.com/instantforum41/Topic200-8-1.aspx

...you can enumerate existing Excel.Application windows via FindWindowEx(), but how can one convert this to an Excel.Application reference that one can control via Automation? The short answer, I think, is you can't. You, can, of course use GetObject(), but this just gets you the 1st Instance. This is a lot better than nothing, but if more than one Excel instance is running, you can only enumerate them by FindWindow() API's, but then there is no conventional way to convert these Hwnd's to Memory Addresses.

I think that the short answer is that there might be a hack, but it's likely to be so complicated and unconventional that no one should *really* be attemting such things...


Now, what you could do is check if the workbook was opened by just trying to open it. If it opens in read only state, then it is in use, so close it. If it opens fine, then it isnt' open in another instance, so close it and use CreateObject to open it in a new instance.

gsouza
01-17-2006, 11:32 AM
I know the workbook is open in another instance, it is minimized but when the operator has to activate the form again on the original instance the code says to open that file. I dont want the file to open again. If everytime the activate the form the file will keep opening and opening in other instances. I hope this makes sence and I am not driving you crazy.

gsouza
01-17-2006, 11:53 AM
I changed the location of the code and put it in workbook open. This way it only opens once when the file is open and not everytime the form is activated. Thanks for the help as always. I should have thought about that before but I think sometimes I am just stuborn with trying to solve the problems I have. : )

Ken Puls
01-17-2006, 11:58 AM
Okay, gotcha... this is kind of hokey, as it forces the file to open regardless, but I think it will work for you. I haven't actually tested this, but let me know if it errrors.

Sub Tester()
Dim sFname As String
Dim wbTest As Workbook

'Set your filename here
sFname = "C:\test.xls"

'Avoid prompting
Application.EnableEvents = False

'Open workbook and test if it is read only
On Error GoTo ExitOut
Set wbTest = Workbooks.Open(sFname)
If wbTest.ReadOnly = True Then
'If it's read only, close it and stop processing
wbTest.Close
GoTo ExitOut
Else
'If it's not read only,close it and resume regular processing
wbTest.Close
End If

'The rest of your code here

'Turn events back on
ExitOut:
Application.EnableEvents = True
On Error GoTo 0

End Sub

EDIT: Didn't see your post on the Worbook open event. It might be a better way. :)

gsouza
01-17-2006, 12:00 PM
okay let me try it and I will get back to you.

gsouza
01-20-2006, 08:44 AM
Thanks for your help I fooled around with it and got it to work but i am going to keep the original code in the Worbook open event. Sorry it took so long to get back to you but you know at work can't always be here.

Ken Puls
01-20-2006, 04:46 PM
No problem! Glad you got it sorted. :)