Access to Excel CopyFromRecordset Errors 1

[bobbobruns12]

Hi,

I'm trying to create a way to automatically fill in excel sheets from an access table. I built a test database just to try some things and see if I can apply it to some of my other projects. However I am running into an error with the range in the CopyFromRecordset line. My Code looks like this.

Public Function Module()

Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set xlapp = New Excel.Application
Set rs = db.OpenRecordset("Acc", dbOpenSnapshot)
xlapp.Visible = True
Set xlbook = xlapp.Workbooks.Open("C:\Documents and Settings\rbransfield1\Desktop\Test.xls")
Set xlsheet = xlbook.Worksheets("sheet2")
xlsheet.Range(A, 2).CopyFromRecordset rs, 5
'xlsheet.Cells(5, 5) = "Go"

Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing
Set rs = Nothing
Set db = Nothing
End Function

Excel opens ok and when I have the 'xlsheet.Cells(5,5) = "Go" un-commented that works. However I am trying to format the recordset to capture the table and display it in the cell and it gives me a "Method 'Range' of Object '_Worksheet failed. Does anyone have an idea of what is going wrong. Thanks for your help

 

[PHV]

Perhaps this ?
xlsheet.Range([!]"A2"[/!]).CopyFromRecordset rs, 5

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886