#REF,ISERROR, File Not Found question.

[Guest]

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.

 

[eluehmann]

Try putting

application.displayalerts=false

in the ThisWorkbook sheet in Object in VB.

Not sure if it will work or not... I can't test... but it is worth a shot

 

[Dave Peterson]

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

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]

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.

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

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.

 

[Guest]

That looks like it should work, according to what documentation I could find.
I put it by itself in a macro under 'ThisWorkBook' , but it did not supress
the
'File Not Found' box. Also tried using 'Open' and App_WorkBookOpen() to
activate it. No luck, I can't even tell if the macro runs when I open the
workbook.
Do you know of something easy I can put in the macro to tell if it is even
activating?

Thanks for the help! The Post from Dave Peterson suggests creating 365
blank files, I guess I might end up with that, but I hate that solution -
there should be a neater way to fix this.
Allen

Try putting

application.displayalerts=false

in the ThisWorkbook sheet in Object in VB.

Not sure if it will work or not... I can't test... but it is worth a shot

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.

 

[Guest]

I might end up creating the 365 blank files as you suggested. I tried the
other suggestion, making a macro with
'application.displayalerts=false' in it, and I can't get it to work. It
seems like there
should be a neater solution than having all those blank files out there.
Thanks for the help,
Al

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.

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

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]

I'm not sure if there are neater solutions, but there are always alternatives.

Maybe you could have a macro populate the formulas--but only if they exist.

Maybe you could use a User defined function that verifies the existence of the
workbook first.

Option Explicit
Function FileExists(myFileName) As Boolean

Dim testStr As String

FileExists = False

On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0

FileExists = CBool(testStr <> "")

End Function

Then you could use it in a worksheet formula like:

=IF(fileexists("C:\My Documents\excel\book4.xls"),
'C:\My Documents\excel\[book4.xls]Sheet1'!$A$1,"")
(all one cell)

Harlan Grove wrote a function called Pull that allows you to get values from
closed workbooks when the reference is wrapped in =Indirect().

You may want to look to see if it's useful for you.

http://google.co.uk/[email protected]

You could even put the portions of the filename that don't change in a couple of
cells.
and the portion that varies in another cell and build the reference inside that
into your formula.

I might end up creating the 365 blank files as you suggested. I tried the
other suggestion, making a macro with
'application.displayalerts=false' in it, and I can't get it to work. It
seems like there
should be a neater solution than having all those blank files out there.
Thanks for the help,
Al

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.

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.

 

[Guest]

Ok, now it looks like the file references are checked BEFORE any macro in
'ThisWorkbook' is executed. I tried putting in a command to make the workbook
expand to full size, and that happened after the 'File not found' boxs
started popping up.
I think you might have a good method, Dave. Someone at work also suggested
using the 'TODAY' function, and using IF to test - something like:
IF (ThisCell links to a date LESS THAN TODAY(), then value = link to other
spreadsheet, ELSE " "). I think that might work also. I'll try both
methods, and see which one is easier... Thanks!

I'm not sure if there are neater solutions, but there are always alternatives.

Maybe you could have a macro populate the formulas--but only if they exist.

Maybe you could use a User defined function that verifies the existence of the
workbook first.

Option Explicit
Function FileExists(myFileName) As Boolean

Dim testStr As String

FileExists = False

On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0

FileExists = CBool(testStr <> "")

End Function

Then you could use it in a worksheet formula like:

=IF(fileexists("C:\My Documents\excel\book4.xls"),
'C:\My Documents\excel\[book4.xls]Sheet1'!$A$1,"")
(all one cell)

Harlan Grove wrote a function called Pull that allows you to get values from
closed workbooks when the reference is wrapped in =Indirect().

You may want to look to see if it's useful for you.

http://google.co.uk/[email protected]

You could even put the portions of the filename that don't change in a couple of
cells.
and the portion that varies in another cell and build the reference inside that
into your formula.

I might end up creating the 365 blank files as you suggested. I tried the
other suggestion, making a macro with
'application.displayalerts=false' in it, and I can't get it to work. It
seems like there
should be a neater solution than having all those blank files out there.
Thanks for the help,
Al

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.