Update links only if file exists

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greg H. - 15 May 2008 16:35 GMT

I have built a Year to date report for my department that I send out on a
monthly basis. The main file pulls data from other documents to compile
everything. Because I know the name of every file it will pull from (its not
hard with you watched Sesame Street and learned your months) I set up all the
equations in the future months to already pull from those files. Problem is
when I open the document and select update links, it errors out once I come
to a file it can not find. Is there a way for it to skip over a file if it
does not exists and continue to look for the other files, updated those that
are there?

Reply to this Message

RB Smissaert - 15 May 2008 22:48 GMT

Use a simple function like this:

Function bFileExists(ByVal sFile As String) As Boolean

Dim lAttr As Long

On Error Resume Next
lAttr = GetAttr(sFile)
bFileExists = (Err.Number = 0) And ((lAttr And vbDirectory) = 0)
On Error GoTo 0

End Function

Use it like this:

If bFileExists(yourfilepath) then
'do whatever code needs doing here
Else
'Skip the file
End If

RBS

>I have built a Year to date report for my department that I send out on a
> monthly basis. The main file pulls data from other documents to compile
[quoted text clipped - 11 lines]
> that
> are there?

Reply to this Message