Option Explicit causes error with CopyFromRecordset?!? Excel 2007, SQL Server 2008 R2

JeffK627

Active Member
Joined
Jun 22, 2005
Messages
313
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 works fine as long as I don't use Option Explicit. I just don't like NOT using Option Explicit.

Any insights?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,389
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You should not have parentheses there:
Code:
wsActive.Range("A2").CopyFromRecordset objmyrecordset
 

JeffK627

Active Member
Joined
Jun 22, 2005
Messages
313
Oh good grief, that's all? I removed the parens and sure enough it works with Option Explicit now. Thanks!
 

Forum statistics

Threads
1,082,576
Messages
5,366,419
Members
400,888
Latest member
Cdim7

Some videos you may like

This Week's Hot Topics

Top