0

I use VBA to acquire data from around 1000 closed workbooks. It works if the worksheet exists in the targeted workbook.

If the worksheet doesn't exist, instead of throwing an error, a pop-up asks me to select another worksheet. The issue being, 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 parameter to turn off.

The code (largely taken from the internet), and the pop-up.

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

enter image description here

Community
  • 1
  • 1
Martin
  • 3
  • 1
  • Try this instead - https://stackoverflow.com/a/9261915/478884 It will return an error value if the full path to the cell is not found. – Tim Williams Aug 26 '22 at 06:17

1 Answers1

0

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
JohnM
  • 2,422
  • 2
  • 8
  • 20
  • Thank you very much, JohnM! Actually I have to acquire arrays in the files I'm targeting, so the code doesn't work directly. However, from what you have given, I have created a function that returns True/False if the tab exists or not, and launches the 'full' acquisition depending on the result. So for me the case is closed. – Martin Aug 27 '22 at 06:56