Error following Microsoft Article
 

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

Error following Microsoft Article
 

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

Error following Microsoft Article
 

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

Error following Microsoft Article
 

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

Error following Microsoft Article
 

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

Error following Microsoft Article
 

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

Error following Microsoft Article
 

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

Error following Microsoft Article
 

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

Error following Microsoft Article
 

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

Error following Microsoft Article
 

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

Error following Microsoft Article
 

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

Error following Microsoft Article
 

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.

Error following Microsoft Article
 

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.)

--
Regards,
Tom Ogilvy

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