Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I having to export records to an Excel file. This I have working well. Except when exporting the records I end up with a number on empty Excel sheets, as there are no records to export.

    My apologies for being dim, the code that I'm using is pretty simple, I just can not see how to not export to Excel file when no records exist. I know that this staring me right in the face.

    Here is my code, would someone point me in the direction of a solution?

    Code:
    Private Sub btnRunAll_Click()
    
    Dim IndexNum As Integer
    
    For IndexNum = 0 To Me.cboRoutes.ListCount - 1
    
    'Select Routes and Run Query
    Me.cboRoutes.Value = Me.cboRoutes.ItemData(IndexNum)
        DoCmd.RunMacro "mcrMapPointRouteExport"
    
    Next IndexNum
        
        MsgBox "Your file can be found here:"
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    We might to know the details of the macro that you are running, but you should be able to put in a test before the line that runs the macro

    Code:
    if dcount("*", "qrysomequery") >0 then
     DoCmd.RunMacro "mcrMapPointRouteExport"
    end if
    Somehow qrysomequery needs to be the query that you are exporting for each iteration of the loop. How does the macro know what to export each time?
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello John

    The test did not work. All were exported to Excel sheets.

    The macro runs all listings from a combo box through a Query (qryMapPointExportSpecial).

    The listings are Delivery Route codes.

    I've not dealt much with macros. Nor have I dealt much with Excel.
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    So qryMapPointExportSpecial uses
    Me.cboRoutes.Value as a parameter to return the right records for each iteration.

    The IF test restricts the macro to cases where the query returns records. Are there perhaps some empty records in there?

    Can you post a cut down version to have a look at?
    Regards
    John



  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts
    John

    Just wanted to let you and others know, the addition did in fact work.

    When I was making the cut down version on the database I had to make some adjustments to make the process work. When I ran a test it worked perfect.

    Thank you much for your time, and the hard nudge to the head.
    threecrow

    Don't make excuses. It's what you do, not why you didn't.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Glad you sorted it, and let us know.
    Regards
    John



Posting Permissions

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