Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Exporting To Excel

    Hello,

    I am exporting a query to an excel file which the user designates. Exporting it works as I want it to, except if you click Cancel, a VB Error box comes up with End or Debug. How do I keep this box from appearing?

    This is my code

    Code:
    DoCmd.OutputTo acOutputQuery, "qry_TerminatedEmployees"

    I tried this, but I guess I'm doing it wrong
    Code:
    DoCmd.OutputTo acOutputQuery, "qry_TerminatedEmployees"
    
    If OutputTo = vbCancel Then
    Exit Sub
    End If

  2. #2
    Join Date
    Oct 2009
    Posts
    10
    Not seeing the rest of the routine makes it a little difficult to see what is going wrong but maybe this example will help you.

    varResponse would be whatever you named the messagebox

    Code:
    If varResponse = vbCancel Then
             Cancel = True
        Exit Sub

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This is how I export data to an Excel file: http://www.dbforums.com/6390529-post84.html

    The user's seem very happy with how it works.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You can probably respond to the error with Error Trapping. Try adding:

    On Error Resume Next

    just before the export and see what happens.

  5. #5
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by DCKunkle View Post
    You can probably respond to the error with Error Trapping. Try adding:

    On Error Resume Next

    just before the export and see what happens.
    Perfect!

    Thank you very much.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by pkstormy View Post
    Originally Posted by DCKunkle
    You can probably respond to the error with Error Trapping. Try adding:

    On Error Resume Next

    just before the export and see what happens.
    Sorry I didn't address your specific issue.

    Just a note with using the On Error Resume Next. You may also want to add an On Error Goto 0 at the bottom of your routine otherwise your code can continue through other routines and not respond to errors that you may want it to respond to! On Error Resume Next can be a bit dangerous to do if you don't turn error checking back on.


    This would be preferred:

    DoCmd.OutputTo acOutputQuery, "qry_TerminatedEmployees"
    On error goto Done
    If OutputTo = vbCancel Then
    Exit Sub
    End If
    Done:
    Last edited by pkstormy; 10-30-09 at 19:24.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •