G
graemeW
Hi folks
I'm refreshing loads of workbooks overnight in an automated trawl thru
a list of directories. Any message boxes popping up block the job
waiting for a manual reply.
I don't want to use "excel.displayAlerts = false",
because my desired approach is not to ignore alerts, but rather to
abandon work on this workbook, log an error and proceed to the next
workbook as soon as any alert pokes its head above the parapet.
Here's a code snippet....
The error handler writes a log message, and closes the workbook without
saving, proceeds to the next workbook. But it doesn't catch many
alerts, the most problematic being
"Do you want to replace the contents of the destination cells"
I'd appreciate any solution to this specific message,
or even better to the holy grail "see any message box coming and
abandon processing of this workbook" problem
Ta!
Graeme
I'm refreshing loads of workbooks overnight in an automated trawl thru
a list of directories. Any message boxes popping up block the job
waiting for a manual reply.
I don't want to use "excel.displayAlerts = false",
because my desired approach is not to ignore alerts, but rather to
abandon work on this workbook, log an error and proceed to the next
workbook as soon as any alert pokes its head above the parapet.
Here's a code snippet....
Code:
On Error GoTo err
' refresh data ranges and pivot tables
For Each ws In ActiveWorkbook.Worksheets
For Each qt In ws.QueryTables
qt.Refresh False
Next
For Each piv In ws.PivotTables
piv.RefreshTable
Next piv
Next wsThe error handler writes a log message, and closes the workbook without
saving, proceeds to the next workbook. But it doesn't catch many
alerts, the most problematic being
"Do you want to replace the contents of the destination cells"
I'd appreciate any solution to this specific message,
or even better to the holy grail "see any message box coming and
abandon processing of this workbook" problem
Ta!
Graeme