Export to Excel Question

Topic

New Reply

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:"

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

Glad you sorted it, and let us know.

Reply | Quote