Symptom
When a workbook that contains Office Connector functions is saved and later the workbook is re-opened, the cells containing Office Connector functions show zeros or blanks.
Cause
Each Office Connector function represents a link to your Sage 300 Construction and Real Estate database. When Excel evaluates an Office Connector function, Office Connector obtains the value from the Sage 300 Construction and Real Estate database and returns it as the result of the function (displayed in the cell in your worksheet). Values returned by Office Connector functions are held in memory so that subsequent requests for the same information don’t incur the time penalty of querying the database again. When a workbook is closed, the values held in memory are discarded. Later when the workbook is re-opened, the Office Connector functions will only return a value if a connection is made to your Sage 300 Construction and Real Estate database (by logging in). Current values are then queried from the database and displayed. If you cancel the login process or don’t have access to the database, the functions do not have a value to return (either from the database or from memory) and so they will return zeros or blanks.
Resolution
If you need a workbook to show the last values read by Office Connector and have those values remain until you elect to refresh data regardless of whether you have a database connection, there are two possible methods to accomplish this.
1. Cached Data
Office Connector includes a feature that will allow the function values that are being held in memory to be stored with the workbook. This way, when the workbook is opened again at a later time, Office Connector does not require a connection to the database. As functions are evaluated, their values will be retrieved from the values stored with the workbook. This effectively allows the workbook to represent a “snap-shot” of the data as of the last time that data was refreshed. When you click the refresh button, the cached information is discarded and current values are read from the database.
To make use of this feature open the Edit Preferences window from the Office Connector toolbar and select the Read and Write option under Persistent caching. Note that since the workbook can be opened and the last read values can be displayed without a connection to your database, this method does not utilize a concurrency. A concurrency would only become in use when you establish a connection to the database in order to get current values.
For more information about this method, see the Preference Window – This Workbook topic in online help.
2. Save As Value
This method results in a copy of the workbook being saved without any of the Office Connector functions or queries. Cells that formerly contained Office Connector functions will be replaced with static values. This is effective if you wish to provide a copy of the workbook to a user who does not have Office Connector installed. Note that the newly saved copy of the workbook cannot be refreshed with current data because it no longer contains any links to the Sage 300 Construction and Real Estate database.
To save a workbook in this way, click the Save As Values button on the Office Connector toolbar. When this option is selected, you will be able to save a copy of the workbook under a new name and preserve the original workbook as well.
For more information about saving workbooks as values, see the Save As Values topic in online help.