I have a series of reports set up to be opened, refreshed, printed, saved and closed via VBA code.
My problem is that when any of these reports has no data to fetch a dialog box appears telling me so and the user would have to press Enter in order for the code to continue running.
Can anyone tell me how to suppress the dialog box telling me that there is “No Data to Fetch”.
I did a search using “no data to fetch” and found more than 6000 matches - then tried looking at a few pages of those without luck. Just thought I would post to see if anyone knew the answer off the top of their head.
Did a search using “suppress” and found an answer in the FAQS (should have thought of that).
But using the answer supplied there of adding “Application.Interactive=False” at the beginning and “Application.Interactive=True” at the end almost fixes my problem but not quite.
All the reports I have are governed by a With block like:
Application.Documents.Open ("path\ReportName.rep")
With ActiveDocument
.Refresh
.PrintOut ("\\named_Network_Printer")
.Save
.Close
End With
and the difficulty I have now is that having suppressed the “no data to fetch” dialog box the report with no data now wants to go and print even though there is no data. What I really want to do is avoid sending a blank report and only print if data is fetched.
Have an IF condition which checks for the No Of Rows Retrieved by the Report DataProvider and only if it is greater than 0 execute the print statement…
Something like this…
Application.Documents.Open ("path\ReportName.rep")
With ActiveDocument
.Refresh
If .DataProviders.Item(1).NbRowsFetched > 0 Then
.PrintOut ("\\named_Network_Printer")
End If
.Save
.Close
End With