Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Reference to ADODB in Workbook_Open
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Reference to ADODB in Workbook_Open
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Reference to ADODB in Workbook_Open
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Reference to ADODB in Workbook_Open
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Reference to ADODB in Workbook_Open
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Reference to ADODB in Workbook_Open
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Reference to ADODB in Workbook_Open
Because there appears to be a version dependence on MDAC (although it may be
an artefact of the previous early binding - I do not have a suitable PC for testing this until I return to the office on Monday) and I would like to check the version up front rather than wait for it to go wrong. JPL "Bob Phillips" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Reference to ADODB in Workbook_Open
I had the opportunity to check today on a PC that generates the exception,
and the change to late binding does not solve the problem - it merely moves the exception from the time when the spreadsheet is opened to the time when the CreateObject call is executed. I'm still not sure why On Error ... does not catch it. JPL "Bob Phillips" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check Reference to ADODB in Workbook_Open
Post the code and let us try it
-- HTH RP (remove nothere from the email address if mailing direct) "JPL" <j.p.lxdsl.pipex.com wrote in message ... I had the opportunity to check today on a PC that generates the exception, and the change to late binding does not solve the problem - it merely moves the exception from the time when the spreadsheet is opened to the time when the CreateObject call is executed. I'm still not sure why On Error ... does not catch it. JPL "Bob Phillips" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disable sheet reference check | Excel Worksheet Functions | |||
Check-box relative reference | Excel Discussion (Misc queries) | |||
Check Box reference name | Excel Discussion (Misc queries) | |||
Check If COM Reference Available on Machine | Excel Programming | |||
ADODB | Excel Programming |