LLImporter

Imports the data of an external workbook into the linelist that holds this object. CheckImportFile reads the file and says whether it can be imported, HasSameLanguage compares the two interface languages, and CurrentLanguage answers the language of this linelist. ImportData brings the HList and VList data in, ImportCustomDropdown the custom dropdowns, ImportShowHide the show/hide choices, ImportEditableLabels the labels a user may edit, ImportSingleValues the single metadata values and ImportGeobase the geobase. ClearData empties the linelist first when the caller asks for it, and HasData says whether the linelist already carries rows.

THE REPORT

CompareWithImportFile records which sheets and which variables the two files have in common and which they have on their own. NeedReport says whether anything was recorded, FinalizeReport writes the entries into ImportReport, ReportSheets and ReportVariables read them back, and ClearReport empties them. Entries filed along the way leave through HasCheckings and CheckingValues.

Depends on: LLdictionary, Passwords, CustomTable, DataSheet, LLGeo, LLChoices, LLVariables, DropdownLists, HiddenNames, ShowHide, ShowHideLayout, ShowHideStore, ChoiceKeys, ImportMetadata, Checking, BetterArray, ImportReport

Factory

Create #

create

Create an importer from the source linelist workbook

Signature:

Public Function Create(ByVal sourceWkb As Workbook) As LLImporter

Factory method that validates the workbook is not Nothing, then constructs and returns a new LLImporter instance. Called on the predeclared instance.

Parameters:

  • sourceWkb: Workbook. The linelist workbook that will receive imported data.

Returns: LLImporter. A fully initialised instance ready for import operations.

Throws:

  • vbObjectError When the workbook is Nothing.
Diagnostics

HasCheckings #

has-checkings

Whether the import filed anything worth reading

Signature:

Public Property Get HasCheckings() As Boolean

What the import found and what it decided to skip. Every entry is in English and the worksheet the caller writes them to is what the user reads.

Returns: Boolean. True when at least one entry was filed.

CheckingValues #

checking-values

The entries the import filed

Signature:

Public Property Get CheckingValues() As Checking

Returns: Checking. The store, ready to hand to a CheckingOutput.

Data Checks

HasData #

has-data

Whether the workbook contains user-entered data

Signature:

Public Property Get HasData() As Boolean

Scans all HList sheets for rows carrying more filled cells than an untouched row does. LLDataEntry records that figure per sheet under the hidden name blank_row_count when it makes the table. Returns True as soon as a single row has user data. Used to warn the user before clearing or overwriting.

Returns: Boolean. True when at least one HList row has user data.

CurrentLanguage #

current-language

The language this linelist was built in

Signature:

Public Property Get CurrentLanguage() As String

The workbook-level RNG_DictionaryLanguage hidden name, which the build set to the dictionary language. The Metadata sheet of an export carries the same fact under language, so the two compare like with like.

Returns: String. The language code, empty when the workbook carries none.

HasSameLanguage #

has-same-language

Check whether the file being imported is in this linelist's language

Signature:

Public Function HasSameLanguage(ByVal meta As ImportMetadata) As Boolean

Compares the language tag of the file with CurrentLanguage. A file naming no language answers False, which is also the answer for a file carrying no Metadata sheet at all. The caller tells those two apart through the metadata object and says which happened.

The answer used to be kept on the class and read by the three metadata imports, so a caller that skipped this step got False by default and all three did nothing with no diagnostic. The three take the metadata object now and the caller decides whether to call them.

Parameters:

  • meta: ImportMetadata. The metadata of the file being imported.

Returns: Boolean. True when the languages match.

CheckImportFile #

check-import-file

Read the file over before importing it, and file what is wrong

Signature:

Public Function CheckImportFile(ByVal impwb As Workbook, _
                                ByVal meta As ImportMetadata) As Boolean

Every worksheet an export is supposed to carry is looked for, and the export options are read. The dictionary and the choices are errors when they are absent, because the labels and the category lists cannot be compared without them; the other metadata sheets are warnings, because the data still lands.

ONE THING STOPS AN IMPORT

A custom export written with the labels as its header row carries no variable name anywhere, so no column of it can be matched to a column of this linelist. Every row of such a file used to land in "not imported" with nothing said. It is refused here, with the reason.

Parameters:

  • impwb: Workbook. The opened import workbook.
  • meta: ImportMetadata. What that workbook says about itself.

Returns: Boolean. True when the import may go on.

Data Operations

ClearData #

clear-data

Clear all user-entered data from HList and VList sheets

Signature:

Public Sub ClearData()

Iterates over all worksheets in the source workbook. For HList sheets, clears non-formula column values from the ListObject data body. For VList sheets, clears non-formula named range values. Unprotects and re-protects each sheet via Passwords.

ImportData #

import-data

Import data from HList and VList worksheets

Signature:

Public Sub ImportData(ByVal impwb As Workbook, ByVal pasteAtBottom As Boolean, _
                      ByVal meta As ImportMetadata)

Iterates over the import workbook worksheets, matching them to source workbook sheets by name. For HList sheets, delegates to ImportHListSheet. For VList sheets, delegates to ImportVListSheet. Tracks imported sheets and variables, and builds "not imported" report entries for sheets and variables not found in the current workbook.

Parameters:

  • impwb: Workbook. The opened import workbook.
  • pasteAtBottom: Boolean. When True, appends data below existing rows.
  • meta: ImportMetadata. The metadata of that workbook. It says which row of a data sheet holds the variable names, which is row 2 for a file written with the labels above them.

ImportCustomDropdown #

import-custom-dropdown

Import custom dropdown choices from the import workbook

Signature:

Public Sub ImportCustomDropdown(ByVal impwb As Workbook, ByVal pasteAtBottom As Boolean)

Walks the dropdown registry of the source workbook, reads the values the import workbook holds for each dropdown, and writes them back. Silently exits when the import workbook has no Choices sheet or the custom dropdown sheet does not exist.

The walk goes through DropdownLists.AllDropdowns, so each name is the one the registry holds and the one Update takes. Reading the ListObject names off the worksheet instead is what used to break this: the generated name ends with the workbook counter, cutting the five characters of "list_" off the front left that counter attached, and the lookup missed for every dropdown ever created. ChoiceKeys builds the key both ends of the round trip use.

Parameters:

  • impwb: Workbook. The opened import workbook.
  • pasteAtBottom: Boolean. When True, appends choices below existing entries.
Migration Metadata

ImportShowHide #

import-show-hide

Import show/hide choices from a migration workbook

Signature:

Public Sub ImportShowHide(ByVal impwb As Workbook, ByVal meta As ImportMetadata)

Reads the show/hide table of the import workbook and puts each data sheet of the running workbook in the state it describes: visibility, sizes and, on a printed sheet, the direction of each header. Every layer a sheet has is covered - HList and CRF for a data entry sheet, Printed for its companion, VList for an info sheet.

Two faults used to sit here. The visibility read out of the file was written into memory and never reached a sheet, so nothing was ever hidden; and a blank width cell was read as a width of zero and written to the sheet, which is how Excel hides a column, so entries vanished for the opposite reason.

Skips when the file carries no show/hide sheet, and when its show_hide tag asks for no update.

Parameters:

  • impwb: Workbook. The opened migration workbook.
  • meta: ImportMetadata. The metadata of that workbook.

ImportEditableLabels #

import-editable-labels

Import editable label values from a migration workbook

Signature:

Public Sub ImportEditableLabels(ByVal impwb As Workbook, ByVal meta As ImportMetadata)

Reads the import workbook dictionary for editable label values and updates both the worksheet cells and dictionary entries in the source workbook. Skips when the file carries no Dictionary sheet, when that dictionary is not prepared, and when the editable_labels tag asks for no update.

Parameters:

  • impwb: Workbook. The opened migration workbook.
  • meta: ImportMetadata. The metadata of that workbook.

ImportSingleValues #

import-single-values

Import single metadata values from a migration workbook

Signature:

Public Sub ImportSingleValues(ByVal meta As ImportMetadata)

Takes the epiweek start value the file carries and writes it into the RNG_EpiWeekStart hidden name of the source workbook, then recalculates every sheet that carries an epiweek formula. Skips when the file names no value.

THE NAME HAS NO RANGE BEHIND IT

RNG_EpiWeekStart is a HiddenNames entry and HiddenNames writes a string as a quoted literal in the name definition. So the name exists, Names(...) finds it, and asking that name for a range raises 1004. The write used to go through Names(RNG_EpiWeekStart).RefersToRange.Value under a swallow, which means the value has never once landed. The store is what answers it, the same way FormLogicEpiWeek and CustomLinelistFunctions already read and write it.

Parameters:

  • meta: ImportMetadata. The metadata of the workbook being imported.

ImportGeobase #

import-geobase

Import geobase data from a workbook

Signature:

Public Sub ImportGeobase(ByVal impwb As Workbook, Optional ByVal histoOnly As Boolean = False)

Delegates to LLGeo.Import to load geobase data. When not histoOnly, also updates geobase headers in HList worksheets via geo.Update and dictionary geo labels via geo.UpdateDict.

THE SHOW/HIDE SHEET IS NOT TOUCHED

A third call used to write the geo labels into per-sheet tables on the show/hide worksheet. Those tables belonged to the first show/hide design and ShowHideStore deletes them on sight now, so the call read a sheet carrying only the store''s own table and raised 91 on every import. The label reaches the form on its own: ShowHide composes it from the dictionary main label that geo.UpdateDict has just rewritten, every time the form is built.

Parameters:

  • impwb: Workbook. The opened geobase workbook.
  • histoOnly: Optional Boolean. When True, imports only historic geobase data. Defaults to False.
Comparing the two files

CompareWithImportFile #

compare-with-import-file

Compare the labels and the choice lists of the two files

Signature:

Public Sub CompareWithImportFile(ByVal impwb As Workbook)

Walks the dictionary of this linelist and, for every variable the file also carries, files a warning when the two disagree about the label or about the values a choice variable may take.

WHY THE CHOICE LISTS MATTER MORE THAN THE LABELS

A data validation dropdown does not block a programmatic write. CustomTable writes the imported column straight into the cells, so a value the receiving linelist does not offer lands in the cell and stays there: the cell shows something no dropdown can produce, the next hand edit of it is rejected, and every analysis grouping by that variable gains a category the setup never declared. None of that used to appear anywhere.

Run this after ImportData, so the sheets that were touched are known.

Parameters:

  • impwb: Workbook. The opened import workbook.
Import Report

NeedReport #

need-report

Whether the last import had issues

Signature:

Public Property Get NeedReport() As Boolean

Returns True when at least one report entry exists, meaning some sheets or variables were not imported or not touched during the last import.

Returns: Boolean. True when the import report has entries.

FinalizeReport #

finalize-report

Build the "not touched" part of the import report

Signature:

Public Sub FinalizeReport()

Compares source workbook sheets and variables against the imported items tracked during ImportData. Identifies sheets not touched and variables not touched, skipping formula-type variables (case_when, formula, choice_formula). Call after ImportData to complete the report.

ReportSheets #

report-sheets

Report sheet names for the given scope

Signature:

Public Function ReportSheets(ByVal scope As Byte) As BetterArray

Returns a BetterArray of sheet names that were either not imported (ImportReportNotImported) or not touched (ImportReportNotTouched). Returns an empty BetterArray when no entries exist for the scope.

Parameters:

  • scope: Byte. ImportReportNotImported or ImportReportNotTouched.

Returns: BetterArray. Sheet names matching the scope, or empty.

ReportVariables #

report-variables

Report variable entries for the given scope

Signature:

Public Function ReportVariables(ByVal scope As Byte) As BetterArray

Returns a BetterArray where each entry is a Variant array of Array(variableName, sheetName). Returns an empty BetterArray when no entries exist for the scope.

Parameters:

  • scope: Byte. ImportReportNotImported or ImportReportNotTouched.

Returns: BetterArray. Variant arrays of (variableName, sheetName), or empty.

ClearReport #

clear-report

Reset report state

Signature:

Public Sub ClearReport()

Clears all report tracking arrays, drops the diagnostics, and resets the needReport flag. Call before starting a new import session to ensure a clean report.

Internal members (not exported)

Public Accessors

SourceWorkbook #

source-workbook

Source linelist workbook

Signature:

Public Property Get SourceWorkbook() As Workbook

Returns: Workbook. The linelist workbook bound to this importer.

SourceWorkbook #

source-workbook-set

Assign the source linelist workbook

Signature:

Public Property Set SourceWorkbook(ByVal wb As Workbook)

Parameters:

Seal #

seal

Prevent further changes to setup-only properties

Signature:

Public Sub Seal()

GuardNotSealed #

guard-not-sealed

Raise when a setup-only property is written after sealing

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

Diagnostics

ImportCheckings #

import-checkings

The checking store, built on first use

Signature:

Private Function ImportCheckings() As Checking

Returns: Checking. The store this import files into.

FileChecking #

file-checking

File one entry, and never raise on a key already used

Signature:

Private Sub FileChecking(ByVal keyName As String, ByVal label As String, _
                         ByVal scope As Byte)

Checking.Add raises on a key it already holds. An import walks a dictionary, so a key built from a variable name can repeat when the same variable is on two sheets. The counter suffix keeps every entry and keeps the key unique.

Parameters:

Migration Metadata

ApplyOneShowHideLayer #

apply-one-show-hide-layer

Put one layer of one sheet in the state the file describes

Signature:

Private Sub ApplyOneShowHideLayer(ByVal impStore As ShowHideStore, _
                                  ByVal dict As LLdictionary, _
                                  ByVal layer As Byte, _
                                  ByVal sheetName As String, _
                                  ByVal targetSh As Worksheet)

Load carries the sizes and the header directions onto the sheet, and Apply is what actually hides and shows. The choices are written into this workbook's own store as well, so they survive the next time it is opened.

Parameters:

OwnShowHideStore #

own-show-hide-store

The show/hide store of the workbook being written into

Signature:

Private Function OwnShowHideStore() As ShowHideStore

Returns: ShowHideStore. The store, or Nothing when the workbook has no __show_hide worksheet.

CompanionSheet #

companion-sheet

Find a prefixed companion sheet of the running workbook

Signature:

Private Function CompanionSheet(ByVal sheetName As String) As Worksheet

Parameters:

Returns: Worksheet. The sheet, or Nothing when the workbook has none.

BaseTableNameOf #

base-table-name-of

The table name a sheet's PRINTSTART anchor is named after

Signature:

Private Function BaseTableNameOf(ByVal sh As Worksheet) As String

A printed companion stores its table name with the print_ prefix in front and the anchor is named after the base one, so the prefix comes off here.

Parameters:

Returns: String. The table name with no print_ prefix.

RecalculateEpiWeekSheets #

recalculate-epi-week-sheets

Recompute every sheet whose formulas read the epiweek start

Signature:

Private Sub RecalculateEpiWeekSheets()

Each epiweek formula in the workbook reads RNG_EpiWeekStart, so changing that value leaves every one of them showing the old week until the sheet is recalculated. FormLogicEpiWeek does the same after the user picks a day, and this is the same list of sheet tags.

Comparing the two files

SheetWasImported #

sheet-was-imported

Whether one sheet of this linelist took data from the file

Signature:

Private Function SheetWasImported(ByVal sheetName As String) As Boolean

A comparison run on its own, with no import before it, has no list of touched sheets and answers True for every sheet.

Parameters:

Returns: Boolean. True when the sheet is worth comparing.

CompareOneLabel #

compare-one-label

File a warning when the two files give a variable different labels

Signature:

Private Sub CompareOneLabel(ByVal currVars As LLVariables, _
                            ByVal inpVars As LLVariables, _
                            ByVal varName As String)

Parameters:

CompareOneChoiceList #

compare-one-choice-list

File a warning when the two files offer a choice variable different values

Signature:

Private Sub CompareOneChoiceList(ByVal currVars As LLVariables, _
                                 ByVal inpVars As LLVariables, _
                                 ByVal currChoi As LLChoices, _
                                 ByVal impChoi As LLChoices, _
                                 ByVal drop As DropdownLists, _
                                 ByVal varName As String)

choice_formula is left alone: its list name is computed from a formula and the routine that reads that formula lives in the setup, which a linelist does not carry.

Parameters:

ValuesMissingFrom #

values-missing-from

The values of one list that the other does not hold

Signature:

Private Function ValuesMissingFrom(ByVal source As BetterArray, _
                                   ByVal other As BetterArray) As String

Values are matched without regard to case and to surrounding spaces, and they are shown as they were written, so "Yes" against "yes" is reported as a difference a person can act on. The list is capped, because a user reads a handful and the count is what tells them the size.

Parameters:

Returns: String. The missing values, joined, empty when there are none.

HoldsValue #

holds-value

Whether a list holds one value, ignoring case and outer spaces

Signature:

Private Function HoldsValue(ByVal listValues As BetterArray, _
                            ByVal oneValue As String) As Boolean

Parameters:

Returns: Boolean. True when the list holds it.

ChoicesOf #

choices-of

The Choices worksheet of one workbook

Signature:

Private Function ChoicesOf(ByVal wb As Workbook) As LLChoices

Parameters:

Returns: LLChoices. The choices, or Nothing when the workbook carries none.

CustomDropdowns #

custom-dropdowns

The custom dropdown manager of this linelist

Signature:

Private Function CustomDropdowns() As DropdownLists

Returns: DropdownLists. The manager, or Nothing when the workbook has no custom dropdown worksheet.

Import Report

SaveReport #

save-report

Keep the report where it survives the workbook closing

Signature:

Private Sub SaveReport()

The four lists used to live in memory alone and die with this object at the end of the import, while the four ListObjects they belong on sat on __import_rep with nothing writing to them. They are written now, so F_ImportRep can be opened again days later from the advanced form.

A workbook that will not take the store - anything other than a generated linelist - leaves the lists in memory and says so.

FileReportCheckings #

file-report-checkings

Put the four report lists where the user can read them

Signature:

Private Sub FileReportCheckings()

The four lists are the report the form shows. They are filed as checkings as well, so one worksheet carries everything the import found and the user can read it without opening a form.

Private - Source Object Accessors

SourceDictionary #

source-dictionary

Get the dictionary from the source workbook

Signature:

Private Function SourceDictionary() As LLdictionary

Returns: LLdictionary. Dictionary instance from the Dictionary sheet.

PasswordsObject #

passwords-object

Get passwords from the source workbook

Signature:

Private Function PasswordsObject() As Passwords

Returns: Passwords. Passwords instance from the __pass sheet.

GeoObject #

geo-object

Get the geo object from the source workbook

Signature:

Private Function GeoObject() As LLGeo

Returns: LLGeo. Geo instance from the Geo sheet.

Private - Import Helpers

InitializeTracking #

initialize-tracking

Initialize tracking state for a new import session

Signature:

Private Sub InitializeTracking()

The two "not touched" lists are left alone because FinalizeReport replaces them, and the diagnostics are left alone because CheckImportFile runs before this and its entries belong to the same import.

IsReservedSheet #

is-reserved-sheet

Check if a sheet name is reserved (metadata/infrastructure)

Signature:

Private Function IsReservedSheet(ByVal sheetName As String) As Boolean

Parameters:

Returns: Boolean. True when the name matches a reserved sheet.

ShowHideSheetOf #

show-hide-sheet-of

The show/hide worksheet of a workbook, or Nothing

Signature:

Private Function ShowHideSheetOf(ByVal wb As Workbook) As Worksheet

A file written before the internal-sheet rename carries the old trailing name, so the new name is looked for first and the old one answers when it is absent.

Parameters:

Returns: Worksheet. The show/hide worksheet, or Nothing when the workbook has none.

ImportVListSheet #

import-vlist-sheet

Import VList data from a single import sheet to the current sheet

Signature:

Private Sub ImportVListSheet(ByVal currsh As Worksheet, ByVal impsh As Worksheet)

Iterates over the import sheet used range rows, resolving each variable name to a named range on the current sheet. Copies the value when the target cell has no formula. Tracks variables not found as "not imported" report entries.

Parameters:

ImportHListSheet #

import-hlist-sheet

Import HList data from a single import sheet to the current sheet

Signature:

Private Sub ImportHListSheet(ByVal currsh As Worksheet, ByVal impsh As Worksheet, _
                             ByVal pasteAtBottom As Boolean, ByVal headerRow As Long)

Creates a CustomTable from the current sheet ListObject and a DataSheet from the import sheet, then delegates to CustomTable.Import. Tracks columns not found in the current table as "not imported" report entries.

Parameters:

SheetTagOf #

Get the sheet type tag from worksheet-level HiddenNames.

Signature:

Private Function SheetTagOf(ByVal sh As Worksheet, _
                            Optional ByVal nameKey As String = "sheet_type") As String

Parameters:

Returns: String. The sheet type tag (HList, VList, etc.).

Private - Helpers

SheetExists #

sheet-exists

Check if a worksheet exists in a workbook

Signature:

Private Function SheetExists(ByVal wb As Workbook, ByVal sheetName As String) As Boolean

Parameters:

Returns: Boolean. True when the sheet exists.

Private - Error Handling

ThrowError #

throw-error

Raise a class-level exception

Signature:

Private Sub ThrowError(ByVal message As String)

Raises a VBA error using vbObjectError + 516, standardising the source to CLASS_NAME.

Parameters:

Throws:

Used in (13 file(s))