Exporting query's results to excel template

[bugsy]

Hi all
I have several databases that use the same code wor exporting query's results to excel template
(at the end of the message)
For some reason when i try to recycle it once again I get error message

Run-time error '-2147467259 (80004005)
Method 'CopyFromRecordset' of object 'Range' failed

i see that this current db I am trying to put in now has DAO version 3.51, other has 3.6
Could that matter ? Other references are identical.

'starting Excel
Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim oXL As Object

Dim objCreateWkb As Object
Dim objActiveWkb As Object

Set dbs = CurrentDb
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True

Dim sFile As String

sFile = "\\myfolder\Pivot2.xlt"
Set objActiveWkb = oXL.Workbooks.Open(Filename:=sFile, ReadOnly:=True)

'Sheet 1 - Reconciliation
'--------------------------------------------------------------------------------

objActiveWkb.Worksheets("Info").Select

Set rstGetRecordSet = dbs.OpenRecordset("qryPivot2")

objActiveWkb.Worksheets("Info").Cells(2, 1).CopyFromRecordset rstGetRecordSet

error happens on this line

objActiveWkb.Worksheets("Info").Cells(2, 1).CopyFromRecordset rstGetRecordSet

 

[boblarson]

Change this:

Dim rstGetRecordSet As Recordset

To this:

Dim rstGetRecordSet As DAO.Recordset

 

[bugsy]

changed.
Get exactly same error....

 

[boblarson]

How about:

Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim oXL As Object

Dim objCreateWkb As Object
Dim objActiveWkb As Object

Set dbs = CurrentDb
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True

Dim sFile As String

sFile = "\\myfolder\Pivot2.xlt"
Set objActiveWkb = oXL.Workbooks.Open(Filename:=sFile, ReadOnly:=True)

'Sheet 1 - Reconciliation
'--------------------------------------------------------------------------------

objActiveWkb.Worksheets("Info").Select

Set rstGetRecordSet = dbs.OpenRecordset("qryPivot2")

objActiveWkb.Worksheets("Info").Cells(2, 1).CopyFromRecordset rstGetRecordSet

To this:

Dim dbs As DAO.Database
Dim rstGetRecordSet As DAO.Recordset
Dim objXL As Object
Dim objActiveWkb As Object
Dim sFile As String


Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")

objXL.Visible = True



sFile = "\\myfolder\Pivot2.xlt"
Set objActiveWkb = oXL.Workbooks.Open(Filename:=sFile, ReadOnly:=True)

'Sheet 1 - Reconciliation
'--------------------------------------------------------------------------------

objActiveWkb.Worksheets("Info").Select

Set rstGetRecordSet = dbs.OpenRecordset("qryPivot2")

objActiveWkb.Worksheets("Info").Range("A2").CopyFromRecordset rstGetRecordSet

 

[bugsy]

now i get run time error 424
object Required

 

[boblarson]

where does it highlight? Go to DEBUG > COMPILE and see where it squawks.