Here is what I use. Trapping for error # 3010 will allow you to alert the user why the export did not work.
Private Sub bExport_Click()
On Error GoTo Err_bExport_Click
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "qselectAuditors", "X:\Auditors.xls", True
Exit_bExport_Click:
Exit Sub
Err_bExport_Click:
If Err = 3010 Then 'Table '???' already exists - Excel file is already opened.
Beep
MsgBox "The '" & Me.cbReports.Column(1) & "' report you attempted to export as an Excel file is already opened on your computer." & vbCrLf & vbLf & "Please close the Excel file before attempting to export the report.", vbCritical, "Excel Export Error"
Else
MsgBox Err.Number, Err.Description
Resume Exit_bExport_Click
End If
End Sub
HTH