Copyfromrecordset Automation Error
Contents |
SQL Server 2014 Express resources Windows Server 2012 resources Programs MSDN subscriptions Overview Benefits Administrators Students Microsoft Imagine Microsoft Student Partners ISV Startups TechRewards Events Community Magazine Forums method 'copyfromrecordset' of object 'range' failed access Blogs Channel 9 Documentation APIs and reference Dev centers Retired content
Vba Automation Error
Samples We’re sorry. The content you requested has been removed. You’ll be auto redirected in 1 second. Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Asked by: CopyFromRecordset Automation Error Microsoft ISV Community Center > Visual Basic for Applications (VBA) Question 0 Sign in to vote I have an Access 2003 DB that I have migrated to 2007. It is still supported in 2003 and works perfectly. It does not behave as well in 2007. The problem is the places where I have used the CopyFromRecordset method of the range object. I have a number of places where I want to dump large amounts of data into Excel worksheets. The basic interfaces are working correctly.Everything runs fine until I get to the line of code that actually executes the copyfromrecordset method. At that point I get the error message "Run-Time Error: 430. Class does not support Automation or does not support expected interfaces". I am not sure what to think of this since I have included all the Excel Interfaces I would need to drive the operation from the Jet engine (under Access VBA) to Excel. Private Sub btnExport_Click() On Error GoTo Err_BtnExport Dim strSQL As String Dim db As DAO.Database Dim rs As DAO.Recordset Dim strFileName As String Dim XLApp As Excel.Application Dim XLBook As Excel.Workbook Dim XLSheet As Excel.Worksheet Dim XLRange As Excel.Range Dim XLRow As Integer Dim GrpCount As Long Dim Done As Boolean Dim RowCount As Long Dim ColCount As Long Dim strRange As String Dim fso As Scripting.FileSystemObject Dim i As
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Automation Error with Range.Recordset method up vote 0 down https://social.msdn.microsoft.com/Forums/en-US/c487e0f9-0681-4d3d-a980-38e1fdffe7e5/copyfromrecordset-automation-error?forum=isvvba vote favorite I'm running into an issue when executing this line of code, that is contained in a userform: Dim RS As ADODB.Recordset Set RS = CreateObject("ADODB.RecordSet") 'omitting the queryString and connection parameters - I'm highly certain the issue doesn't lie there RS.Open queryString, cn 'Declare the object Dim classCutCells As Range 'The next 2 lines are 1 in the actual code, just broke it up here for http://stackoverflow.com/questions/23061251/automation-error-with-range-recordset-method readability Set classCutCells = reisWS.Range(reisWS.cells(classCutRow, classCutCol), _ _ reisWS.cells(classCutRow + RS.RecordCount, classCutRow + RS.Fields.Count)) 'The following line produces the error classCutCells.CopyFromRecordset (RS) When the click event goes off, I get this error: Run-time error '430': Class does not support Automation or does not support expected interface. My current references include: Visual Basic for Applications Microsoft Excel 14.0 Object Library OLE Automation Microsoft Forms 2.0 Object Library Microsoft Office 14.0 Object Library Microsoft Scripting Runtime Microsoft Windows Common Controls 6.0 (SP6) Microsoft Visual Basic for Applications Exentisibility 5.3 Microsoft ActiveX Data Objects 6.0 Library When I step in to debug, and pass classCutCells.CopyFromRecordset (RS) into the immediate window, the code works without a hitch, and the data is dropped into desired range. I'm thinking this could be a reference issue, but have not found anything online after a few days of looking. Any ideas? excel excel-vba share|improve this question edited Apr 14 '14 at 13:34 asked Apr 14 '14 at 13:20 jeff m 151317 What type of declaration has RS? –lowak Apr 14 '14 at 13:32 @lowak RS is ADODB.Recordset - updated the code above. –jeff m Apr 14 '14 at 13:35 add a comment| 1 Answer 1 ac
Search Community Links Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag Search Advanced Search Find All Thanked Posts Go to Page... Thread Tools Rate Thread http://www.access-programmers.co.uk/forums/showthread.php?t=111433 Display Modes 07-22-2006, 03:25 AM #1 pman78x Guest Posts: n/a CopyFromRecordSet gives 430 run-time error I am trying to export data from MS Access (2003) to MS Excel (2003). For some reason this http://www.mrexcel.com/forum/excel-questions/744225-option-explicit-causes-error-copyfromrecordset-excel-2007-sql-server-2008-r2.html works perfectly in the English version of MS Office 2003, but not in the Ducth version of Office 2003. I get the following error message when the export routine gets to the CopyFromRecordSet statement: Run automation error time error 430 Class does not support automation . or does not support expected interface. Van anybody help me? Part of the code: Dim db As DAO.Database Dim rs As DAO.Recordset Dim qd As QueryDef Dim objXL As Object Dim objWkb As Object Dim objSht As Object Dim intMaxRow As Integer Set db = CurrentDb() Set qd = db.QueryDefs("qry_Matrix") Set rs = qd.OpenRecordset Const Template = "Matrix.xlt" Const StartRow = copyfromrecordset automation error 8 If rs.RecordCount > 0 Then rs.MoveLast: rs.MoveFirst intMaxRow = rs.RecordCount intMaxCol = rs.Fields.Count Set objXL = CreateObject("Excel.Application") With objXL .Visible = True Set objWkb = .Workbooks.Open(DbaseDir$ & Template) Set objSht = objWkb.Worksheets("Matrix") objSht.Name = "Matrix" With objSht .Range(.Cells(StartRow, 2), .Cells(StartRow + intMaxRow, 2)) _ .CopyFromRecordset rs End With End If rs.Close qd.Close db.Close 07-22-2006, 05:46 AM #2 PeterF Newly Registered User Join Date: Jun 2006 Posts: 284 Thanks: 2 Thanked 42 Times in 42 Posts Try naming the the worksheet different, I believe "matrix" is a reserved word in the dutch version. What is the name of the function?? PeterF View Public Profile Find More Posts by PeterF 07-22-2006, 10:15 AM #3 pman78x Guest Posts: n/a I just mentioned 'Matrix' here to shorten the code. The actual values are: Const Template = "Function Task Matrix.xlt" Set qd = db.QueryDefs("qry_Function_Task_Matrix_Crosstab_wi th_SODs") Set objSht = objWkb.Worksheets("Function-Task Matrix") objSht.Name = "Task-Function Matrix" Appologies for the confusion. The code is run on the on click event of a command button. It starts en ends with: Private Sub cmdFunctionTaskExcelwithSODs_Click() End Sub 07-23-2006, 01:40 AM #4 pman78x Guest Posts: n/a Problem is solved!
Forums Excel Questions Option Explicit causes error with CopyFromRecordset?!? Excel 2007, SQL Server 2008 R2 Results 1 to 3 of 3 1Likes Top All This Page 1 Post By RoryA Option Explicit causes error with CopyFromRecordset?!? Excel 2007, SQL Server 2008 R2This is a discussion on Option Explicit causes error with CopyFromRecordset?!? Excel 2007, SQL Server 2008 R2 within the Excel Questions forums, part of the Question Forums category; I've encountered an odd error in an Excel 2007 VBA sub. The code queries a SQL Server 2008 R2 database, ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Dec 10th, 2013,08:22 AM #1 JeffK627 Board Regular Join Date Jun 2005 Posts 306 Option Explicit causes error with CopyFromRecordset?!? Excel 2007, SQL Server 2008 R2 I've encountered an odd error in an Excel 2007 VBA sub. The code queries a SQL Server 2008 R2 database, then copies the recordset to a worksheet in the Excel workbook. If I don't specify Option Explicit, it works fine, but if I add Option Explicit I get the error "Class does not support automation or does not support expected interface." All the variables are properly declared and all the necessary references are included. Here's the code: Code: Private Sub populateSheet() Dim wsActive As Worksheet Dim wsSQL As Worksheet Dim sActive As String Dim objmyconn As ADODB.Connection Dim objmyrecordset As ADODB.Recordset Set wsActive = ThisWorkbook.Worksheets("Active") Set wsSQL = ThisWorkbook.Worksheets("SQL") Set objmyconn = New ADODB.Connection Set objmyrecordset = New ADODB.Recordset objmyconn.ConnectionString = "Provider=SQLOLEDB;Data Source=ABCDSQL1;Initial Catalog=WXYZP01;Integrated Security=SSPI;" objmyconn.Open sActive = wsSQL.Range("A1").Value & wsSQL.Range("A2").Value & wsSQL.Range("A3").Value & wsSQL.Range("A4").Value & wsSQL.Range("A5").Value Set objmyrecordset.ActiveConnection = objmyconn objmyrecordset.Open sActive wsActive.Range("A2").CopyFromRecordset (objmyrecordset) objmyrecordset.Close Set objmyrecordset = Nothing objmyconn.Close Set objmyconn = Nothing End Sub The error occurs on the line " wsActive.Range("A2").CopyFromRecordset (objmyrecordset)". The SQL query is dynamically created which is why it's concatenated from the values of a number of cells on another worksheet. I've tested the query itself and it works fine, and besides this whole thing