Workbook links, error handling, VBA

Hi All,

I have a workbook which contain links to other workbooks, these links refer to files which are not yet created as they produced daily, so the formulas I have work great with closed workbooks, however becaues the future files do not exist yet when I update workbook links in excel it will open a popup window to search for the file due it being not found.

So I am wondering if there is a way in VBA I am able to create an error handler, even if it means I have to set up each file path as a string to refer to (which I would be prepared to do if abosolutely necesary)

Just to clarify I'm not looking to disable the message about updating workbook links. Wanting to disable the "File not found" pop up box that asks me search for the file.

Thanks!

Ok that sounds good...

The formula i'm using will look something like this

=SUMPRODUCT(([Volume (01JAN15).xlsx]Sheet1!$A$2:$A$16="R6")*([Volume (01JAN15.xlsx]Sheet1!$A$2:$A$16="R6")*([Volume (01JAN15).xlsx]Sheet1!$C$2:$C$16+[Volume (01JAN15).xlsx]Sheet1!$D$2:$D$16+[Volume (01JAN15).xlsx]Sheet1!$E$2:$E$16))

So i'm thinking ill have to build a sheet to physically select what date I want to feed, as I can't really have the macro trying 365 days worth of files everytime it runs, can you give me any thoughts of what code to use for the testing for the existance of the file and the best way to achieve what I'm looking to do? I'm not to bad with VBA but a little bit out my ability at the moment.

If some example workbooks would help let me know.

Thanks in advance

Well, it will be a closed workbook link which is why I used SUMPRODUCT, avoiding opening of files where possible as the network & machines the excel file is run on is quite slow, but I don't have a problem opening the file via VB in order to update it if thats necessary, however... I was planning on inputting the formulas into the sheets in advance, hence the reason why I needed to validate whether the file existed as it will bring up automatically a prompt to search for the file which is what I need to avoid. Although I suppose if VB input the formula each day that could work also, but that would require VB to change the position of where the formula is placed for each day, not sure how easy that is.

My formulas will be positioned in each column starting with B in row 7 and they have a date heading the row above.

Well I have used userforms for some of the work already. So I was thinking perhaps have a userform in each sheet, that requests the date to be updated, ideally the field with the date in could default to today's date. Although the file that will contain the data I'm looking to link will always be 1 day in arrear, so if we we're updating today the file would be Volumes (15NOV14).xlsx (DDMMMYY) format

Which preferably Id rather have it refer to the current day's date and simply -1 on DD, hope that makes sense

Thanks for the reply shg, been out of the office for a few days, I will have a play with the code you provided and let you know how its working. I'm assuming then you would simply disable workbook updates and use this instead. Keep you posted in a few days thanks!