3305 Main Street Ste 019 Vancouver, WA 98663

Why do I receive an error about zzUpdateOCLink when opening a workbook?

Symptom

When opening an Excel workbook, the following message is displayed:

Microsoft Excel
(i) An unexpected error occurred in zzUpdateOCLink.
Error 1004
You cannot use this command on a protected sheet. To use this command, you must first unprotect the sheet (Review tab, Changes group, Unprotect Sheet button). You may be prompted for a password.
<OK>

Background

When an Excel workbook contains Office Connector functions (such as TSLookup, TSSum, etc), Excel adds a link to the path of the Office Connector add-in (OfficeConnector.xla) to the workbook. For example, this path may be "C:Program FilesEvent 1Office ConnectorOfficeConnector.xla". When opening the same workbook on another computer and the location of the OfficeConnector.xla file is different, Office Connector automatically updates the link information to identify the correct path. For example, another user's Office Connector add-in might be located at "C:Program Files (x86)Event 1Office ConnectorOfficeConnector.xla". Normally the update to this link occurs automatically and silently.

Cause

If a worksheet containing an Office Connector function is protected, the link to the OfficeConnector.xla file cannot be automatically updated. This will result in the above error message.

Resolution
  • Option 1 - Un-protect the worksheets containing Office Connector worksheet functions.
  • Option 2 - Unload the Office Connector add-in, move the OfficeConnector.xla file to a location that is consistent for all workstations (e.g., "C:Program Files (x86)Event 1Office Connector" on all machines), and then reload the Office Connector add-in by browsing to its new location. Note that relocating this file will require you to physically re-copy this file after each update to Office Connector.
See Also - How do I unload and load the Office Connector add-in? https://event1software.com/oc/how-do-i-unload-and-load-the-office-connector-add-in/