Problems with exporting records from Access to Excel.

[roaml]

Hi,

Can someone tell me why when I execute the following code and go to view my Excel file the Excel application returnes with an error?

I'm trying to export records from an Access (2000) table to an Excel file.


<BEGIN CODE>
Private Sub Export_TT_Records_Click()
On Error GoTo Err_Export_TT_Records _Click

Dim StrCriterion As String
Dim strDocName As String

'Open Excel report and clear existing data

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "sev_other_tt_qry", "C:\Project_Trouble_Ticket_Report.xls", True, "OTHER"


' MISSAGE DIALOG BOX -- Display message when data export is completed.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

strMsg = "Project records have been successfully exported!"
strTitle = "Export Project Data"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Cancel = True

Exit_Export_TT_Records_Click:
Exit Sub

Err_Export_TT_Records_Click:
MsgBox Err.Description
Resume Exit_Export_TT_Records_Click

End Sub
<END CODE>

 

[SkipVought]

Hi,

Why EXPORT an entire table from Access to Excel?

You could more easily, with ABSOLUTELY NO VBA CODE AT ALL, create a query in Excel using Data/Get External Data/New Database Query...

You could either query a criteria quallified query or on that returns the entire table. The beauty is that each time you Data/Refresh Data, you get CURRENT DATA from Access.

Skip,
[sub]
[red]Be Advised![/red] Dyslexic poets...
write inverse! [/sub]