Error populating excel sheet via automation from Access 97


I am trying to use automation to populate a named range on an excel spreadsheet via automation from a Access 97 front end.  I am having a problem with the following line in the attached code
ActiveWorkbook.Names("CSIBDistribution").CopyFromRecordset rst

I am getting an error message that the object doesn't support this property.  I'd appreciate any help
Public Function SanityCheckReport() As Boolean

If CreateExcelObj = True Then

        Dim strSql As String
        Dim strPath As String
        Dim rst As ADODB.Recordset
        Dim cmd As ADODB.Command
        Dim strConnection As String
        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & CurrentDb.Name
        Set rst = New ADODB.Recordset
        Set cmd = New ADODB.Command
        cmd.CommandType = adCmdStoredProc
        cmd.ActiveConnection = strConnection
        cmd.CommandText = "qryFinalDistributions"
        Set rst = cmd.Execute

        strPath = "C:\Databases\97\TEST.xls"
        gobjExcel.Workbooks.Open FileName:=strPath
        gobjExcel.Visible = True
        ActiveWorkbook.Names("CSIBDistribution").CopyFromRecordset rst
        'Set gobjExcel = Nothing
        'Set rst = Nothing
    MsgBox "Spreadsheet creation has been canceled"
    Exit Function
End If

End Function

Open in new window

Juan VelasquezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
CopyFromRecordset is really only looking for the top left cell, not an entire range.
Try this

ActiveWorkbook.Range("CSIBDistribution").CopyFromRecordset rst

Rey Obrero (Capricorn1)Commented:
if you want use named range, use

 strPath = "C:\Databases\97\TEST.xls"

docmd.transferspreadsheet acexport,,"qryFinalDistributions",strPath,0,"CSIBDistribution"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
You need a Range object not a Name object:
ActiveWorkbook.Names("CSIBDistribution").RefersToRange.cells(1).CopyFromRecordset rst

Open in new window

for example.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.