VXData
VXData allows the import from Microsoft Access Database tables and queries into an Microsoft Excel spreadsheet, which allows – once the link is established – to easily update monthly reports.
Screenshot
General Options
Update external data
The data update can happen via the shortcut Ctrl+Shift+U (Update) or via the top right button (aktualisieren).
Workbook protection
After the data has been successfully updated, the workbook can be protected and unprotected using either the shortcut Ctrl+Shift+P (Protection) or via the button underneath the update facility. The button naturally is not available for unprotection, as the config sheet is invisible when in protected mode; otherwise the password would be pretty useless.
It is possible to assign a password for the protection / unprotection process. The password can be entered in cell F2 and is asked, when the protection is turned off via pressing Ctrl+Shift+P (Protection) again.
_ConfigExtData
In the named range _ConfigExtData reside the details for the different data sources, namely:
| Worksheet | Name of the worksheet that will be created and containes the retrieved data. |
|---|---|
| App / Path | Path to the database. It is also possible to retrieve the path of the database by providing the name of the application / project and providing an according setting in the registry via Visual Basic. For detailed information please have a look at the source code or contact the developer. |
| DSN / File | File name of the database. It is also possible to retrieve the connection to the database via a DSN entry. |
| SqlFields | Default is "*"; You can specify single fields of the resulting recordset to be loaded. Example: "Id, Description, Price". |
| SqlFrom | Name of the table that is queried, usually something like "tblData". |
| SqlWhere | Where condition of the query without the keyword "where". Example: "Price > 1000". |
| SqlOrder | Comma separated list of fields to order by. Example: "Price, Description". |
| Name | Internal Name that is assigned to the imported data. Useful for abstract binding to graphs, pivot tables and other analyses. |
| NameColumn | Default is "alle"; Comma separated list of fields to assign a name to. Very handy for abstract analyses too. |
_ConfigWorksheets
In this section the defaults for the other worksheets – besides the imported ones – is made. It primarily focuses on the visibility of data.
| _ConfigWorksheet | Name of the worksheet that the settings apply to. |
|---|---|
| _ConfigWorksheetLevelRow | RowLevel to be displayed after protecting the workbook. |
| _ConfigWorksheetLevelCol | ColumnLevel to be displayed after protecting the workbook. |
| _ConfigWorksheetDisplay | Shall the worksheet be visible after protecting the workbook? Possible values: 0 and -1. |
| _ConfigWorksheetCalculation | Shall the calculation of the Worksheet happen automatically? Possible values: 0 and -1. |
| _ConfigWorksheetRange | Which cell shall be selected after protecting the workbook? |
_ConfigFormat
Sometimes it is necessary to apply a special format to the data that has been loaded from a database.
| _FormatRange | Name of the range that the settings apply to. |
|---|---|
| _Format | Number format that will be applied to the range. |
Finally
As VXData has been developed on a localized computer, some of the names and values are still in the "original" language. These settings will hopefully be fixed in future versions.
If you encounter problems, have suggestions or simply want to give feedback to VXData, please do not hesitate to contact us.
