Excel Macro - trapping Error Message when Opening Workbooks

[ToddInLondon]

I have an Excel macro that opens workbooks in a folder/sub-folder structure and processes each worksheet via Copy/Paste SpecialValue and re-saves the workbook. As the macro processes hundreds/thousands of workbooks, there have been a few workbooks that have been "skipped". One reason for the skipping that I have found is if the workbook has an initial message box that pops up during the open active workbook command. For example, if the workbook has a circular reference msg OR bad links msg, etc., a message box with an "Update" or "OK" button is displayed.

For now, I would like to write to an error log, the name/path of the workbook, an error message id of the pop-up box, and a general message of the error. My initial goal to get thru all the workbooks in the folder structure and then go back and individually review the workbooks in the error log for potential issues.

Any idea how I can trap out the information regarding pop-up boxes when a workbook is initially opened.

Thanks, Todd

 

[chicagocomputerclasses]

What's your current macro?

 

[ToddInLondon]

here's the core of the macro - open the workbook, refresh, copy, paste special values, save, and close. I'm thinking that after the open, I would like to monitor for any popup message that may exists (i.e. Circular Reference, bad links, etc.). Given the endless possibilities of potential errors, for now I think I just want a listing of the workbooks with messages when opened.

Todd
-----------------------------------------

Private Sub DoOneFile(sFullFileName As String)

'Define variables
Dim Snr As Integer

'Open the workbook
Workbooks.Open sFullFileName
ActiveWorkbook.RefreshAll

'For each worksheet, process
For Snr = 1 To ActiveWorkbook.Sheets.Count
'Activate the worksheet
Sheets(Snr).Activate

'Select the entire worksheet
Cells.Select

'Copy the data on the worksheet
Selection.Copy

'Use Paste Special Values to remove all calc fields and HPVAL command
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'Set the cursor to the top, left hand corner
ActiveCell.Range("A1").Select

'Loop to next worksheet
Next Snr

'After all worksheets have processed, set Sheet1 as current, save/close the workbook
Sheets(1).Activate
ActiveWorkbook.Save
ActiveWorkbook.Close True

End Sub