Access to Excel

[dagoose]

Hi All,

I am trying to link fields from a recordset to specific cells within an excel worksheet. For example;

"select * from test_table" returns:

row1 row2 row3
a b c
d e f

I would like to be able to say that the value from record 1 , row 1 is in cell A1 of an excel worksheet.

This is what i am using so far;

Dim dbCH As Database, rsCH As DAO.Recordset
Dim CHApp As Object

Set dbCH = CurrentDb
Set rsCH = dbCH.OpenRecordset("test_table2", dbOpenDynaset)

Set CHApp = CreateObject("Excel.Application")
CHApp.Visible = True

Dim chwb As Excel.Workbook
Dim chws1 As Excel.worksheet

Set chwb = Excel.Workbooks.Add
Set chws1 = Excel.Worksheets.Add
chws1.Range("A1").Value = "Test"

------------------------------------------------ problem here ---------------
'chws1.Range("A2").CopyFromRecordset (rsCH)
---------------------------------------------------------------------------------
any help appreciated

[alex_read]

Here you go:
' From the tools menu in Access, click References & check
' "Microsoft DAO x.x" and "Microsoft Excel x.0" Object
' library references.
Private Sub Command0_Click()
Dim dbCH As Database
Dim rsCH As DAO.Recordset

Dim CHApp As Excel.Application
Dim chws1 As Excel.worksheet

Set dbCH = CurrentDb
Set rsCH = dbCH.OpenRecordset("test_table2")

Set CHApp = New Excel.Application

CHApp.Visible = True
CHApp.Workbooks.Add

Set chws1 = CHApp.Workbooks(1).Worksheets(1)

chws1.Range("A1").Value = "Test"
chws1.Range("A2").CopyFromRecordset rsCH

chws1.SaveAs "C:\Test.xls"
Set chws1 = Nothing

CHApp.Workbooks(1).Close
CHApp.Quit
Set CHApp = Nothing

rsCH.Close
Set rsCH = Nothing

Set dbCH = Nothing
End Sub
I just want to add a couple of things here too to help:

1) You don't need to enclose methods in parethesis in access (i.e.on the line with the error, place the recordset variable outside of the brackets - remove them completely).

2) DAO is an older technology & if you can learn it's replacement (ADO) you'll get much quicker and more efficient code.

3) When you get 5 mins, look up LATE BINDING and EARLY BINDING in the access/msdn help files to see why I've created the initial Excel object this way - If you're coding for different versions of Excel & did that purposely, then I'll say sorry now & shut up, but if not thentry to look this up.

Cheers! :D

[dagoose]

Many thanks for your help, apologies for the long delay in replying. (pooter problems)

Thanks again