CopyFromRecordset Problem

[mac6777]

I am having a problem with the CopyFromRecordset function in VBA Access. I am attempting to run VBA in an Access Database that copies query results into an Excel spreadsheet. The VBA opens an Excel template, runs the query, then attempts to copy the query as a recordset into the Excel spreadsheet. The problem is that I get the following error when trying to use the CopyFromRecordset function: "Run-time error '430': Class does not support expected interface. Below is the code I am using. The last line is the one that causes the error. What am I doing wrong? Thanks for any and all help.

Sub Create_OH_Detail_Report()
 
'***Set path of OH Detail Report Template
Dim OHrptPath As String
OHrptPath = "\\kaci-fs-05\energy_finance\Management Financial Reporting\Overhead Analysis\Overhead Detail Report\OH Detail Energy Template.xls"
 
'***Opening OH Detail Report Template
Dim myDB As Database
Set myDB = CurrentDb
Set ExcelWindow = CreateObject("excel.application")
ExcelWindow.Visible = True
ExcelWindow.Workbooks.Open (OHrptPath)
Dim OHrpt As Workbook
Set OHrpt = activeworkbook
 
'***Inserting Current Month and Year in cell A1
curMOname = Forms!Basic!Text35
CurYr = Forms!Basic!Text25
Dim Heading
Heading = curMOname & " " & CurYr & " Energy Overhead"
OHrpt.Worksheets(1).Range("a1").Value = Heading
wksht = curMOname & " " & CurYr & " OH"
OHrpt.Worksheets(1).Name = wksht
 
'***Running the 'Overhead Detail' query
OHrpt.Application.WindowState = xlMinimized
DoCmd.OpenQuery "Overhead Detail", , acReadOnly
DoCmd.Close acQuery, "Overhead Detail"
Dim Rst1 As Recordset
Set Rst1 = myDB.OpenRecordset("Overhead Detail")
OHrpt.Worksheets(1).Range("a5").CopyFromRecordset Rst1

[FishVal]

Hi, there.
Try ADO recordset instead of DAO.

[mac6777]

Hi, there.
Try ADO recordset instead of DAO.

How exactly is that done?

[FishVal]

Dim Rst1 As New ADODB.Recordset
With Rst1
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .ActiveConnection = CurrentProject.Connection
    .Open "Overhead Detail"
End With
 

[mac6777]

Hi, there.
Try ADO recordset instead of DAO.

I changed the code to declare the Recordset Rst1 as an ADO Recordset:

Dim Rst1 As ADODB.Recordset

But I then got the following error: "Run-time error '13': Type mismatch"

Is there something else that I need to do?

[mac6777]

Dim Rst1 As New ADODB.Recordset
With Rst1
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .ActiveConnection = CurrentProject.Connection
    .Open "Overhead Detail"
End With
 

I tried the code above and am still getting a Run-time error '13': Type Mismatch error on the following line of code:

[PHP]

Set Rst1 = myDB.OpenRecordset("Overhead Detail")

[/PHP]

[FishVal]

Replace in your code

Dim Rst1 As Recordset
Set Rst1 = myDB.OpenRecordset("Overhead Detail")
 

with the code I've posted before.

[mac6777]

Replace in your code

Dim Rst1 As Recordset
Set Rst1 = myDB.OpenRecordset("Overhead Detail")
 

with the code I've posted before.

I've replaced the code and am now getting the following error "Run-time error '-2147217900 (80040e14)': Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

The error occurs on the following line of code:
[PHP].Open "Overhead Detail"[/PHP]

[FishVal]

I've replaced the code and am now getting the following error "Run-time error '-2147217900 (80040e14)': Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

The error occurs on the following line of code:
[PHP].Open "Overhead Detail"[/PHP]

Replace it with

.Open "[Overhead Detail]"
 

or

.Open "SELECT * FROM [Overhead Detail];"
 

[mac6777]

Replace it with

.Open "[Overhead Detail]"
 

or

.Open "SELECT * FROM [Overhead Detail];"
 

It worked perfectly. Thanks so much for your help!

[FishVal]

Not a problem.
Good luck.

[boldbuff]

@FishVal
FishVal : I tried searching this solution on like a gazzilion sites!!

Thanks very much...works perfectly!