Error following Microsoft Article

[PJ Usher]

I followed Microsoft Knowledge Base Article 213299 XL2000: For Each Loop to
Determine If an Excel Workbook is Open

When I run the code the line

Dim wb as Workbook

is highlighted and I receive an error

Compile error: User-defined type not defined

?????

The code is written exactly as written in the article. Can I fix this?

Thanks for any help

PJ

 

[Ron de Bruin]

Hi PJ

I don't look at the article.(you don't need a loop)
Use this function in a normal module

Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function

Sub File_Open_test()
If bIsBookOpen("Test.xls") Then
MsgBox "the File is open!"
Else
MsgBox "the File is not open!"
End If
End Sub

 

[Tom Ogilvy]

In the VBE in tools => references, do you have a reference to the Excel
library?

You are doing this in Excel itself; correct?

 

[PJ Usher]

You're right I don't need a loop. Thank you for this code. I tried it but
I still get an error highlighting workbooks on this line

bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)

The error is Compile error: Methond or data member not found.

PJ

 

[PJ Usher]

I'm actually doing this from MS Word but have the Microsoft Excel 9.0 Object
Library referenced.
Am I in the wrong newsgroup?

PJ

 

[Tom Ogilvy]

Not necessarily, but if you do have a reference to that library - that is
where workbook is defined and you shouldn't get an error. In excel, the
reference in specific to the activeworkbook in the VBE IDE. I think it is
different in Word, but can't say as I don't do much in the word VBE IDE. If
the reference is set up properly, you shouldn't get the error. If you do
get the error, then I would say it isn't set up properly.

 

[PJ Usher]

Hi Ron

To fix the error I changed the line to excel.application.workbooks. Thank
you very much for helping me with this code.

 

[PJ Usher]

A change in the code made it work. I had to reference excel in the code.
Thanks for your help.

PJ

 

[Tom Ogilvy]

I went into a new word document and created a reference to Excel.

then put in code

Sub Tester1()
dim bk as Workbook
End Sub

Workbook was offered as an option in intellisense and when I compiled, it
compiled fine. It is good you have it working, but this could be indicative
of other problems that may crop up. I would look in Tools=>References and
see if you have any reference marked as MISSING. If so, you need to fix
them or get rid of them.

 

[Myrna Larson]

I think that it should be

Dim WB AS Excel.Workbook

But you need to consult Help on how to refer to the Excel application and its
objects from another program like Word.

 

[Tom Ogilvy]

If there is no duplication, then it shouldn't need to be qualified.

 

[Myrna Larson]

OK, but why is he getting the error then? Any thoughts?

 

[Tom Ogilvy]

I think he has a reference problem as I already posted.

When you have a reference problem and it craps out on Left or MID as an
example, qualifying it with

VBA.Left or VBA.Mid often appears to clear up the problem. I suspect a
similar situation here. Of course this is compounded by automation and
working in word, so perhaps it is not the source of the problem.

In any event, qualifying it with a reference to Excel is certainly
appropriate as a general practice. (this is in declarations - in the code
itself, you definitely need to qualify all references to objects starting
from the Excel application level or you could have problems closing the
instance of excel.)