Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Calling A subroutine from a User Defined Function (UDF) that
I don't understand why I get an "Unspecified Automation Error" when calling a
subroutine from a UDF. If I run the code from an Excel Button calling the subroutine as a macro no errors are reported. Any light on this... here is the code: ---------------------------------------------------------------------------------- Option Explicit Public Sub GetAccessData() On Error GoTo ErrorHandler 'Step 1: Declare your Variables Dim MyConnect As String Dim DBConn As ADODB.Connection Dim MyRecordset As ADODB.Recordset 'Step 2: Declare your Connection String MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source= C:\Documents and Settings\csanchez\My Documents\My Databases\TrendGraphics.accdb" 'Step 3: Instantiate and Specify your Recordset Set DBConn = New ADODB.Connection DBConn.Open MyConnect Set MyRecordset = New ADODB.Recordset MyRecordset.Open "BrokersQry", MyConnect, adOpenStatic, adLockReadOnly 'Step 4: Copy the Recordset to Excel Sheets("Test").Select ActiveSheet.Range("A2").CopyFromRecordset MyRecordset 'Step 5: Add Column Labels With ActiveSheet.Range("A1:C1") .Value = Array("Product", "Description", "Segment") .EntireColumn.AutoFit End With Exit Sub ErrorHandler: MsgBox "Error Captured" End Sub Function Test() As Integer Call GetAccessData End Function ---------------------------------------------------------------------------------- If I set the fomolling formula in a given cell: =Test() , an error is triggered once the GetAccessData() code reaches this statement: ActiveSheet.Range("A2").CopyFromRecordset MyRecordset But it will execute without problems if called by running as a Macro from a button. ---- I am not knowledgable about ADO and this is part of my testing for the ultimate goal: Need to execute a parameter query in Access 2007 that returns a single value to an Excel 2007 cell many times over (50+). I need to place the returned value to a cell and then analyze the results within excel. THis is why I want to use a UDF to return the Access query result. THanks in advance for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Calling A subroutine from a User Defined Function (UDF) that
This might help
http://support.microsoft.com/kb/170787 -- HTH, Barb Reinhardt "Carlos" wrote: I don't understand why I get an "Unspecified Automation Error" when calling a subroutine from a UDF. If I run the code from an Excel Button calling the subroutine as a macro no errors are reported. Any light on this... here is the code: ---------------------------------------------------------------------------------- Option Explicit Public Sub GetAccessData() On Error GoTo ErrorHandler 'Step 1: Declare your Variables Dim MyConnect As String Dim DBConn As ADODB.Connection Dim MyRecordset As ADODB.Recordset 'Step 2: Declare your Connection String MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source= C:\Documents and Settings\csanchez\My Documents\My Databases\TrendGraphics.accdb" 'Step 3: Instantiate and Specify your Recordset Set DBConn = New ADODB.Connection DBConn.Open MyConnect Set MyRecordset = New ADODB.Recordset MyRecordset.Open "BrokersQry", MyConnect, adOpenStatic, adLockReadOnly 'Step 4: Copy the Recordset to Excel Sheets("Test").Select ActiveSheet.Range("A2").CopyFromRecordset MyRecordset 'Step 5: Add Column Labels With ActiveSheet.Range("A1:C1") .Value = Array("Product", "Description", "Segment") .EntireColumn.AutoFit End With Exit Sub ErrorHandler: MsgBox "Error Captured" End Sub Function Test() As Integer Call GetAccessData End Function ---------------------------------------------------------------------------------- If I set the fomolling formula in a given cell: =Test() , an error is triggered once the GetAccessData() code reaches this statement: ActiveSheet.Range("A2").CopyFromRecordset MyRecordset But it will execute without problems if called by running as a Macro from a button. ---- I am not knowledgable about ADO and this is part of my testing for the ultimate goal: Need to execute a parameter query in Access 2007 that returns a single value to an Excel 2007 cell many times over (50+). I need to place the returned value to a cell and then analyze the results within excel. THis is why I want to use a UDF to return the Access query result. THanks in advance for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Calling A subroutine from a User Defined Function (UDF) that
FOr those that may be interested here is a solution to the problem, at least
in my case it does resolve the issue: Option Explicit Public Sub GetAccessData(ByRef RSLT As String) On Error GoTo ErrorHandler 'Step 1: Declare your Variables Dim MyConnect As String Dim DBConn As ADODB.Connection Dim MyRecordset As ADODB.Recordset Dim Brokers As Variant 'Step 2: Declare your Connection String MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source= C:\Documents and Settings\csanchez\My Documents\My Databases\TrendGraphics.accdb" 'Step 3: Instantiate and Specify your Recordset Set DBConn = New ADODB.Connection DBConn.Open MyConnect Set MyRecordset = New ADODB.Recordset MyRecordset.Open "BrokersQry", MyConnect, adOpenStatic, adLockReadOnly 'Step 4: Copy the Recordset to Excel Brokers = MyRecordset.GetRows(1, 0, 0) RSLT = Brokers(0, 0) Exit Sub ErrorHandler: MsgBox "Error Captured" End Sub Function Test() As Integer Dim RSLT As String Call GetAccessData(RSLT) Test = RSLT End Function "Carlos" wrote: I don't understand why I get an "Unspecified Automation Error" when calling a subroutine from a UDF. If I run the code from an Excel Button calling the subroutine as a macro no errors are reported. Any light on this... here is the code: ---------------------------------------------------------------------------------- Option Explicit Public Sub GetAccessData() On Error GoTo ErrorHandler 'Step 1: Declare your Variables Dim MyConnect As String Dim DBConn As ADODB.Connection Dim MyRecordset As ADODB.Recordset 'Step 2: Declare your Connection String MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ "Data Source= C:\Documents and Settings\csanchez\My Documents\My Databases\TrendGraphics.accdb" 'Step 3: Instantiate and Specify your Recordset Set DBConn = New ADODB.Connection DBConn.Open MyConnect Set MyRecordset = New ADODB.Recordset MyRecordset.Open "BrokersQry", MyConnect, adOpenStatic, adLockReadOnly 'Step 4: Copy the Recordset to Excel Sheets("Test").Select ActiveSheet.Range("A2").CopyFromRecordset MyRecordset 'Step 5: Add Column Labels With ActiveSheet.Range("A1:C1") .Value = Array("Product", "Description", "Segment") .EntireColumn.AutoFit End With Exit Sub ErrorHandler: MsgBox "Error Captured" End Sub Function Test() As Integer Call GetAccessData End Function ---------------------------------------------------------------------------------- If I set the fomolling formula in a given cell: =Test() , an error is triggered once the GetAccessData() code reaches this statement: ActiveSheet.Range("A2").CopyFromRecordset MyRecordset But it will execute without problems if called by running as a Macro from a button. ---- I am not knowledgable about ADO and this is part of my testing for the ultimate goal: Need to execute a parameter query in Access 2007 that returns a single value to an Excel 2007 cell many times over (50+). I need to place the returned value to a cell and then analyze the results within excel. THis is why I want to use a UDF to return the Access query result. THanks in advance for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel User Defined function calling RTD | Excel Programming | |||
Excel User Defined function calling RTD | Excel Programming | |||
Which cell is calling a user-defined function? | Excel Programming | |||
Calling Excel user defined function from C# | Excel Programming | |||
Calling user defined function from C/C++ | Excel Programming |