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

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"PJ Usher" wrote in message ...
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

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



"Ron de Bruin" wrote in message
...
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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"PJ Usher" wrote in message
...
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

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" wrote in message
...
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



"Ron de Bruin" wrote in message
...
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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"PJ Usher" wrote in message
...
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

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

You are doing this in Excel itself; correct?
--
Regards,
Tom Ogilvy

"PJ Usher" wrote in message
...
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

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" wrote in message
...
In the VBE in tools = references, do you have a reference to the Excel
library?

You are doing this in Excel itself; correct?
--
Regards,
Tom Ogilvy

"PJ Usher" wrote in message
...
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

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.


--
Regards,
Tom Ogilvy

"PJ Usher" wrote in message
...
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" wrote in message
...
In the VBE in tools = references, do you have a reference to the Excel
library?

You are doing this in Excel itself; correct?
--
Regards,
Tom Ogilvy

"PJ Usher" wrote in message
...
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

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

PJ



"Tom Ogilvy" wrote in message
...
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.


--
Regards,
Tom Ogilvy

"PJ Usher" wrote in message
...
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" wrote in message
...
In the VBE in tools = references, do you have a reference to the
Excel
library?

You are doing this in Excel itself; correct?
--
Regards,
Tom Ogilvy

"PJ Usher" wrote in message
...
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

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.

--
Regards,
Tom Ogilvy

"PJ Usher" wrote in message
...
A change in the code made it work. I had to reference excel in the code.
Thanks for your help.

PJ



"Tom Ogilvy" wrote in message
...
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.


--
Regards,
Tom Ogilvy

"PJ Usher" wrote in message
...
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" wrote in message
...
In the VBE in tools = references, do you have a reference to the
Excel
library?

You are doing this in Excel itself; correct?
--
Regards,
Tom Ogilvy

"PJ Usher" wrote in message
...
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

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.

On Mon, 4 Oct 2004 12:00:55 -0400, "PJ Usher" wrote:

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" wrote in message
...
In the VBE in tools = references, do you have a reference to the Excel
library?

You are doing this in Excel itself; correct?
--
Regards,
Tom Ogilvy

"PJ Usher" wrote in message
...
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

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

--
Regards,
Tom Ogilvy

"Myrna Larson" wrote in message
...
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.

On Mon, 4 Oct 2004 12:00:55 -0400, "PJ Usher" wrote:

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" wrote in message
...
In the VBE in tools = references, do you have a reference to the Excel
library?

You are doing this in Excel itself; correct?
--
Regards,
Tom Ogilvy

"PJ Usher" wrote in message
...
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

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

On Mon, 4 Oct 2004 14:09:42 -0400, "Tom Ogilvy" wrote:

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