Excel Excel When Importing Spreadsheets Into Pagemaker Or Quark!
Getting Started: A Quick Tutorial
Who said that the Digital Revolution would make life easier??

Getting Started: A Quick Tutorial

FutureWare Spreadsheet Power Importer!  The FutureWare Spreadsheet Power ImPorter ("SPIP") application is used to generate an Import file from a selected Excel worksheet, either as a tab delimited plain text file, or as a rich text document that includes style information (e.g., fonts, their sizes and colors, etc.). The generated Import file can then be imported into other tool programs, often directly. The use of an intermediate file instead of the Excel file directly eliminates time-consuming file interpretation and version dependency issues, as well as enhancing application stability and security. It also eliminates requiring any specialized knowledge of Excel's use and manipulation.

Each area covered will bring up various forms and windows, each with their own set of buttons and controls, which we suggest you ignore for now and address only those mentioned in the tutorial; the rest will be covered in due course. The tutorial is organized in the following sections, which should be followed sequentially.

Opening And Examining An Excel Spreadsheet

An Excel file is selected and SPIP set-up to generate an Import file that will be imported by another tool application (e.g., Pagemaker or Quark) by clicking the toolbar button, or clicking the keyboard combination, which opens a (Find Excel File) dialog, where an Excel file is located and then opened when the dialog's Open button is clicked. The first time SPIP is used in a session, the search will start at the system's root (i.e., at c:\), with subsequent finds starting at the folder where the last Excel file was selected.

When an Excel file is selected, the first worksheet will be opened and examined for it's size in column width and row count. The number of columns is determined by examining each cell in row one, starting at the first one (i.e., cell A1) and continuing until an empty cell is detected in row one or 255 columns have been examined. The values of these cells in row are called "column headers", and are used to describe the contents of the cells in the associated column.
While the selected Excel file is being examined, a progress bar will become visible, and will continue looping to indicate activity until the examination is complete, at which time the progress bar will become invisible.
The number of rows in the worksheet is determined by examining up to three column cells in successive rows, starting with the first detected non-empty column, until either all columns in a row are empty, or 10,000 rows have been examined.
By convention, the first row of an opened Excel's worksheet contains column header descriptions (e.g., Name, Address, PhoneNumber, etc.), while the following rows contain the data corresponding to the column header descriptions. These column header descriptions from the Excel worksheet's first row are displayed in the form's listbox, with the first entry corresponding to Excel cell A1, the second entry corresponding to Excel cell B1, etc.

When the examination is complete, the number of rows and columns will be displayed, and the various sections of the main form enabled for changing the order, naming, style selections, etc., that will be used when the Import file is generated, which is done by clicking the toolbar button.
Table Of Contents

Re-Ordering The Spreadsheet's Column Positions

When an Excel worksheet is selected, opened and examined, the column headers from the worksheet's first row are loaded into the listbox in their "native" order (i.e., cells A1, B1, C1, etc.), including hidden cells. The order of the columns in the Import file can be changed by first clicking a column header in the listbox and then using the MoveDown and MoveUp buttons (which does NOT make any changes to the worksheet's organization). A column cannot be moved into a column group, and a column group cannot be moved. The original column header order can be restored by clicking the toolbar button, or using the keyboard combination.

Table Of Contents

Changing The Column Header Descriptions

By convention, the first row of an opened Excel's worksheet contains column header descriptions (e.g., Name, Address, PhoneNumber, etc.), while the following rows contain the data corresponding to the column header descriptions. These column header descriptions from the Excel worksheet's first row are displayed in the form's listbox, with the first entry corresponding to Excel cell A1, the second entry corresponding to Excel cell B1, etc.

The column header descriptions can be changed by clicking an entry in the listbox, changing its description in the ColumnName edit box, and then tabbing out of the edit box, which will change the column header's description in the list box. A column description cannot exceed 48 characters. The ColumnValue is used to display a representative value of the selected Excel spreadsheet's cell, as an aid in formatting.
A column header description can be restored to its original value by double-clicking the ColumnName edit box, or doing a full refresh by clicking the toolbar button, or using the keyboard combination.
Checking the checkbox will format the selected column's currency value to have separators and cents' expressed, so that (for example) 1234 will be presented as 1,234.00
If the checkbox is checked, then the column header descriptions in the listbox will be inserted as the first line into the Import file, including any style settings or indentations. Similarly, if the CopyToClipboard ColumnHeaders radio button is selected, then the column header descriptions in the listbox will be inserted into the System's Clipboard as regular text, with each column header separated by a tab delimiter, but without any style settings or indentations.
Table Of Contents

Adding Text Before And/Or After Column Data

Every column value inserted into the Import file can have a prefix string in front, and a suffix string following, the column value. These strings will have the same style information (if any) as their associated column. The prefix and suffix strings cannot exceed 12 characters. These strings are entered by first clicking a column entry in the list box, and then using the Prefix and Suffix edit boxes, respectively.

Table Of Contents

Collecting Multiple Columns Into One Unit

Contiguous column values can be grouped together as if they were a single column, with a tab delimiter at the end of the group but not between the individual columns that make up the group. Each column in the group will include its prefix and suffix strings, and can have their own styling information if the checkbox is checked.
An example of a column group is City, State and ZipCode which have their own column values, and would be inserted into the Import file with tab delimiters. Grouping these columns, with a ", " suffix string for the City column and a " " suffix string for the State column would insert the following into the Import file: City, State ZipCode as if it were a single column value.
A column group is made by first clicking a column header entry in the listbox, then holding down the Ctrl key and clicking on additional and adjacent column header entries (called a pending group), and finally right-clicking the mouse, which will mark the multiple column header entries as a group, indicated by an alpha-character [.A.] preceeding the column header entries, where A is the column identifier letter within the Excel spreadsheet of the first column in the group.

A column group cannot be moved, or cross an indentation boundary, or include an indentation. Similarly, ungrouped column headers cannot be moved across a group. A column group can be indented by double-clicking the first entry in the group. A pending group can be cleared by clicking the toolbar button, or using the keyboard combination.
Table Of Contents

Indentation For More Efficient Use Of Layout Space

Excel column row values are inserted into the Import file in a left-to-right manner, with each column value separated by a tab delimiter. In some cases, it's convenient to break up one Import line for one Excel row by adding a hard return within the row. Each indentation level will add a hard return, prefix the next line with the number of tab delimiters as the indentation level. An indentation is selected by double-clicking a column header description in the listbox, which moves the selected column headers and all those below it to the right. An indentation can also be removed by double-clicking the first column header description in the indentation list.

A column group can be indented (or un-indented) by double-clicking the first entry in the group.
The indentation always adds a hard return, but the number of leading tab delimiters will be ignored if the checkbox is checked.
Indentations can also be removed by clicking the toolbar button, or using the keyboard combination.
Table Of Contents

Selecting Fonts, Colors And Styles

When the checkbox is checked, then style information (i.e., font, size, color, etc.) can be set for each column value, which will include any prefix and suffix strings, by clicking its entry in the listbox and then clicking either the FontName display box or the FontColor square, which opens a (Font) dialog where style information can be set. Each time the (Font) dialog is opened, the previously selected values will be retained and displayed for acceptance or change as required.

Style information is not obtained from the Excel worksheet, but instead set within SPIP. This insures that any style selection is "known" to the system using SPIP, and eliminates the problem of an Excel style that is undefined to the system (e.g., when the Excel file originates from a different organization). It also increases security in that imbedded macros or "unknown" objects within an Excel file will not be initiated.
If the checkbox is not checked, then no style information can be set, and any style information that may have been assigned to the column headers will be removed.

When the checkbox is checked when the toolbar button is clicked, then the Import file generated by SPIP will have an RTF extension and will be saved as a rich text file. If the [SetStyleInformation] checkbox is not checked when the toolbar button is clicked, then the Import file generated by SPIP will have an TXT extension and will be saved as a regular text file.
Table Of Contents

Generating An Import File

When the column changes have been made (e.g., column header order, naming, style selections, etc.) then clicking the toolbar button (or using the keyboard combination) will open a (Save Import File To) dialog, where the generated Import file will be saved. By default, the save-to folder is the same as that in which the Excel file was found, and the generated Import filename will have a "SPIP_" suffix to the name of the opened Excel worksheet, with an extension set according to the setting of the checkbox: If checked, then the extension is set to RTF, and a generated Import file will be saved as a rich text document; if not checked, then the extension is set to TXT, and a generated Import file will be saved as a standard text document without any style information.

The size of the Import file generated when the toolbar button can be controlled by the number of column headers displayed in the listbox, and the starting and ending row number values of the respective edit boxes, which by default are set to 2 for the start row number, and the number of rows in the worksheet that were detected when the worksheet was examined when the toolbar button was clicked. Any other values should be entered before the toolbar button is clicked.

When the Import file is being generated from the selected Excel file, then either the column headers, or the first data line, can be copied into the System's Clipboard, as determined by the CopyToClipboard radio group selection. The clipboard data will be a single line with the column values, including any prefix and suffix strings, each separated by a tab delimiter, without any line insertions/indentations or style information, but column header groups will be included.

If the checkbox is checked, then the column header descriptions in the listbox will be inserted as the first line into the Import file, including any style settings or indentations each column may have.
While the Import file is being generated, a progress bar will become visible, and will indicate activity as a percentage complete (and remaining) until the Import file generation is successfully completed, at which time the SPIP application will close automatically.
Table Of Contents

Examining A Generated Import File

The last generated import file can be viewd and examined by clicking the ViewImportFile main menu item.
Table Of Contents

Sorting To Arrange Column Data In An Ordered Fashion

If the checkbox is checked when the toolbar button is clicked, then a (Select SPIP Sort Keys) form is opened, where up to three column headers can be selected for sorting.
If more than one sort key is selected, then the Excel worksheet's column data will be sorted in an inclusive-cascaded method. As an example, if the first sort key column is "State", the second sort key column is "City", and the third sort key column is "Name", then all of the Excel rows will be sorted first by State, then for each state value the City rows will be sorted, then for each city value the Name rows will be sorted.

Columns to be sorted are clicked in the AvailableColumnHeaders listbox and then added to the SortKeyColumnHeaders listbox by clicking the form's Add toolbar button. Columns can also be added to the sort list by double-clicking their entry in the AvailableColumnHeaders listbox. Selected sort columns in the SortKeyColumnHeaders listbox can be re-ordered by clicking an entry and then clicking the MoveDown or MoveUp buttons. Sort columns can be removed by either clicking their entry in the SortKeyColumnHeaders listbox and then clicking the form's Remove toolbar button, or by double-clicking an entry in the SortKeyColumnHeaders listbox.
The sort order, either Ascending or Descending, can be set by clicking an entry in the SortKeyColumnHeaders listbox and then clicking the required entry in the Sort Order radio group.
When the form's toolbar button is clicked, the form will be closed and the Excel worksheet's data sorted according to the sort keys that have been selected.

Table Of Contents

Pagemaker Example: Using An Import File Generated By SPIP

Open the Pagemaker document where you want to place your SPIP file. CAUTION: Turn off any Style assignment, using the Arrow tool to select No Style as the default.
Now you can PLACE your SPIP RTF file into your Pagemaker document.
Use the File/Place command as you normally would. When the file is in place in the document, put the Text tool somewhere in the content and go into the text editor (e.g., using the Ctrl+E keyboard combination). Here you can change any words or numbers to any other characteristic you need by simply utilizing the Change utility in the Pagemaker story editor and set up the CharAttributes controls. To utilize this utility as a global changer, don't put any specific words in the boxes in the first Change window - simply leave those first Find and Change boxes blank and go right to the CharAttributes section. Watch your choices carefully.
For example, if you set up your SPIP RTF file with a column to which you assigned a blue color, you can look for any text of blue color in CharAttributes (Find What) and change it to (Change To) any other characteristic you require in Pagemaker.
Close the CharAttributes, use any of the Change options on the Change panel. Close the text editor and go back to the layout. To change the tabs which you retained in the file, simply use the Tab command as usual. Your SPIP RTF file will retain the tabs you did not delete and these can be controlled in Pagemaker with the Tab command as usual.
Remember: WHEN YOU FIRST SET UP YOUR SPIP OUTPUT, YOU SHOULD KEEP IN MIND HOW YOU WANT TO ASSIGN CHARACTERISTICS IN PAGEMAKER, SO THAT YOU CAN USE THE PAGEMAKER FIND AND CHANGE COMMAND AS YOU REQUIRE.
You can assign a Style to your new Pagemaker content - it will likely hold any bold font assignment - but the Style will override your font and color choices and tab setups with its own.
1. This example uses a Pagemaker file that is ready to accept tabular data extracted from an Excel file with SPIP. The Pagemaker document is opened, and the import started by selecting the Place command, using the {File|Place} Main Menu item.

2. If the Import file has Style information, then it was saved as a rich text document. Select it from the selection dialog.

3. If the Import file does not have any Style information, then it was saved as a standard text document. Select it from the selection dialog, and then use the settings in the Text Only Filter form as indicated.

4. When the file is in place in the document, put the Text tool somewhere in the content and go into the text editor (e.g., using the Ctrl+E keyboard combination).
5. Here you can change any words or numbers to any other characteristic you need by simply utilizing the Change utility in the Pagemaker story editor and set up the CharAttributes controls.
6. To utilize this utility as a global changer, don't put any specific words in the boxes in the first Change window - simply leave those first Find and Change boxes blank and go right to the CharAttributes section. Watch your choices carefully.

Table Of Contents