On error...

[ph8]

Is there a way for Excel to skip reporting an error if a function doesn't work? More specifically I once saw the use of the command "on error resume next" which as it stands I have very little clue as to what it does.

Regardless...

Please Login or Register  to view this content.

This is my code. I receive an "Method 'UpdateLink' of object '_Workbook' failed" error when the source and the destination sheets are both open and the "refresh" button is clicked. This will very rarely happen when the users are actually using the files, but in the rare case that it does I'd rather they be able to still update links without receiving the error.

Is there a way to either ignore the error, or a different way to update the links which would avoid the error completely?

THanks in advanced.

[Bob Phillips]

On Error Resume Next has the effect of ignoring errors and continuing on to
the next command. It can be very useful, but it has to be used with caution,
resetting as a minimum with On Error Goto 0, at some point, otherwise all
errors will get ignored, even those you don't.

Which error do you want to ignore, and why?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ph8" <[email protected]> wrote in message
news:[email protected]...
>
> Is there a way for Excel to skip reporting an error if a function
> doesn't work? More specifically I once saw the use of the command "on
> error resume next" which as it stands I have very little clue as to
> what it does.
>
> Regardless...
>
>
> Code:
> --------------------
>
> Private Sub Refresh_Click()
> Dim i As Integer
> ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
>
> For i = 10 To 39
> addComment (Cells(i, "B"))
> Next i
> End Sub
>
> --------------------
>
>
> This is my code. I receive an *"Method 'UpdateLink' of object
> '_Workbook' failed"* error when the source and the destination sheets
> are both open and the "refresh" button is clicked. This will very
> rarely happen when the users are actually using the files, but in the
> rare case that it does I'd rather they be able to still update links
> without receiving the error.
>
> Is there a way to either ignore the error, or a different way to update
> the links which would avoid the error completely?
>
> THanks in advanced.
>
>
> --
> ph8
> ------------------------------------------------------------------------
> ph8's Profile:
http://www.excelforum.com/member.php...o&userid=19871
> View this thread: http://www.excelforum.com/showthread...hreadid=517217
>

[ph8]

I can understand how ignoring errors is probably not the best habit to form when coding. So I guess maybe if there was a way to avoid the error altogether that would be the best bet.

I get the error when both the source and destinatino spreadsheets are open on the same computer and I try to update the links with that particular line of VBA code which updates the link ( ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources). The error I get is "Method 'UpdateLink' of object '_Workbook' failed."

That code is on the destination spreadsheet. WHere formula links exist in the cells. But when I have both this destination spreadsheet and source spreadsheet open (on the same computer). When the source file is opened on a different computer (ala network) or when its closed the function performs flawlessly.

[Bob Phillips]

In that instance, try just wrapping it with error statements as I showed

On Error Resume Next
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
On Error Goto 0

This should mitigate against any further problems,. The other option is to
test whether the file is open, if so, don't issue the update.
--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ph8" <[email protected]> wrote in message
news:[email protected]...
>
> I can understand how ignoring errors is probably not the best habit to
> form when coding. So I guess maybe if there was a way to avoid the
> error altogether that would be the best bet.
>
> I get the error when both the source and destinatino spreadsheets are
> open on the same computer and I try to update the links with that
> particular line of VBA code which updates the link (
> ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources). The error
> I get is "-Method 'UpdateLink' of object '_Workbook' failed-."
>
> That code is on the destination spreadsheet. WHere formula links exist
> in the cells. But when I have both this destination spreadsheet and
> source spreadsheet open (on the same computer). When the source file
> is opened on a different computer (ala network) or when its closed the
> function performs flawlessly.
>
>
> --
> ph8
> ------------------------------------------------------------------------
> ph8's Profile:
http://www.excelforum.com/member.php...o&userid=19871
> View this thread: http://www.excelforum.com/showthread...hreadid=517217
>

[ph8]

Thanks for your help, Bob!

One last question. Just to confirm. That code will ONLY skip the error for that particular line of code? It won't disable error reporting for other errors I may get for various reasons?

[Bob Phillips]

No, the On Error Goto 0 disables error handling within the code, so VBA
takes back control.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ph8" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks for your help, Bob!
>
> One last question. Just to confirm. That code will ONLY skip the error
> for that particular line of code? It won't disable error reporting for
> other errors I may get for various reasons?
>
>
> --
> ph8
> ------------------------------------------------------------------------
> ph8's Profile:
http://www.excelforum.com/member.php...o&userid=19871
> View this thread: http://www.excelforum.com/showthread...hreadid=517217
>