For the first time in years I couldn't find anyone that had the same issue as me...

I use VBA to acquire data from around 1000 closed workbooks. It works fine as long as the worksheet I'm looking for exists in the targeted workbook. However, if the worksheet doesn't exist, instead of throwing an error, a pop-up appears, asking me to select another worksheet. The issue being, of course, that it stops the macro.

I would like to disable this functionality, in order to be able to manage the error. I'm pretty sure it's a simple parameter to turn off, but despite my best efforts, I couldn't find which one.

Below is the code (largely taken from internet), and the related pop-up.

Thank you very much for the help!

Sub get_data_from_closed_file()
     Dim rgTarget As Range: Set rgTarget = ThisWorkbook.Sheets("tmp").Range("$A$1")
     Dim f As String: f = "=' FILE_PATH [FILE_NAME] SHEET_NAME'!$A$1"

    rgTarget.FormulaArray = f
    Dim data: data = rgTarget.Value                                                               
End Sub

CodePudding user response：

I can't help you with the setting to disable the functionality, but an alternative is to use an 'Excel 4.0 macro', such as

Dim f As String: f = "' FILE_PATH [FILE_NAME] SHEET_NAME'!R1C1"
rgTarget.FormulaArray = ExecuteExcel4Macro(f)

This will retrieve the value from a single cell of an external Workbook, but note:

• The cell reference must be in R1C1 notation, not A1 notation
• It retrieves a fixed value, it does not create a formula
• If the specific Worksheet does not exist, the value returned will be an error value ("#REF!")
• You should ensure the 'source' Workbooks are closed ... if a Workbook is open AND the Worksheet name is invalid, you will get an error