Excel Integration - Axpo Demo Notes

Version 3, 2011-06-06 (AH)

Ref Prj-E-6-01-610-221-00_0004-Anl-v03

Contents

  1. Excel Integration - Axpo Demo Notes
  2. Status of the Excel Integration
    1. Recently Completed Features
    2. Limitations of the Excel VBA Code
    3. Columns Not Implemented
    4. Columns Implemented
    5. Restrictions
  3. Operation
    1. Startup
    2. Menu Items
    3. Login
    4. Creating and Saving Documents
      1. Features
      2. Document Creation Scenario
      3. Editing Scenarios
      4. Saving
    5. Searching for Documents
    6. Refresh Data
  4. Compatibility Testing Notes
    1. Excel Versions Tested
    2. Windows Versions and Languages Tested

Status of the Excel Integration

Recently Completed Features

  1. The temporary test data has been removed from the beta servers.
    • This means that searches will not return any documents, because there are no documents with the correct revision types.

    • Use the Create New Document menu item to create new documents for saving and searching

  2. New menu items Create Report O2F1 and Create Report O2F2

    • O2F1 (Dokumentenlieferprogramm) - incomplete prototype (see below)

    • O2F2 (Ressourcenplanung) - not implemented.

  3. All main menu items work

    • With the exception of the new Create Report items mentioned above.

  4. The initial testing of document and revision serial numbers (hidden fields for catching simultaneous updates) is complete.

Limitations of the Excel VBA Code

The following features are not completely implemented and/or not well-tested:

  1. Known bug: New rows. When creating new rows with Search Documents or Duplicate Selected, the rows created at the end of the first contiguous selection, instead of after the last selected item.

  2. Known bug: Highlight colors. The highlight coloring of the new comlumn Status/DrawMGT-Nr. is incomplete. It needs corrections and more testing.

    • Check for Duplicates menu sets the highlight color to white in spite of previous yellow/red coloring.

    • The highlight colors are:

      • White = OK

      • Pink = Error, typically a duplicate document

      • Yellow = Warning, typically not saved or cell changed

  3. Incomplete implementation: Cell-locking. Cells with a light-orange color are intended to be read-only.

    • This is a temporary solution to avoid slowing the developement effort. Protecting worksheets is a time consuming exercise.
    • Currently the following fields are set to read-only:

      • Calculated fields, typically Soll date fields

      • Bereich and Vorabzug ja/nein. When the Vorabzug ja/nein menu is changed then all the Vorabzug fields will be locked/unlocked. This code is not well-tested, and also does not set the revision codes correctly.

  4. Incomplete testing: Ist fields. The validation of the Ist date sequence is implemented, but not extensively tested, there may be bugs.

  5. Incomplete implementation: Reports

    • O2F1 (Dokumentenlieferprogramm) is an early prototype, There are column formatting errors (date fields).

    • O2F2 (Ressourcenplanung) is not implemented.

Columns Not Implemented

The following worksheet columns are not implemented:

  1. The Q-Eklaerung column

  2. Links to Grundlagen documents

  3. The Format column

  4. The Vorabzug ja/nein column - Does not clear the Vorabzug fields when the menu is set to nein

  5. The Stn-Nr column

  6. The Art (Pruefungsart) column

  7. The Pruef-Nr column

Columns Implemented

  1. The date calculation, based on the T0 and delta values is implemented. You can change T0 and the delta column values. The Soll date felds are colored to indicate that they are read-only

  2. Automatic creation of the document code, based on classification menu settings and the Laufnummer and Teil fields is implemeted

  3. Wer, Ist and Status fields are implemented

  4. Title, scale and other text fields are implemented

Restrictions

  1. You cannot change Bereich or Vorabzug ja/nein columns for a document that has already been saved

  2. You cannot not change Ver (revision codes) columns

  3. You can copy entire rows of data and use the Check for Duplicats to flag them.

    • It will not be possible to save copied rows, unless unique document codes are assigned to the rows. Enter them manually or use the Get Next Sequence menu item

Operation

Startup

  1. Open the Excel file Prj-E-6-01-610-221-00_0004-V05.xslm

  2. Click on the DrawMGT menu.

  1. Server

    1. Login - Login into the DrawMGT server

    2. Logout - Logout from server

  2. Save

    1. Save Selected - Save all rows to server (see below)

    2. Save All - Save selected rows to server (see below)

  3. Search

    1. Search Documents - Displays a search form. See below for details

    2. Refresh Selected - Refresh selected rows (local data) with latest data from server (see below)

    3. Refresh All - Refresh all rows (local data) with latest data from server (see below)

  4. Edit

    1. Create New Document - Create a new document at the selected row

    2. Duplicated Selected - Duplicate the selected rows

    3. Get Next Sequence - Get the sequence Lauf-Nr. for the selected rows

    4. Restart Revision Sequence - Assign the text set of revision codes to the selected rows

    5. Check for Duplicates - Check for duplicate documents. Checks the DrawMGT-Nr. and the Documentindex

    6. Refresh Attributes - Functions correctly, but is very slow (it downloads many thousands of records!)

  5. Report

    1. Create Report O2F1 - Displays a Documentenlieferprogramm report (incomplete)

    2. Create Report O2F2 - Displays a Ressourcenplanug report (not implemented yet)

Operations on all and selected rows:

Header rows and empty rows are ignored. E.g. if you select cells in the header or empty rows nothing bad happens.

Login

  1. Select menu item: DrawMGT --> Login

  2. Enter DrawMGT user name and password
  3. Use system (selected by default):
  4. A confirmation dialog is presented if the login is successful

Creating and Saving Documents

Features

  1. Create new documents/revisions
  2. Duplicate documents/revisions
  3. Save changes and new documents/revision to the server
  4. Refresh documents/revisions from the server

Document Creation Scenario

  1. Create a new document using the Create New Document menu item

    • Assign a unique document code manually or using the Get Next Sequence menu item

    • Set/edit the other (non-readonly) fields
  2. Save the document
  3. Duplicate the document (perhaps multiple times)
    • Assign unique document codes
    • Save the documents
  4. Delete the document rows from the worksheet
  5. Search for the documents just created (see next section)

Editing Scenarios

  1. Change the value of the T0 field and observe that the Soll dates change.

  2. Change the value of the Delta fields and observe that preceding Soll date fields change.

  3. Set the Ist dates and Status fields.

    • You can also change the Wer columns. Note that the user list is displayed based on the user roles in the server

  4. Change the values in the Resourcenplannung and Verteiler.

    • The Resourcenplannung von and bis dates are calculated base on the T0 value and cannot be changed manually.

Saving

To save data there are two choices:

  1. Select the menu item Save All. All rows will be saved

  2. Select cells in rows that should be saved and press the menu item Save Selected

Searching for Documents

Note that there is no longer any test data in the beta server and that Search will only show documents with revisions having the following revision types:

When documents are displayed, the header contains sections for the document and its vorabzug, zur Freigabe and gut zur Ausfuerhung revisions.

Refresh Data

If data is changed on the server, then it is possible to refresh rows in the planning spreadsheet, so that they are updated with the latest server data.

To reshresh data there are two choices:

  1. Select the menu item Refresh All. All rows will be refreshed

  2. Select cells in rows that should be refreshed and press the menu item Refresh Selected

When data is refreshed, all document and revision data for the row is downloaded and displayed in the corresponding Excel cells. If ifferences occur, then the cell contains the server (new) value and a cell note is appended to the cell with the original (old) spre dsheet value.

Compatibility Testing Notes

The Excel code has been tested on different versions of Windows and Excel.

The only know limitation is that the VBA code does not yet work with Excel 2003.

Excel Versions Tested

Windows Versions and Languages Tested

ExcelIntegrationAxpoDemoNotes (last edited 2011-06-06 14:14:11 by alan)

Copyright 2008-2014, SoftXS GmbH, Switzerland