How To: Copy from a Grid to Excel

Whilst there is more than one way of copying from a Khaos Control grid to Excel, you may find this two-step process to be the most straightforward method:

  1. Exporting a grid to the clipboard
    Note: this function is only available to Administrators and other users who have the 'Print/export grid' checkbox checkbox ticked in their "User Permissions" dialog.
  2. Pasting the data into Excel

Exporting a grid to the clipboard

  1. Ensure the grid contains the data you wish to export.
  2. In the grid, green-select the cells you want to copy (e.g. by holding down the Ctrl key and left-clicking in the rows you want to select).

    Sc Stock Grid3.jpg

    Hint: You can use Ctrl+A to select the entire grid.
  3. Clicking on the small drop down arrow at the top left of the grid Btngridmenu.jpg displays the Grid Menu.
  4. Choose [ Send to Notepad ] from the options to open the 'Export Columns' dialog box:

    The 'Export Columns' dialog box.

    Note: this dialog shows the names of the columns from the current Grid which may not match those shown in this screenshot.

  5. By default, all columns from the grid are included in the export.
    To select a subset of these:
    • Pressing the "Exclude All" button turns off all columns, preventing them being exported (notice how the column names in the dialog box turn dark when they are excluded from the export).
    • Pressing the "Include All" button turns on all columns, including them in the export.
    • Clicking on the names of individuals columns toggles them between the excluded (darker coloured background) and included (lighter coloured background) state. The horizontal scroll bar that appears immediately below the column names allows the column names to be moved left and right.
  6. To set the output format, press the "Single Tabs" radio button.
  7. Press the "OK" button. This will:
    1. Write your selection to the default file (C:\KeystoneSoftware\GridKC.txt); and
    2. Close the "Export Columns" dialog.
  8. The GridKC.txt file should then open automatically in Microsoft Notepad, with the data you have exported showing. Here is an example using data from our training system. Notice how the headings and columns do not always line up; this is because each column is separated from the next by a single tab character:
    DlgGridMenuExportColumnsGridKC.jpg

    Note: If your computer has a program other than Microsoft Notepad associated with files having a ".txt" extension, you should see GridKC.txt open in that program instead.
  9. To copy this data to the clipboard:
    1. Select the entire contents of the GridKC.txt file (e.g. Ctrl+A); and
    2. Copy it to the clipboard (e.g. Ctrl+C).

Pasting the data into Excel

  1. Startup Microsoft Excel.
  2. Open a new worksheet in Excel and make sure the current cell is A1 (top left hand corner of the worksheet).
  3. Paste the clipboard's contents into the worksheet (e.g. Ctrl+V).
  4. Carefully examine the data that has been pasted, looking for any data that should have leading zeroes (by default, Microsoft Excel uses the 'General' format for all columns in a new spreadsheet). Should any columns (e.g. Customer URN, Sales Order code) be missing its leading zeroes:
    1. Select the column.
    2. Set the affected column's format to be "text" (e.g. In Excel 2003, select 'cells' from the main window's 'Format' menu, click on 'text' and then press the "OK" button to close the dialog):


      Note: Do not be tempted to shortcut this step by setting the entire spreadsheet to use text format, as currency values and other wholly numeric fields will not be stored correctly.
    3. Put the cursor in cell A1 (top left hand corner of the worksheet).
    4. Paste the clipboard's contents into the worksheet (e.g. Ctrl+V), overwriting what you had previously pasted.
    5. Re-examine the data, repeating these steps as necessary until all columns containing data with leading zeroes have been formatted as text.
  5. Use Excel's "Replace All" command to delete the contents of any cells that contain '(empty)':
    1. Use Ctrl+H to display Excel's "Find and Replace" dialog;
    2. Enter '(empty)' into 'Find' and delete anything in 'Replace'; then
    3. Select 'Replace All':


  6. Save your new Excel worksheet.
  7. Close the window displaying the KhaosKC.txt file.

See Also


Did you find this article helpful?