Hi,

I have an Excel workbook that reads data from an Access database and copies
it into spreadsheet tables. That works fine on my PC, but there is a
problem for anyone who has a different version of MDAC installed on their
PC. In this case, it fails immediately (i.e. before any of my code is
executed) with the awful error message "Compile error in hidden module".

A check to find the item (ADODB in this case) in
Application.VBE.ActiveVBProject.References works correctly for all OK
references, but is irrelevant if the reference is not OK because the
exception occurs before the code is called, and "Compile error in hidden
module" is reported anyway.

The check is in my Sub CheckRefs, which is called directly from
Workbook_Open; like Workbook_Open, the sub is in ThisWorkbook.

A similar check in Word - to ensure that the global template AddIn has been
installed - works correctly provided that direct references to routines in
the global template are avoided within routines in ThisDocument, i.e.
Document_New, Document_Open, etc. The exception can therefore be controlled
in Word.

Is Workbook_Open the earliest event on which I can hang the code? The
documentation on MSDN says this occurs before Auto_Open.

Is there any technique by which references in Excel may be checked before
the exception for the missing reference is raised?

Regards,
JPL

Use late binding.

Dim oConn as Object
or
Dim oRS as Object

with

Set oConn = CreateObject("ADODB.Connection")
or
Set oRS = CreateObject("ADODB.Recordset")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JPL" <j.p.lxdsl.pipex.com wrote in message
...
Hi,

I have an Excel workbook that reads data from an Access database and
copies
it into spreadsheet tables. That works fine on my PC, but there is a
problem for anyone who has a different version of MDAC installed on their
PC. In this case, it fails immediately (i.e. before any of my code is
executed) with the awful error message "Compile error in hidden module".

A check to find the item (ADODB in this case) in
Application.VBE.ActiveVBProject.References works correctly for all OK
references, but is irrelevant if the reference is not OK because the
exception occurs before the code is called, and "Compile error in hidden
module" is reported anyway.

The check is in my Sub CheckRefs, which is called directly from
Workbook_Open; like Workbook_Open, the sub is in ThisWorkbook.

A similar check in Word - to ensure that the global template AddIn has
been
installed - works correctly provided that direct references to routines in
the global template are avoided within routines in ThisDocument, i.e.
Document_New, Document_Open, etc. The exception can therefore be
controlled
in Word.

Is Workbook_Open the earliest event on which I can hang the code? The
documentation on MSDN says this occurs before Auto_Open.

Is there any technique by which references in Excel may be checked before
the exception for the missing reference is raised?

Regards,
JPL

Bob,

Thanks very much. That has done the trick.

I was aware of the concept of late binding but had not realised the full
implications and so had not used it before, and certainly had not twigged
its relevance here.

Regards,
JPL

"Bob Phillips" wrote in message
...
Use late binding.

Dim oConn as Object
or
Dim oRS as Object

with

Set oConn = CreateObject("ADODB.Connection")
or
Set oRS = CreateObject("ADODB.Recordset")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JPL" <j.p.lxdsl.pipex.com wrote in message
...
Hi,

I have an Excel workbook that reads data from an Access database and
copies
it into spreadsheet tables. That works fine on my PC, but there is a
problem for anyone who has a different version of MDAC installed on their
PC. In this case, it fails immediately (i.e. before any of my code is
executed) with the awful error message "Compile error in hidden module".

A check to find the item (ADODB in this case) in
Application.VBE.ActiveVBProject.References works correctly for all OK
references, but is irrelevant if the reference is not OK because the
exception occurs before the code is called, and "Compile error in hidden
module" is reported anyway.

The check is in my Sub CheckRefs, which is called directly from
Workbook_Open; like Workbook_Open, the sub is in ThisWorkbook.

A similar check in Word - to ensure that the global template AddIn has
been
installed - works correctly provided that direct references to routines
in
the global template are avoided within routines in ThisDocument, i.e.
Document_New, Document_Open, etc. The exception can therefore be
controlled
in Word.

Is Workbook_Open the earliest event on which I can hang the code? The
documentation on MSDN says this occurs before Auto_Open.

Is there any technique by which references in Excel may be checked before
the exception for the missing reference is raised?

Regards,
JPL

Yeah, it is very useful in these multi-version environments.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JPL" <j.p.lxdsl.pipex.com wrote in message
...
Bob,

Thanks very much. That has done the trick.

I was aware of the concept of late binding but had not realised the full
implications and so had not used it before, and certainly had not twigged
its relevance here.

Regards,
JPL

"Bob Phillips" wrote in message
...
Use late binding.

Dim oConn as Object
or
Dim oRS as Object

with

Set oConn = CreateObject("ADODB.Connection")
or
Set oRS = CreateObject("ADODB.Recordset")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JPL" <j.p.lxdsl.pipex.com wrote in message
...
Hi,

I have an Excel workbook that reads data from an Access database and
copies
it into spreadsheet tables. That works fine on my PC, but there is a
problem for anyone who has a different version of MDAC installed on
their
PC. In this case, it fails immediately (i.e. before any of my code is
executed) with the awful error message "Compile error in hidden
module".

A check to find the item (ADODB in this case) in
Application.VBE.ActiveVBProject.References works correctly for all OK
references, but is irrelevant if the reference is not OK because the
exception occurs before the code is called, and "Compile error in
hidden
module" is reported anyway.

The check is in my Sub CheckRefs, which is called directly from
Workbook_Open; like Workbook_Open, the sub is in ThisWorkbook.

A similar check in Word - to ensure that the global template AddIn has
been
installed - works correctly provided that direct references to routines
in
the global template are avoided within routines in ThisDocument, i.e.
Document_New, Document_Open, etc. The exception can therefore be
controlled
in Word.

Is Workbook_Open the earliest event on which I can hang the code? The
documentation on MSDN says this occurs before Auto_Open.

Is there any technique by which references in Excel may be checked
before
the exception for the missing reference is raised?

Regards,
JPL

I spoke too soon...

The technique worked fine for the connection and recordset objects,
but I think I need to do the same with the references check itself (?)

The check involves stepping through the refs:

For Each refItem In ActiveWorkbook.VBProject.References

This works OK on a PC with all the refs. intact:

Dim refItem As Reference

But this fails at runtime with Error Message 429 - "ActiveX component can't
create object"

Dim refItem As Object
Set refItem = CreateObject("VBIDE.Reference")

Do I have the wrong top-level object (VBIDE), or is this just the wrong way
to process a Reference, or what? Any help would be appreciated.

JPL


"Bob Phillips" wrote in message
...
Yeah, it is very useful in these multi-version environments.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JPL" <j.p.lxdsl.pipex.com wrote in message
...
Bob,

Thanks very much. That has done the trick.

I was aware of the concept of late binding but had not realised the full
implications and so had not used it before, and certainly had not twigged
its relevance here.

Regards,
JPL

"Bob Phillips" wrote in message
...
Use late binding.

Dim oConn as Object
or
Dim oRS as Object

with

Set oConn = CreateObject("ADODB.Connection")
or
Set oRS = CreateObject("ADODB.Recordset")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JPL" <j.p.lxdsl.pipex.com wrote in message
...
Hi,

I have an Excel workbook that reads data from an Access database and
copies
it into spreadsheet tables. That works fine on my PC, but there is a
problem for anyone who has a different version of MDAC installed on
their
PC. In this case, it fails immediately (i.e. before any of my code is
executed) with the awful error message "Compile error in hidden
module".

A check to find the item (ADODB in this case) in
Application.VBE.ActiveVBProject.References works correctly for all OK
references, but is irrelevant if the reference is not OK because the
exception occurs before the code is called, and "Compile error in
hidden
module" is reported anyway.

The check is in my Sub CheckRefs, which is called directly from
Workbook_Open; like Workbook_Open, the sub is in ThisWorkbook.

A similar check in Word - to ensure that the global template AddIn has
been
installed - works correctly provided that direct references to
routines
in
the global template are avoided within routines in ThisDocument, i.e.
Document_New, Document_Open, etc. The exception can therefore be
controlled
in Word.

Is Workbook_Open the earliest event on which I can hang the code? The
documentation on MSDN says this occurs before Auto_Open.

Is there any technique by which references in Excel may be checked
before
the exception for the missing reference is raised?

Regards,
JPL

With late binding, why bother with the references check?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JPL" <j.p.lxdsl.pipex.com wrote in message
...
I spoke too soon...

The technique worked fine for the connection and recordset objects,
but I think I need to do the same with the references check itself (?)

The check involves stepping through the refs:

For Each refItem In ActiveWorkbook.VBProject.References

This works OK on a PC with all the refs. intact:

Dim refItem As Reference

But this fails at runtime with Error Message 429 - "ActiveX component
can't
create object"

Dim refItem As Object
Set refItem = CreateObject("VBIDE.Reference")

Do I have the wrong top-level object (VBIDE), or is this just the wrong
way
to process a Reference, or what? Any help would be appreciated.

JPL


"Bob Phillips" wrote in message
...
Yeah, it is very useful in these multi-version environments.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JPL" <j.p.lxdsl.pipex.com wrote in message
...
Bob,

Thanks very much. That has done the trick.

I was aware of the concept of late binding but had not realised the
full
implications and so had not used it before, and certainly had not
twigged
its relevance here.

Regards,
JPL

"Bob Phillips" wrote in message
...
Use late binding.

Dim oConn as Object
or
Dim oRS as Object

with

Set oConn = CreateObject("ADODB.Connection")
or
Set oRS = CreateObject("ADODB.Recordset")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"JPL" <j.p.lxdsl.pipex.com wrote in message
...
Hi,

I have an Excel workbook that reads data from an Access database and
copies
it into spreadsheet tables. That works fine on my PC, but there is
a
problem for anyone who has a different version of MDAC installed on
their
PC. In this case, it fails immediately (i.e. before any of my code
is
executed) with the awful error message "Compile error in hidden
module".

A check to find the item (ADODB in this case) in
Application.VBE.ActiveVBProject.References works correctly for all
OK
references, but is irrelevant if the reference is not OK because the
exception occurs before the code is called, and "Compile error in
hidden
module" is reported anyway.

The check is in my Sub CheckRefs, which is called directly from
Workbook_Open; like Workbook_Open, the sub is in ThisWorkbook.

A similar check in Word - to ensure that the global template AddIn
has
been
installed - works correctly provided that direct references to
routines
in
the global template are avoided within routines in ThisDocument,
i.e.
Document_New, Document_Open, etc. The exception can therefore be
controlled
in Word.

Is Workbook_Open the earliest event on which I can hang the code?
The
documentation on MSDN says this occurs before Auto_Open.

Is there any technique by which references in Excel may be checked
before
the exception for the missing reference is raised?

Regards,
JPL