OC – Save As Values

The Save As Values command creates a copy of your spreadsheet with all functions and queries specific to Office Connector removed.  The resulting file is much smaller, does not require the Office Connector software, loads much faster and cannot be refreshed.

The following actions are taken when you click Save As Values:

  1. You are prompted for a new file name with the default being the current file name plus the word "Values" in parenthesis.
  2. All queries based on the Event 1 data source are removed, but the data remains on the spreadsheet.
  3. All Office Connector workbook functions are replaced by their values.
  4. The workbook is saved under the new name.

Unattended Automation

The Save As Values command can be recorded and called from a VBA macro; just like when invoked from the toolbar, the recorded macro uses the Save As dialog to prompt for the new file name.  It is possible to suppress the Save As dialog box by editing your macro to pass a file name to the OCSaveAsValues command.

NOTE - You must make sure that the file does not already exist or Excel will prompt to replace the existing file.  Also, for users of Excel 2007 and above, you must choose an appropriate file extension to avoid additional confirmation prompts (for example, using the XLAM file extension because your workbook contains macros).

Example

Sub SaveAsValues()

Call Application.Run("OCSaveAsValues", "C:MyFileName.xls")

End Sub