Importing Data from Password Protected Excel Workbooks
When configuring an import from an excel workbook, if the workbook is encrypted with a password, MooD currently gives a message saying it is not supported.
In our latest release users can now input and store the password for manual and scheduled execution.
When the user is configuring an import of a password protected excel document for the first time, they will be presented with a password prompt dialog.
This dialog has a checkbox option to save the password, which is checked by default.
Even if the password is saved, the user will still be prompted to re-enter the password if they attempt to open the spreadsheet to edit the ranges.
Changing or deleting saved passwords
The saved password may be removed using the Synchronizer properties.
The dialog to change the saved password does not validate the password against the spreadsheet. This is deliberate in order to support situations in which the synchronizer may be configured against a local version of a spreadsheet, but a different password may be required to access the equivalent spreadsheet on the server.
The saved password is saved in an encrypted format and is only decrypted at the point of use. The current value of the saved password is never displayed to the user.
Invalid or missing passwords
In the event of a Synchronizer execution failing, because of an invalid or missing password, this information will be included in the Activation Errors and Warnings:
- In normal operation, and with the Synchronizer configured to use Server Mode (default), selecting ranges at configuration and importing data at execution ignores Excel's finer grained protection; i.e. the protection of cell ranges, sheets and Workbook structure. Passwords will not need to be provided for these types of protection if they are present in the Workbook. Where the entire workbook is password protected the operation will be as detailed above.
- Where the local machine has been configured (using a registry key) to use Excel to provide the range selection grid, password protected Workbooks will not be supported. Furthermore, in this situation, protection of individual Worksheets and Cell ranges will be enforced on the grid; i.e. it will not be possible to select a cell within a range which has been locked and protected. N.B. this configuration requires creating a special registry key and should be rarely used.
- Where the Synchronizer has been set to ‘Native’ mode, password protected Workbooks will not be supported and any saved password cannot be used to access the spreadsheet when the Synchronizer is executed. In ‘Native’ mode, finer grained Excel protection such as locked ranges and protected Worksheets will not prevent execution of the import.
Improved Support for Password Protected Workbooks on Excel Merge Export
The UI of the Excel Matrix Export configuration dialog, has been modified to challenge a user to provide a password before the spreadsheet can be opened to select a cell.
This protects the data in the spreadsheet from inadvertent access by users who do not know the password.
No challenge is raised if the spreadsheet is not password protected.
Note the challenge also provides the option to save the password, this will now be saved in an encrypted form. The saved password can be removed or modified from within the configuration dialog:
The saved password will be used to open the spreadsheet during Execution of the Synchronization.
If the spreadsheet is password protected and an invalid, or no password is provided, the synchronisation will fail and provide an appropriate error message:
NOTE: existing Excel Matrix Export Synchronizers which currently have a valid password saved will continue to execute correctly.
It would be good practice, after upgrade, to open all such Synchronizers, re-enter and re-save the correct password; this will ensure that the password is resaved in an encrypted format.
Under normal operation, selecting ranges at configuration and exporting data at execution ignores Excel's finer grained protection; i.e. the protection of cell ranges, sheets and Workbook structure. Passwords will not need to be provided for these types of protection if they are present in the Workbook.
Where the local machine has been configured (using a registry key) to use Excel to provide the cell selection grid, protection of Worksheets and locking of Cell ranges will be enforced; i.e. it will not be possible to select a cell in a locked range on a protected sheet. There will be no effect on the behaviour at Execution of the Synchronizer.
Excel Matrix Export Synchronizer - New File Formats
All Excel file formats are now supported for a Matrix Export Synchronizer merge, into existing Excel files:
Excel matrix exports now support the following file formats:
- Tab Delimited Text
- As well as the standard Excel file formats (.xlsx, .xlsm, .xls)
Images / charts in merge files will no longer be lost on merge.
Supporting Import of Large Excel Files
Previously a user was only able to see up to the 65,536th row, for large Excel Import SATs. Now the user can view up to the 1,048,576th row when importing large Excel files.
Large improvements have also been made to memory usage, and to prevent memory build-ups with Excel Import SATs.
These changes are made available in Build 85