Tips and Techniques for Using Improv Files in 123
The procedure for exporting an Improv file to a 123 file includes a number of steps. Below is an overview of this process:
A. Determine which 123 file format to use for the export.
B. Prepare the Improv file for export.
C. Choose File Save As, and select the desired 123 file format.
D. Select the appropriate options in the Export Worksheet dialog box.
E. Open the exported file in 123 to inspect and modify the results.
Each step is described in greater detail below.
A. Choosing the Export File Format:
Improv files can be exported in different 123 file formats (as well as ASCII text and Microsoft Excel file formats). Below is a table that lists each supported export file format and the reasons a customer would choose to export an Improv fil
e in a particular format.
NOTE: For information on exporting Improv files in an ASCII text file format, reference the document entitled: "Tips and Techniques for Exporting Improv Files to ASCII Text."
B. Preparing the Improv File for Export:
Below are the recommended steps for preparing an Improv worksheet for export to a 123 file format.
NOTE: Some Improv files may require additional preparation for export. Customers should also review the section below entitled Additional Export Considerations.
1. Open all Improv worksheets and views to be exported.
a. Open the Model Browser (Worksheet, Browser)
b. Double click on the icons for each worksheet and view to open.
2. Change group summary functions/formulas to regular functions.
3. Replace intersheet formulas and DDE formulas with the data.
a. Select the range of cells that is calculated by an intersheet or DDE formula.
b. Choose Edit Copy.
c. Choose Edit Paste and click OK to overlap the calculated values.
d. Delete the intersheet and DDE formulas from the formula pane.
4. Decide whether page categories should become sheets or hierarchical row layouts.
a. Position category tiles accordingly.
5. Hide items and ranges that should not be exported.
6. Show any hidden items and ranges that should be exported.
7. Check for data overrides in otherwise calculated cells that affect the integrity of the results.
a. Use Edit, Clear, Data to display the calculated values.
8. Create a new view or sheet for desired data under these conditions:
- Only specific subsets or ranges need be exported.
- The hide option is not appropriate.
9. Enter Category names in cells to preserve them in the export.
10.Store Page Item names in cells using the CELLNAME function.
C. Exporting the Improv File in a 123 File Format:
Take the following steps to export (save) an Improv file in a 123 file format.
1. Choose File, Save As.
2. Under "Save File As Type", select one of the following: (123 WK3), (123 WK4), or (123 WK1).
3. Enter a file name and select OK.
4. Select the desired options in Worksheet Export dialog box. (The following section provides
information on the Export options in this dialog box.)
5. Click OK.
6. Retrieve the new file in 123 and make Post Export modifications if necessary.<:p<* >>
D. Selecting the Worksheet Export Options:
The Worksheet Export dialog box displays when a customer selects File Save As and then chooses one of the Worksheet Export file types (WK1, WK3, WK4, or XLS). The options chosen in the Worksheet Export dialog box determine the results of the
export.
Refer to the Additional Export Considerations (section B) for information on exporting Improv files that contain intersheet formulas.
Each option in the Worksheet Export dialog box is explained below:
Export: Determines which elements of the worksheet are exported.
- Cell contents: Exports the contents of cells.
- Formulas: Exports the formulas along with the values calculated by the formulas.
- Item names: Exports the Improv item names as spreadsheet labels.
Scope of Export: Determines which views are exported.
- Current view: Exports only the currently selected view.
- Open views: Exports all open views.
- Entire model: Exports the first view of each worksheet within the model
Intersheet Formulas: Determines how intersheet formulas references are exported.
- Treat as error: Terminates export upon the first encounter of an intersheet formula and
displays an alert box.
- Replace with @ERR: Replaces values calculated by intersheet formulas with @ERR.
- Replace with values: Exports the value only, not the intersheet formula.
Sheet Layout: Determines how Improv page categories are positioned in a row or column layout.
- Diagonal: Starts the beginning of each new page at the lower right corner or the
preceding page.
- Vertical: Starts the beginning of each new page below the preceding page.
- Horizontal: Starts the beginning of each new page to the right of the preceding page.
E. Post-Export Modifications:
Once the file has been exported, open it in 123 and review the worksheet. It may be necessary to make
some of the following modifications:
1. Check for formulas or calculations that did not export.
a. Values appear instead of formulas
b. @ERR
2. Reposition group summary labels.
3. Recreate charts. For a static copy of the Improv Presentation Page, take the following steps:
a. Open the file in Improv.
b. Go to the Presentation Page.
c. Choose Edit Select All.
d. Choose Edit Copy.
e. Switch to 123, and choose Edit Paste.
4. Create macros to perform actions that were done using LotusScript.
Note: Dialog boxes created using the Lotus Dialog Editor can be used in 123.
5. Re-enter headers and footers. (Select File, Page Setup)
6. Re-establish DDE links to the DDE source (server).
7. Format numeric values and font styles.
8. Re-center text labels or group name labels.
9. Add "page" names to sheets.
ADDITIONAL EXPORT CONSIDERATIONS:
A. Improv Features that Do Not Export to 123 File Formats:
Because of functionality differences between Improv and 123, certain features cannot be exported. Below is a list of Improv features that will not export to a 123 file format.
a. Groups and Group Summary Items
b. Presentations and Charts
c. Scripts
d. Headers/Footers
e. DDE Links
f. Hidden Items
g. Names of Categories
h. Names of Page Items
B. Exporting Linked Worksheets:
Customers have the following three options when exporting Improv files that contain intersheet (linked) formulas.
Option 1. Export Entire Contents of Improv Model:
To export the entire contents of an Improv file, select the Entire Model option for the Scope of Export in the Worksheet Export dialog box.
NOTE: This option exports only the first view in each worksheet. To export every view in every worksheet, refer to Option 2.
The results of exporting the entire model depends on which file format is selected:
Option 2. Export All Required Worksheets and Maintain Formula Links:
To export all the worksheets and maintain the formula links, first open all the views that contain linked worksheets. Then select the Open Views option for the Scope of Export in the Worksheet Export dialog box.
The results of exporting all Open Views depends on which file format is selected:
Option 3. Export Only the Current View:
Open the desired worksheet and view, and select the Current View option for the Scope of Export in the Worksheet Export dialog box.
The Intersheet Formulas setting selected on the Worksheet Export dialog box will determine how intersheet formulas are handled.
C. Spreadsheet File Format and Improv Layout Issues:
There are some inherent differences in the layout of Improv and 123 files. Below are some tips to ensure the exported Improv file displays with the desired format in 123.
1. 2D Improv Worksheet to 2D Spreadsheet
Format in Improv: A two category worksheet in Improv
Desired format in 123: A basic row and column spreadsheet
a. Choose any of the following file formats for export: WK1, WK3, WK4 or XLS.
b. Be sure to reposition any category located in the page layout area as either a row or a column.
2. 3D Improv Worksheet to 2D Spreadsheet
Format in Improv: A three category worksheet in Improv
Desired format in 123: A row and column oriented spreadsheet
a. Choose any of the following file formats for export: WK1, WK3, WK4, or XLS.
b. If Improv worksheet has a category positioned in the page layout position, and either a WK1
or XLS format is chosen for export, be sure to specify a Sheet Layout setting in the Worksheet
Export dialog box (or reposition all page categories in Improv to row or column layouts).
3. 3D Improv worksheet to Multi-sheet spreadsheet
Format in Improv: A three category worksheet in Improv
Desired format in 123: A multiple sheet spreadsheet file
a. Choose either the WK3 or WK4 file format for export.
b. To successfully create a new 123 spreadsheet that contains multiple sheets, in Improv:
Position any category that should be converted to sheets in the Improv page layout position.
Note: Page category item names are not translated into sheet names during an export. Either
rename the sheets of the exported spreadsheet in 123 or place the current page category item
name in a cell in Improv using the CELLNAME function.
Example: Item A1:Item B1=cellname("Category C")
4. Multidimensional Improv Worksheet to 2D Spreadsheet
Format in Improv: A multiple category worksheet in Improv
Desired format in 123: A row and column oriented spreadsheet
a. Choose any of the following file formats for export: WK1, WK3, WK4 or XLS.
b. For WK1 and XLS file formats, categories positioned as pages will define new ranges in the
exported spreadsheet. Be sure to select the Sheet Layout setting of the Worksheet Export
dialog that best suits the spreadsheet's layout.<:p<* >>
Note: Page category item names are not displayed anywhere in the
ranges. To workaround this,
place the current item names for all page categories in a cell using the CELLNAME function.
Example: Item A1:Item B1 = cellname("Category C","Category D",...)
5. Multidimensional Improv Worksheet to Multi-sheet Spreadsheet
Format in Improv: A multiple category worksheet in Improv
Desired format in 123: A multiple sheet spreadsheet file
a. Choose either the WK3 or WK4 file format for export.
b. To successfully create a new spreadsheet with multiple sheets, any category (or categories)
that should be exported as a worksheet should be repositioned, in Improv, as a page. When
more than one category is placed in the page layout area, the exported spreadsheet will define
each new sheet for each unique combination of category items.
Note: Page category item names are not translated into sheet names. Either rename the sheets
of the exported spreadsheet or place the current item names for all page categories in a cell
using the CELLNAME function.
Example: Item A1:Item B1=cellname("Category C","Category D",...)
RELATED DOCUMENTS:
Below are a list of related technotes that are available from the Lotus re:Quest BBS.
A Blank 256 Page WK3 or WK4 File is Created on Export
Data is Shifted When Exported to WK1, WK3, WK4 or XLS File
Item Names Lost When Model is Exported to WK3 or WK4 File
Tips and Techniques for Exporting Improv Files to ASCII Text
Pros and Cons of Using DDE to Link Improv Files
ADDITIONAL INFORMATION:
This document contains the same information as the technote entitled: "Tips and Techniques for Using
Improv Files in 123." (Reference #: 1340891) This duplicate document was created in order to provide formatting, such as text attributes and tables, which is not supported in the ASCII text file format of the technote.