Excel 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 Blogs Channel 9 Documentation APIs and reference Dev centers Retired content Samples We’re method 'copyfromrecordset' of object 'range' failed access sorry. The content you requested has been removed. You’ll be auto redirected in 1
Copyfromrecordset Error -2147467259
second. Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Asked by: vba automation error 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" 'This is the source query of the recordset strSQL = "select * from qryMainPanelData_Comments " If IsNull(Forms("frmassertionsmain").Controls("txtformfilter")) = False And Len(Trim(Forms("frmassertionsmain").Controls("txtformfilter"))) > 0 Then strSQL = strSQL & " where " & Forms("frmassertionsmain").Controls("txtformfilter") End If If IsNull(Forms("frmassertionsmain")!txtFormOrderBy) = False And Len(Trim(Forms("frmassertionsmain")!txtFormOrderBy)) > 0 Then strSQL = strSQL & " order by " & Forms("frmassert
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 https://social.msdn.microsoft.com/Forums/en-US/c487e0f9-0681-4d3d-a980-38e1fdffe7e5/copyfromrecordset-automation-error?forum=isvvba 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 readability Set classCutCells = reisWS.Range(reisWS.cells(classCutRow, classCutCol), _ _ reisWS.cells(classCutRow + RS.RecordCount, classCutRow + RS.Fields.Count)) http://stackoverflow.com/questions/23061251/automation-error-with-range-recordset-method '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 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 parenthe
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 http://stackoverflow.com/questions/13129090/excel-automation-error-importing-db-into-worksheets Overflow the company Business Learn more about hiring developers or posting ads with us https://bytes.com/topic/access/answers/914230-copyfromrecordset-dao-issues 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 Excel Automation Error Importing DB into Worksheets up vote 1 down vote favorite I have a automation error workbook with 192 worksheets that correspond to the 192 tables in our mssql database. If I set up a given table in Data Connections Wizard, all of the data is properly dumped into the worksheet. However, when I run my code below, I get: Run-Time Error '214767259 (80004005)' Automation Error Unspecified Error About half of the tables populate just fine. I've noticed that I get the error once it reaches a field excel copyfromrecordset automation that has a large amount of data (rtf text). The fields that have that text aren't important to me, so if excel could leave those blank and continue on I would be happy. That large field is in a different column (sometimes multiple columns) depending on each table, so it'd be time consuming to have to go through all 192 tables to weed out an individual column to not import. Why am I getting this error when I run it in vba, but the data connection wizard has no problems? Sub GetData() Dim cnDump As ADODB.Connection Set cnDump = New ADODB.Connection ' Provide the connection string. Dim strConn As String 'Use the SQL Server OLE DB Provider. strConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=XXXX;Data Source=XXXX\XXXX;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XXXX;Use Encryption for Data=False;Tag with column collation when possible=False;" 'Now open the connection. cnDump.Open strConn ' GET DATA Dim ws As Worksheet Dim tbl_name As String Dim rsDump As ADODB.Recordset Set rsDump = New ADODB.Recordset For Each ws In Worksheets tbl_name = ws.Name ws.Rows.ClearContents With rsDump .ActiveConnection = cnDump .Open "SELECT * FROM " & tbl_name For i = 1 To .Fields.Count ws.Cells(1, i) = .Fields(i - 1).Name Next i ws.Range("A2").CopyFromRecordset rsDump End With ws.Rows(1).Font.Bold = True Next ws cnDump.Close Set rsDum
your question and get tips & solutions from a community of 418,548 IT Pros & Developers. It's quick & easy. CopyfromRecordset DAO issues P: 8 Len Dalberti I am getting a "Run-Time 430 error, Class does not support Automation or does not support expected interface" with the following code. It appears to be related to the use of DAO instead of ADODB. This code is the combination of two codes, one with DAO and the other with ADODB. So I'm trying to marry the two, but I'm having problems. I don't have any preference to stay with DAO or ADODB (which it seems is better for Excel exporting). But I need help with the code. Anyways, here is the code (up until the error) and the last line posted is what is being flagged. Thanks for your help. And to clarify my references, these are active on my system: Visual Basic for Applications Microsoft Access 12.0 Object Library OLE Automation Microsoft Office 12.0 Access database engine Object library Microsoft Excel 12.0 Object Library Microsoft ActiveX Data Objects 2.1 Library Expand|Select|Wrap|Line Numbers PublicSubExportBIGrids_test() DimrsAsDAO.Recordset Dimrs1AsDAO.Recordset Dimrs2AsDAO.Recordset Dimrs3AsDAO.Recordset DimrstMgrAsDAO.Recordset DimdbsAsDAO.Database DimstrSQLAsString DimstrMgrAsString DimxAsNewExcel.Application DimwAsExcel.Workbook DimsAsExcel.Worksheet DimrAsRange Dimr1AsRange Dimr2AsRange Dimr3AsRange DimdAsString strSQL="SELECTDISTINCTBlowInNameFROMBlowIn_tblName" Setdbs=CurrentDb() SetrstMgr=dbs.OpenRecordset(strSQL) strMgr=DLookup("BlowInName","BlowIn_tblName","BlowInName="""&rstMgr!BlowInName&"""") d="K:\OBMSAdmin\Titles\BlowIns\" Setw=x.Workbooks.Open(d&"BlowInProfilesTemplate.xlsx") IfrstMgr.EOF=FalseAndrstMgr.BOF=FalseThen rstMgr.MoveFirst DoWhilerstMgr.EOF=False Setrs=dbs.OpenRecordset("SELECTBI_Name,BI_Title,BI_CPMFROMBlowIn_tblCPMWHEREBI_Name="""&rstMgr!BlowInName&"""") Setrs1=dbs.OpenRecordset("selectProfName,ProfCreative,ProfSize,ProfWeight,ProfStatusfromBlowIn_tblProfileWhereProfName="""&rstMgr!BlowInName&"""") Setrs2=dbs.OpenRecordset("selectBI_Name,BI_Event,BI_Creative,BI_Keycode,B