Exporting table to Excel - getting error - help please.

[FatMan]

Hi all:
Need some help with the following piece of code being used to export a table
to excel.

Code:
DoCmd.OutputTo acOutputTable, "tblSprayrec", acFormatXLS

The code works great when the user uses the save as dialog box to save the
file. My problem occurs when the users clicks the Cancel button. Once the
cancel button is pressed a “run-time error 2501†message box pops up with the
only options being to click End or Debug.

How do I prevent this error message from showing up and have the program to
simply accept that the user has decided to cancel the export/save?

Any help is greatly appreciated.

Thanks,
FatMan

 

[Brendan Reynolds]

Hi all:
Need some help with the following piece of code being used to export a
table
to excel.

Code:
DoCmd.OutputTo acOutputTable, "tblSprayrec", acFormatXLS

The code works great when the user uses the save as dialog box to save the
file. My problem occurs when the users clicks the Cancel button. Once
the
cancel button is pressed a “run-time error 2501†message box pops up with
the
only options being to click End or Debug.

How do I prevent this error message from showing up and have the program
to
simply accept that the user has decided to cancel the export/save?

Any help is greatly appreciated.

Thanks,
FatMan

Trap the error. Standard VBA error-handling usually looks something like
this ...

Begin Sub SomeSub

On Error GoTo ErrorHandler
DoCmd.OutputTo acOutputTable, "tblSprayrec", acFormatXLS

ExitProcedure:
Exit Sub

ErrorHandler:
If Err.Number = 2501 Then
'user cancelled, no need to do anything
Else
'unexpected error
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOkOnly +
vbInformation
End If
Resume ExitProcedure

End Sub

 

[Tom van Stiphout]

On Wed, 1 Apr 2009 05:46:01 -0700, FatMan

Use an error handler. Check the help file for the On Error statement.
In the error handler you can then test for Err.Number being set to
2501 and ignore it.

-Tom.
Microsoft Access MVP