I'm grateful for any help you can provide.

There are three basic approaches for this sort of thing:

1.  In Excel, define a named range for your target cells.  Link to this
range in Access, then use an append query to add the data to the
spreadsheet.
2.  Define a named range and use the DoCmd.TransferSpreadsheet method to add
the data to the range.
3.  Use automation to open the spreadsheet in Excel and add the data to a
named range or an explicit cell reference (for which the Excel
CopyFromRecordset method comes in quite handy).

If you don't already have a named range in your spreadsheet, you would need
to use automation to create it anyway, in which case you might as well use
approach #3.

HTH,
Nicole

< http://www.mvps.org/access/modules/mdl0035.htm >

 -- Dev

Firstly thanks very much for your response to my request.

I have downloaded the example code for transferring a recordset from access
to excel from your site but cannot get it to run - I keep getting a compile
error message

"User defined type not defined"

The code I have tried is shown below, with the changes I have made to path
and file names.

(The first line of the code is highlighted when the error message appears)

I am using Office 97, Windows 95

I am sorry if I am being stupid here!

Hope you can help

Gordon

Sub sCopyRSExample()
'Copy records to first 20000 rows
'in an existing Excel Workbook and worksheet
'

Dim objWkb As Excel.Workbook
Dim objXL As Excel.Application
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim intLastCol As Integer
Const conMAX_ROWS = 35000
Const conSHT_NAME = "data"
Const conWKB_NAME = "d:/breachdata.xls"
  Set db = CurrentDb
  Set objXL = New Excel.Application
  Set rs = db.OpenRecordset("Bus Banking Last", dbOpenSnapshot)
  With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not Err.Number = 0 Then
      Set objSht = objWkb.Worksheets.Add
      objSht.Name = conSHT_NAME
    End If
    Err.Clear
    On Error GoTo 0
    intLastCol = objSht.UsedRange.Columns.Count
    With objSht
      .Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
          intLastCol)).ClearContents
      .Range(.Cells(1, 1), _
        .Cells(1, rs.Fields.Count)).Font.Bold = True
      .Range("A2").CopyFromRecordset rs
    End With
  End With
  Set objSht = Nothing
  Set objWkb = Nothing
  Set objXL = Nothing
  Set rs = Nothing
  Set db = Nothing
End Sub