DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel97,
"qry_CAMRA_Watchlist_Indiv_Portf",
"C:\Watchlist.xls", True

The problem is that this won't work if the file that I'm
trying to save to already exists. Meaning it only works
the first time.

Is there any other way of doing this? Optimally, I'd like
to preview the file in excel after clicking a command
button on a form.

Thanks.

  If Len(Dir(strMyFilePath))>0 Then
    Kill strMyFilePath
  End If

HTH

Tim F

You can get over the "file already exists" problem by checking for the file
first and deleting it:
    sFileName = "C:\Watchlist.xls"
    If Dir( sFileName ) <> "" then Kill sFileName
    DoCmd.TransferSpreadsheet ...

If you want to preview the file, then you will have to start Excel.  In this
case, you might as well do the whole thing using automation, and allow the
user to either close the file without saving, or save it and specify the
filename.  The following code should get you going:

Public Function ExportToExcel(sQuery As String, _
        sFileName As String, fPreview As Boolean)
Dim oXL As Excel.Application
Dim rs As DAO.Recordset, i As Integer
On Error GoTo ProcErr
  Set rs = CurrentDb.OpenRecordset(sQuery)
' Start Excel
  Set oXL = CreateObject("Excel.Application")
  With oXL
    .SheetsInNewWorkbook = 1
    .Workbooks.Add.Sheets(1).Activate
    With .ActiveSheet
' Create a headings row with the field names
      For i = 1 To rs.Fields.Count
        .Cells(1, i) = rs(i - 1).Name
      Next
      .Rows(1).Font.Bold = True
      .Rows(1).HorizontalAlignment = xlCenter
' Copy the data from the recordset
      .Cells(2, 1).CopyFromRecordset rs
' Autofit the columns
      .UsedRange.Columns.AutoFit
    End With
' This freezes the headings row to prevent it scrolling
    With .ActiveWindow
      .SplitRow = 1
      .FreezePanes = True
    End With
' Either show the result or save and quit
    If fPreview Then
      .UserControl = True
      .Visible = True
    Else
      .ActiveWorkbook.SaveAs sFileName
      .Quit
    End If
  End With
  Set oXL = Nothing
ProcEnd:
  On Error Resume Next
  rs.Close
  If Not oXL Is Nothing Then
' we had an error - quit Excel without saving
    oXL.DisplayAlerts = False
    oXL.Quit
    Set oXL = Nothing
  End If
  Exit Function
ProcErr:
  MsgBox Err.Description
  Resume ProcEnd
End Function

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.

Please post new questions or followups to newsgroup.