Excel Integration - Axpo Demo Notes

Version 2, 2011-05-20 (AH) Ref Prj-E-6-01-610-221-00_0004-Anl-v02

Contents

  1. Excel Integration - Axpo Demo Notes
  2. Systems
  3. Status of the Excel Integration
    1. Recently Completed Features
    2. Limitations of the Excel VBA Code
    3. Columns Not Implemented
  4. Operation
    1. Startup
    2. Menu Items
    3. Login
    4. Search for and Display Data
    5. Updating Data and Saving It to Server
      1. Scenarios
      2. Restrictions
    6. Refresh Data
  5. Demo Script
  6. Test Data
  7. Compatibility Testing Notes
    1. Excel Versions Tested
    2. Windows Versions and Languages Tested

Systems

Status of the Excel Integration

Recently Completed Features

  1. The three Excel files have been reduced to a single file

  2. Search Document -- The performance have been slightly improved

  3. Create New Document -- Implemented (see the warning below)

  4. Get Next Sequence Number -- Implemented

  5. Check for Duplicates -- Now highlights the left-most cell and creates a cell comment indicating which rows are duplicates

  6. Menu items that access the server now check for duplicates before the server access is performed.
  7. The hidden worksheets are now hidden, and the system starts with a blank worksheet

Limitations of the Excel VBA Code

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

  1. There is a new comlumn Status/DrawMGT-Nr., which contains the DrawMGT's internal document id (which is blank for new docume ts). This field has the background color set based on the document status (which is new and not completely tested) as follows:

    • Grey = OK

    • Ping = Error, typically a duplicate document

    • Yellow = Warning, typically not saved or cell changed

  2. Cells with a light-orange color are meant to be read-only, this is a temporary solution to avoid slowing the developement effor . Protecting worksheets is a time consuming exercise. Currently the following fields are set to read-only:

    • Calculated field, 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/u locked. This code is not well-tested, and also does not set the revision code correctly.

  3. There is a bug with Create New Document, when you create a new document, you must select a cell in row 17 or below. O herwise it will be impossible to save the document.

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

  5. Re-loading data, using Search Documents into an existing list is not extensively tested and we have seen at least one bug.

Columns Not Implemented

The following worksheet columns are not implemented (and have a dark grey background):

  1. The Q-Eklaerung column

  2. Links to Grundlagen documents

  3. The Format column

  4. The Vorabzug ja/nein column - Currently has a bug where the revision codes are not reset currectly

  5. The Stn-Nr column

  6. The Art (Pruefungsart) column

  7. The Pruef-Nr column

Operation

Startup

  1. Open the Excel file LinthalDrawmgt-Planning-1.xlsm

  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 (make sure it is at or below row 18!)

    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. Test

    1. Test Cases - Displays a list of regression test cases (do not use)

Operations on all and selected rows:

For both cases the 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. A confirmation dialog is presented if the login is successful

Search for and Display Data

  1. Select menu item: DrawMGT --> Search Documents

  2. A search dialog is displayed: Enter the following search criterion:

    1. Rapport - Select Bau

    2. Bereich - Select LA2

    3. Kontakt - Select 213

    4. Laufnummer - Select 1000 --> 1020, or select a smaller range (2002 -> 2006) if you want quicker results.

Note that the search and display of data is slow, and that a number of hidden rows and columns are displayed during the processing p riod. This is because the Excel screen updating (e.g. recalculation) is enabled during the data processing. This was done to assist he testing and development process.

After the search processing completes, the hidden cells and rows are hidden, and a list of approximately 15 documents is displayed.

The header contains sections for the document and its vorabzug, zur Freigabe and gut zur Ausfuerhung revisions.

Features:

  1. The date calculation, based on the T0 and delta values is implemented. You should change T0 and the delta values, not the Soll ' dates

  2. Automatic creation of the Drawing Code, based on classification menu settings and the Laufnummer and Teil is impleme ted

  3. Status menus are implemented
  4. Title, scale and other text fields are implemented

Updating Data and Saving It to Server

You can change data for documents and revisions.

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 observer that preceding Soll dates change.

  3. Set the Ist date for the Vorabzug Erstellen

  4. Set the Ist date and Status (to non-Offen) for the Vorabzug Stellungnahme

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

  5. Change the values in the Resourcenplannung and Verteiler. Notes:

    1. The Resourcenplannung von and bis dates are calculated base on the T0 value and should not be changed manual y.

    2. The gut zur Ausfuerhung revision fields: Verteiler-Ext and Verteiler-Ablage are not saved correctly (a bug)

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

Restrictions

Given the limited test data observe the following restrictions:

  1. The Bereich for a document that has already been saved

  2. Do not change Ver (revision code)

  3. You can copy entire rows of data and the check for duplicats flag them. It will not be possible to save copied rows, unless:
    • No DrawMGT-Nr. is assigned to the rows (in Col R)
    • You must change the documet codes to avoid duplicates

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.

Demo Script

  1. DrawMGT Display

    1. Change to Bereiche LA2

    2. DrawMGT menu: Dokumente --> Suchen

      • Bereich - Aktueller Bereich

      • Kontakt - 213

      • Laufnummer - 1000 --> 1020

    3. Select document LA2-E-6-03-351-213-10_0003-v

      • Display workflow detail for revision V, the Vorabzug

  2. Excel Download

    1. Menu item: Search Documents

      • Rapport - Select Bau

      • Bereich - Select LA2

      • Kontakt - Select 213

      • Laufnummer - Select 1002 --> 1005

  3. Excel Update

    1. Find row (probably row 15) for LA2-E-6-03-351-213-10_0003

    2. Update T0 and Delta value:

      • Column CO(341): T0: Change date to three days earlier

      • Column AS(28): Delta7: Change from 28 to 25

    3. Complete workflow steps Vorabzug Erstellen and Stellungnahme

      • Column AQ(29): Erstellen Ist: Set date one month ago

      • Column AR(29): Erstellen Wer: Change user

      • Column AV(33): Stellungnahme Ist: Set today's date

      • Column AW(34): Stellungnahme Wer: Change user

      • Column AX(35): Stellungnahme Status: keine Bermerkungen

    4. Update Resourcenplannung and Verteiler fields

      • Column CQ(49): Planner CAD: Change from 25 to 28

      • Column CS(51): Planner PL: Change from 6 to 7

  4. Excel Save

    1. Select a cell in the row containing LA2-E-6-03-351-213-10_0003

    2. Menu item: Save Selected

  5. DrawMGT Re-Display

    1. Refresh web screen for revision LA2-E-6-03-351-213-10_0003-v

    2. Verify changes from Excel displayed

  6. DrawMGT Update

    1. In web screen for LA2-E-6-03-351-213-10_0003-v

    2. Button Aendern

      • Change Stellunnahme Dauer (Delta7): from 25 to 29

      • Save

  7. Excel Refresh

    1. Select a cell in the row containing LA2-E-6-03-351-213-10_0003

    2. Menu item: Refresh Selected

    3. Verify Delta7 changed to 29 and that the old value in the cell note is 25

Test Data

Fifteen LA2 documents have been entered into the Beta and Development systems listed above. The documents are copies of existing documents, and include specially created vorabzug, zur Freigabe and gut zur Ausfuerhung revisions. The T0, T0wv dates, Delta values and other planning related fields have been set in order to provide complete and consistent demonstration, which demonstrate the planning and scheduling process.

The document codes for the test data are:

Note that each document is a copy of an existing document aby adding 1000 to the Laufnummer as follows:

You can view the documents with the following search:

  1. Change to Bereiche LA2

  2. DrawMGT menu: Dokumente --> Suchen

    1. Bereich - Aktueller Bereich

    2. Kontakt - 213

    3. Laufnummer - 1000 --> 1020

Note that this displays all revisions associated with these documents:

When you display the revision detail, a new bock is shown which contains the user defined fields with text describing the fields name and contents.

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

Copyright 2008-2014, SoftXS GmbH, Switzerland