IF EXIST for a workbook

[mickj]

Hi, I have developed some fairly complex spreadsheet workbooks for my employer that monitor buying patterns for each day of each week.
There is a workbook for each week with references for each day and each workbook is linked to the previous week for live reference.
It is taking a while to develop each sheet, with the change of formulae etc. but my employer now requires a "Master" sheet that will draw in certain figures from each week, to offer an annual overview wih additions and percentage calculations etc.

The problem I have is that the "master" sheet will need to do calculations from data linked from 52 different workbooks. Not a problem except, as yet, some of them don't exist until they have been developed. In the meanwhile the figures are needed up to an including the current week.

Is there a formula that will test for the existence of a particular workbook but ignore errors and continue with the formula if the named workbook doesn't currently exist?
Difficult to explain the conceopt but I hope this makes sense.

 

[Sektor]

Function WorkbookExists(FilePath As String) As Boolean
    WorkbookExists = Dir(FilePath) <> vbNullString
End Function

 

[mickj]

Many many thanks for the quick response.
Not too familar with "Functions". Where would I put this code.? Would this be in the VB Editor for the worksheet?

What exactly does this code do? Does it just supress the error or is it doing something a bit cleverer?

Also do I need to add anything new into the formula that links to the other workbooks or is this part of the process that runs invisibly in the background?
Many thanks

 

[Sektor]

This function returns Boolean. You pass it full path to workbook, and it returns TRUE if it exists, otherwise it returns FALSE. You can use it in either VBA or worksheet.

 

[mickj]

Many thanks once again.
I'll try it out