Ps. I only went through Jan 31 for my tests.

For iCtr = DateSerial(Year(Date), 1, 1) To DateSerial(Year(Date), 12, 31)

if you need all 12 months.



Dave Peterson wrote:

I can't think of a way.

If I were doing this, I think I'd build the formulas as text:

$$$=if(iserror(.....

then when the workbook for that month became available, I'd do an edit replace
to change $$$= to = (to convert the text to formulas).

Or maybe even build dummy workbooks that could serve as placeholders. Just
populate them with errors.

Option Explicit
Sub BuildDummyWorkbooks()
Dim testStr As String
Dim iCtr As Long
Dim myFolder As String
Dim newWks As Worksheet
Dim myFileName As String

myFolder = "c:\reports\daily_2005"
If Right(myFolder, 1) < "\" Then
myFolder = myFolder & "\"
End If

'check for folder
testStr = ""
On Error Resume Next
testStr = Dir(myFolder & "nul")
On Error GoTo 0

If testStr = "" Then
MsgBox "Please create the output folder"
Exit Sub
End If

'create a dummy worksheet in a new workbook
Set newWks = Workbooks.Add(1).Worksheets(1)
newWks.Range("a1:z999").Value = CVErr(xlErrRef)

For iCtr = DateSerial(Year(Date), 1, 1) To DateSerial(Year(Date), 1, 31)
myFileName = myFolder & "Daily_" & Format(iCtr, "mmmdd") & ".xls"
testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0

If testStr = "" Then
'not there
newWks.Parent.SaveAs Filename:=myFileName, _
FileFormat:=xlWorkbookNormal
End If
Next iCtr

newWks.Parent.Close savechanges:=False
End Sub

I filled A1:Z999 with errors. Adjust as necessary.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

FlaProcessEng wrote:

I have an annual summary sheet that links to 365 separate daily report
sheets. These daily sheets are being created automatically once per day. I
use the following formula for the February 1 2005 cell:

=IF(ISERROR('C:\reports\daily_2005\[DAILY_FEB01.xls]Report'!R20C16)=TRUE,"",'C:\reports\daily_2005\[DAILY_FEB01.xls]Report'!R20C16)

This works, but of course the rest of the files for 2005 don't exist yet.

What I want is to be able to open the annual spreadsheet up every day, and say
'YES' to the 'update links to other spreadsheets' question, and to NOT have
Excel tell me it couldn't find the rest of the 2005 daily files (the File not
Found dialog box).
Is there a way to do this or supress this dialog box!
Thank you.

--

Dave Peterson

--

Dave Peterson