This tool is based on some work i have done for a commercial project.
A dicussion revealed source data headings have been imported with inappropriate characters and lengths.
I developed this tool in my spare time, which gave the team a faster method to review and correct the input data than wait till an upload fails
This table is updated for each push and shows insights to the discussion, analysis and specification that we are building in to the tool
| Journal Id | Date | Discussion | Date completed |
|---|---|---|---|
| 1 | 23 Jul 22 | Source Data headers contain incorrect characters or are too long | 25 jul 22 |
| 2 | 25 Jul 22 | Scan rows for control condition | 25 Jul 22 |
| 3 | 26 Jul 22 | File Picker Dialog with options | 26 Jul 22 |
| 4 | 30 Jul 22 | read map in to ui form list | 30 Jul 22 |
| 5 | 31 Jul 22 | Read user config in workbook | 31 Jul 22 |
| 6 | 31 Jul 22 | read data into list on form | 31 Jul 22 |
| 7 | 09 Aug 22 | finish filter, update comments, add named range management | 05 Aug 22 |
| 8 | 04 Sep 22 |
External source reports under go updates beyond the control of the team that analyses the data supplied. This has lead to characters and lengths that breach the analysis tools input constraints. The upload process fails under these conditions requiring manual fixes before upload can re-commence
A tool that helps identify and potentially fix issues/discrepancies can reduce upload failures.
The bad characters are detectable in several ways: a. as a character in a string b. as a regex in a string (Regex.test) The excess length is detectable as gt or lt
This suggests a set of filters need to be used for visual promting:
- no Filter
- Bad Characater Filter
- Too Long Filter
- Bad Char and Too long
To implement the above, there needs to be a means of
- configurating rather than hard coding mean users can apply this to a broader set of problems.
- reading until a condition is met in either row-wise or columnwise
- reading until a condition is met in a control column or row and either rowwise or column wise
excel sheets start Row=1 Column=1
excel sheets start Row=1 Column=1
- Open a mapping file, read the mapping names, display in a listbox
- open the source file, get a list of sheets and a list of headers per sheet (assume cells start Row=1, Col = 1)
- User configuraiton component reads from excel sheet
All functions tested using a local worksheet. Caveat - remote connection/dwonload is not considered in the testing
==========================================================================
Next issue is to run down a control column and collect all the key column data as a collection, followed by removing any blanks (compacting)
must hand in multiple values and have similar approach to the previous work.
while loop <> key value if cell(row, controlcol) == stop then exit increment row wend
excel sheets start Row=1 Column=1
excel sheets start Row=1 Column=1 as defaults
scan rows until key or control stop condition
a little tech debt in comments to fox identified a refactoring of the range checking iif into a consolidated function (maintenance)
==========================================================================
The user will need to pick a either a single mapping file or a set of data files
use the built in dialog and make most items available as parameters
wrap the dialog in s function and pass params to make it flexable ensure that one data type is returned, preferrably iterator type to catch cancel condition (ie empty set)
excel sheets start Row=1 Column=1
excel sheets start Row=1 Column=1 as defaults
Give the dialog a title to give a user a clue what is to be picked drive single or mulitple selects to ease picking the files
Feature added and unit tested with all passes Refacting improved the maintenance on the range checking
==========================================================================
The need to display data for visual reference to the end user requires a UI
VBA UI's have a limited set of elements. List elements include listbox and combo box
We will need to load data into other lists/combos so a generic load is useful here. We are already extracting most data in a collection due to the ease of iteration, so we will continue the pattern
generic list loader for any UI object of List or combo
a UI with one LB for this features
VBA is not good with UI objects as COntrol althougt this is more correct, its not always usable in a generic to pass the element
the UI is valid when we load the data
- UI with one ListBox
- Generic list loader (for both LB and CB)
==========================================================================
User configuration reduces dependency on a developer to alter some parameters
a config is really a key pair that the end user can maintain in a specific sheet
user a Key/Value approach to locate a named "key" to get to the value as a single call
the usual constraint in excel
call getConfigValue taking one value for the key
test harnes to demo the outcome was achieved
==========================================================================
The user form from id 4 is being extended to add a new element for the data headers
per the pattern established in this code, collections will b used to gather the desired headers
we need a columnwise collection gathering code base and re-use the listFromCollection cdoe
Display the header data to the user on a second list box
==========================================================================
test filter code and reaction to changing selections Discussion with lead about conditions that impact on name ranges. There are references to cells, sheets and formula used. The ranges are removed when sending the workbooks attached to an email.
Workbooks have a Names propery which contain the data we want to extract into plain text or a spare sheet for that purpose.
cannot leave a macro in the book to be sent. must allow a user to pick a file themselves both store and restore need to be robust against errors
store data as text only
Use existing patterns
Use work book names and extract to a temp page
==========================================================================
==========================================================================
==========================================================================