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