CODEWITHSUNDEEP
excelvba
Niall
Niall

Reputation: 1621

Add Excel formula containing a file path that may not exist

I need to add a formula to a range of cells which performs a VLOOKUP against a file which at that point in time may not exist. I cannot find a way to do this without it prompting me to select the file when it doesn't. When I hit escape it just defaults to a #N/A error, which is precisely the functionality that I want, only without having to hit the Escape button repeatedly.

As an example, my formula is:

=VLOOKUP(B1,'R:\Folder Path\[File_that_wont_exist_until_later.xlsx]Data'!$A:$D,4,FALSE)

So is there a way of preventing the dialog being displayed? I've tried setting DisplayAlerts to false but the dialog still appears.

Upvotes: 1

Views: 292

Answers (1)

Vasim
Vasim

Reputation: 3143

Try

Application.DisplayAlerts = False
=VLOOKUP(B1,'R:\Folder Path\[File_that_wont_exist_until_later.xlsx]Data'!$A:$D,4,FALSE)
Application.DisplayAlerts = True

Adding this will remove the prompt box displayed.

Upvotes: 1

Related Questions