= Excel Integration - Axpo Demo Notes = '''Version''' 3, 2011-06-06 (AH) '''Ref''' Prj-E-6-01-610-221-00_0004-Anl-v03 <> = 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 1. New menu items ''Create Report !O2F1'' and ''Create Report !O2F2'' * ''!O2F1'' (''Dokumentenlieferprogramm'') - incomplete prototype (see below) * ''!O2F2'' (''Ressourcenplanung'') - not implemented. 1. All main menu items work * With the exception of the new ''Create Report'' items mentioned above. 1. 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. 1. '''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'' 1. '''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. 1. '''Incomplete testing: Ist fields.''' The validation of the ''Ist'' date sequence is implemented, but not extensively tested, there may be bugs. 1. '''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 1. Links to ''Grundlagen'' documents 1. The ''Format'' column 1. The ''Vorabzug ja/nein'' column - Does not clear the ''Vorabzug'' fields when the menu is set to ''nein'' 1. The ''Stn-Nr'' column 1. The ''Art'' (''Pruefungsart'') column 1. 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 1. Automatic creation of the document code, based on classification menu settings and the ''Laufnummer'' and ''Teil'' fields is implemeted 1. ''Wer'', ''Ist'' and ''Status'' fields are implemented 1. 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 1. You cannot not change ''Ver'' (revision codes) columns 1. 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''' 1. Click on the ''DrawMGT'' menu. == Menu Items == 1. Server 1. '''Login''' - Login into the DrawMGT server 1. '''Logout''' - Logout from server 1. Save 1. '''Save Selected''' - Save all rows to server (see below) 1. '''Save All''' - Save selected rows to server (see below) 1. Search 1. '''Search Documents''' - Displays a search form. See below for details 1. '''Refresh Selected''' - Refresh selected rows (local data) with latest data from server (see below) 1. '''Refresh All''' - Refresh all rows (local data) with latest data from server (see below) 1. Edit 1. '''Create New Document''' - Create a new document at the selected row 1. '''Duplicated Selected''' - Duplicate the selected rows 1. '''Get Next Sequence''' - Get the sequence ''Lauf-Nr.'' for the selected rows 1. '''Restart Revision Sequence''' - Assign the text set of revision codes to the selected rows 1. '''Check for Duplicates''' - Check for duplicate documents. Checks the DrawMGT-Nr. and the Documentindex 1. '''Refresh Attributes''' - Functions correctly, but is very slow (it downloads many thousands of records!) 1. '''Report''' 1. '''Create Report !O2F1''' - Displays a ''Documentenlieferprogramm'' report (incomplete) 1. '''Create Report !O2F2''' - Displays a ''Ressourcenplanug'' report (not implemented yet) Operations on all and selected rows: * '''All Rows''' - Operation works on all rows in the spreadsheet * '''Selected Rows''' - Operation works on rows where any cell in the row is selected. The selection does not need to be contiguous. 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''' 1. Enter DrawMGT user name and password 1. Use system (selected by default): * ZG Development -- http://linthal.softxs.ch/dev/linthal115dev 1. A confirmation dialog is presented if the login is successful == Creating and Saving Documents == === Features === 1. Create new documents/revisions 1. Duplicate documents/revisions 1. Save changes and new documents/revision to the server 1. 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 1. Save the document 1. Duplicate the document (perhaps multiple times) * Assign unique document codes * Save the documents 1. Delete the document rows from the worksheet 1. 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. 1. Change the value of the ''Delta'' fields and observe that preceding ''Soll'' date fields change. 1. 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 1. 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 1. 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: || '''Revision''' || '''Code''' || '''Description''' || || '''v_B''' || ''GA'' || ''gut zur Ausfuehrung'' || || '''v_A''' || ''ZF'' || ''zur Freigabe'' || || '''v``_``_''' || ''VA'' || ''Vorabzug'' || 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 1. 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 == * ''Excel 2007'' and ''Excel 2010'' * Not tested ''Excel 2003'' == Windows Versions and Languages Tested == * ''Windows 7'', German and English * ''Windows XP'', Hungarian