copyfromrecordset automation error
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 Blogs Channel 9 Documentation method 'copyfromrecordset' of object 'range' failed access APIs and reference Dev centers Retired content Samples We’re sorry. The content vba automation error 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 Integer Dim strBeginAddress As String Dim strEndAddress As String DoCmd.Hourglass True DoCmd.SetWarnings False Set db = CurrentDb() strFileName = ParseDBPath(CurrentDb().Name) & "Assertion Export Data.XLS"
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 vote favorite I'm running into an issue when executing this line of code, that is contained in a https://social.msdn.microsoft.com/Forums/en-US/c487e0f9-0681-4d3d-a980-38e1fdffe7e5/copyfromrecordset-automation-error?forum=isvvba 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 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 http://stackoverflow.com/questions/23061251/automation-error-with-range-recordset-method 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 active oldest votes up vote 1 down vote Apparently when you call the CopyFromRecordset(RS) method with parentheses, it expects a variant. I moved the RS out of the parentheses and now it works. If only I had gotten a type error instead of this automation error this would've been solved last week. share|impr
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 http://www.access-programmers.co.uk/forums/showthread.php?t=111433 Rate Thread Display Modes 07-22-2006, 03:25 AM #1 pman78x Guest Posts: https://bytes.com/topic/access/answers/211069-error-430-copyfromrecordset-dao-3-6-a 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 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 automation error the CopyFromRecordSet statement: Run 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 copyfromrecordset automation error Template = "Matrix.xlt" Const StartRow = 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