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!

[dagoose]

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

Thanks again