Cancel export to excel after it has started

[Guest]

Access 2003, app saved in 200 format, Windows XP, Office 2003

I am exporting reports to excel using

DoCmd.OutputTo acOutputQuery, strSQL, acSpreadsheetTypeExcel9, strpath

After the query is exported to excel, I run some code to format the report.
After user has chosen file name and place to save the report from common
save dialog, I pop up a form asking user to wait.

This pop up form has a cancel button, I have tried a couple of different
ways to make this cancel button work

1. I opened a hidden form and in the main code to export and format the
report I checked to see if the hidden form was open and if it was I exitted
the sub and cleaned up
2. after 1. didn’t work because it took too long to open the hidden form, I
set a boolean variable to cancel after the cancel button was clicked on the
pop up form. In the main code to export and format the report I checked to
see if the cancel variable was set to true and if it was I exitted the sub
and cleaned up

Neither of the above worked
I noticed that while access is getting the data and opening excel, I can’t
click the cancel button on the pop up form, because access is showing its
hourglass, (I haven’t set hourglass to true in any code).
By the time I can get the cancel button to respond to a click, it is too
late and
Application.followhyperlink is opening the report in Excel.

Is there any known way to cancel an export to excel after it has started?

Thanks

 

[Guest]

try hitting the {Escape key} or {Break key}

 

[John Nurick]

Hi Nugimac,

The general idea is to have the Cancel button set a global boolean
variable (or at least one that is visible both to the form and to the
code that's doing the exporting. Let's call it gblCancelExport.

In the Cancel button's Click event procedure, just do something like
gblCancelExport = True

In the export procedure,

1) at the very beginning, set gblCancelExport = False. This is to reset
it in case it's been left True.

2) at key points in the code, put something like this:
DoEvents 'this allows other things to happen - such as
'processing the Cancel button's Click event
If gblCancelExport Then
'the button has been clicked
'tidy up and exit
...
End If

The classic use of this is inside a loop that is called many many times.
As far as I know it's not possible to cancel DoCmd.OutputTo part way
through, howevever. You'll have to wait until a point where you can
insert a DoEvents and check the "flag" variable.