excel: if FileExists, if a given value is x in the file, then pull the data from a given cell

Question

I have a function which checks if a file exists, if the file exists then it checks for a specific value in a specific cell, if thats true then it pulls the value from a given cell.

Problem is, a dialouge box keeps appearing when I run this code. I have inserted

Application.DisplayAlerts = False

into my FileExists function but I still get the dialogue box, any idea what Im doing wrong?

my function:

Function FileExists(FilePath As String) As Boolean

   Application.DisplayAlerts = False

'Step 1: Declare your variables.
    Dim FileName As String

'Step 2: Use the Dir function to get the file name
    FileName = Dir(FilePath)

'Step 3:  If file exists, return True else False
    If FileName <> "" Then FileExists = True _
    Else: FileExists = False

End Function

Sub FileExits()

End Sub

What Im inserting to the formula box:

=IF(FileExists("path\filename.xls"),IF('path\[filename.xls]Sheet'!D$13="16m8", 'path\[filename.xls]Sheet'!D40, ""),"false")

continued from here: https://stackoverflow.com/questions/39452159/excel-pull-data-from-one-excel-file-to-another-if-the-file-exists

directory/path to file. But the file exists, so Im not sure why it throws that up. — Boosted_d16, Sep 12 '16 at 15:58
that sucks! so I have to retrieve the values via vba macro? or is there another solution? — Boosted_d16, Sep 12 '16 at 16:12
If the file exists, check the spelling of the sheet name, is it really just `"Sheet"`, or should it be `"Sheet1"` (or something else)? — David Zemens, Sep 12 '16 at 16:55
Also see my suggestion below, I think this would be a better solution anyways. The formula is a bit easier to write & comprehend, and it makes use of Excel built-in function for retrieving value from closed workbook. — David Zemens, Sep 12 '16 at 17:29

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

I was slightly mistaken in my comment on the OP. Yes, you can retrieve values this way (kind of) but the dialog you're seeing is what happens if the file doesn't exist, because Excel's boolean logic doesn't short circuit.

What this means is that if the named file doesn't exist, Excel still has to evaluate the full expression, and it can't evaluate this part if the named file doesn't exist.

IF('path\[filename.xls]Sheet'!D$13="16m8", 'path\[filename.xls]Sheet'!D40, "")

As @pnuts mentions above, when Application.DisplayAlerts = False,

[Excel] will resort to default options where these exist (eg without saving for Close) but there is no default option for 'I can't find the file you told me to use'

NOTE If the named file does exist, you might still get the dialog box if the specified Sheet names don't exist.

Alternative solution:

Since you're already using a custom function FileExists, just use something like this (modified slightly from Sid's answer here) to get values from closed workbooks:

Function GetVal(path$, shtName$, cellRef$)

Dim exists As Boolean, ret
Dim fileName$, directory$, sht$, addr$

    exists = Dir(Trim(path)) <> vbNullString

    If Not exists Then GoTo EarlyExit

    'Else, if the file exists, get the values
    path = Replace(path, "/", "\")
    'Get the query substrings:
    fileName = Dir(path)
    directory = Left$(path, Len(path) - Len(fileName))

    'Get the address of the cell, R1C1 style
    addr = Range(cellRef).Address(True, True, -4150)
    'Build the query string in the ExecuteExcel4Macro function
    ret = ExecuteExcel4Macro("'" & directory & "[" & fileName & "]" & shtName & "'!" & addr)

    GetVal = ret

EarlyExit:
    ret = "File or sheetname doesn't exist!"  '## Modify as needed
End Function

Then, modify your logic, so that instead of:

=IF(FileExists("path\filename.xls"),IF('path\[filename.xls]Sheet'!D$13="16m8", 'path\[filename.xls]Sheet'!D40, ""),"false")

You do this:

=IF(GetVal("path\filename.xls", "Sheet1", "D$13")="16m8", GetVal("path\filename.xls", "Sheet1", "D40"))

The above function uses same logic as your FileExists, and does ensure the file exists before handing the query to ExecuteExcel4Macro built-in function.

excel: if FileExists, if a given value is x in the file, then pull the data from a given cell

1 Answers1

Alternative solution: