Detect if Reference Workbook is Open

[Roderick_E]

I've seen VBA solutions to detect if a reference workbook is open, but is there a formulaic solution without using a UDF?
Thanks

 

[Ron Coderre]

You might be able to use something like this:

If a workbook is linked...AND...is currently open
the linking formula has this structure:

D2: ='[FileName.xlsx]Sheet1'!A1

BUT...if the file is NOT open
the linking formula has this structure:

D2: ='C:\FolderName\[FileName.xlsx]Sheet1'!A1

So, you might be able to use the FORMULATEXT function to return the formula in the cell and maybe search for a specific pattern:

E2: =IF(ISERR(SEARCH(":\",FORMULATEXT(D2))),"Open","Closed")

Note: The search term is COLON BACKSLASH....The website may corrupt that.

Is that something you can work with?

 

[Roderick_E]

You might be able to use something like this:

If a workbook is linked...AND...is currently open
the linking formula has this structure:

D2: ='[FileName.xlsx]Sheet1'!A1

BUT...if the file is NOT open
the linking formula has this structure:

D2: ='C:\FolderName\[FileName.xlsx]Sheet1'!A1

So, you might be able to use the FORMULATEXT function to return the formula in the cell and maybe search for a specific pattern:

E2: =IF(ISERR(SEARCH(":\",FORMULATEXT(D2))),"Open","Closed")

Note: The search term is COLON BACKSLASH....The website may corrupt that.

Is that something you can work with?

Going to test this and will report back thanks

 

[Roderick_E]

You might be able to use something like this:

If a workbook is linked...AND...is currently open
the linking formula has this structure:

D2: ='[FileName.xlsx]Sheet1'!A1

BUT...if the file is NOT open
the linking formula has this structure:

D2: ='C:\FolderName\[FileName.xlsx]Sheet1'!A1

So, you might be able to use the FORMULATEXT function to return the formula in the cell and maybe search for a specific pattern:

E2: =IF(ISERR(SEARCH(":\",FORMULATEXT(D2))),"Open","Closed")

Note: The search term is COLON BACKSLASH....The website may corrupt that.

Is that something you can work with?

Ok I can work with this thanks. Testing shows that I can't simply put the link into the Formulatext part, I have to reference another cell, but it will still work.