locked
Excel 2016 64bit CopyFromRecordset error (from 32 bit VBA) RRS feed

  • Question

  • I recently moved from 32 bit office 2013 to 64 bit office 2016. In the process, one of my macros stopped functioning (but still functions with other computers running 32 bit 2013). Admittedly, I have limited experience with VBA but I have spend a lot of time over the last few months attempting to grow more competent in it. The original creator of this macro is no longer around to view or change it but I will do my best to explain the issue. 

    When run, I get the error "Method 'CopyFromRecordset' of object 'Range' failed.

    The code is a sub process of a much larger macro that appears as follows:


    Sub GetSQLData(sql, ConnString As String, TimeOut As Integer, ColumnHeadings As Boolean, ColumnStart As String, WorksheetName As String, RangeLocation As String)

    Dim connection As ADODB.connection
    Dim Results As ADODB.Recordset
    Dim x As Integer

    Set connection = CreateObject("ADODB.Connection")
    connection.ConnectionString = ConnString
    connection.CommandTimeout = TimeOut
    connection.Open

    Set Results = CreateObject("ADODB.Recordset")

    Results.Open sql, connection, adOpenForwardOnly

    ActiveWorkbook.Sheets(WorksheetName).Select

    If ColumnHeadings = True Then
    For x = 0 To Results.Fields.Count - 1
    Range(ColumnStart).Offset(0, x) = Results.Fields(x).Name
    Next x
    End If
    Range(RangeLocation, _
    Range(RangeLocation).Offset(Range(RangeLocation).CurrentRegion.Rows.Count - 2, _
    Results.Fields.Count)).ClearContents
    Range(RangeLocation).CopyFromRecordset Results

    Results.Close
    connection.Close
    Set Results = Nothing
    Set connection = Nothing

    End Sub

    The goal is to paste data from SQL into a worksheet in excel, which will process further with another part of the macro. The highlighted error is the portion the debugger leads me to. I have no idea where to go from here and other answered forums have not been any help as of yet, explaining that recordsets are simply different from 32 to 64 bit Office. 

    Thank you!
    Monday, December 14, 2015 6:31 PM

All replies

  • Hi there have you had a response to this? I have the same issue. I can't believe it has not been addressed.
    Tuesday, April 12, 2016 12:17 AM
  • See 64bit compatibility problems, e.g. https://www.thespreadsheetguru.com/blog/2015/1/13/how-to-use-vba-code-to-copy-text-to-the-clipboard

    Monday, December 5, 2016 9:35 AM
  • Hello fellows,

    I have similar issue with Excel 2016 (64bit) running on Windows Server 2012 R2

    When I call CopyFromRecordset in VB6 program (32bit), the program is crashing.

    This crash looks like to be random (I use always the same RecordSet) or related to some cache memory.

    My Excel is opening 2-3 times in a row (with correct data), and then "crash"

    Do you have any idea how to fix it ?

    Friday, April 20, 2018 2:19 PM