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?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. 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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,373
Messages
5,635,884
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top