LLExporter

Exports the data of a linelist into standalone workbooks. Four modes are offered: ExportCustom writes one of the exports defined on the export sheet, ExportMigration writes the whole linelist for a move to another workbook, ExportAnalysis writes the analysis sheets, and ExportGeo writes the geobase. Each mode builds a temporary output workbook, fills it with the data, the metadata and the formatting the mode calls for, then saves it and closes it. CloseAll closes whatever is still open. Create binds the exporter to a workbook the caller holds; CreateFromFile opens a linelist file itself, and the workbook it opened is closed by CloseAll.

WHAT AN EXPORT CARRIES

ExportOptionsFor and MigrationOptions build the options line the metadata sheet records, ExcludedVariablesFor answers the variables a mode leaves out, EpiWeekStart answers the first day of the epidemiological week, and LastExportPassword answers the password the last export was saved under. LastFailure answers what the last export failed on, for a caller writing the failure into the log.

Depends on: LLdictionary, LLVariables, LLExport, Passwords, CustomTable, LLLog, DataSheet, AnaTabIds, LLGeo, LLChoices, HiddenNames, LLTranslation, ShowHide, ShowHideLayout, ShowHideStore, BetterArray, Checking, DropdownLists, ChoiceKeys

Factory

Create #

create

Create an exporter from the source linelist workbook

Signature:

Public Function Create(ByVal sourceWkb As Workbook) As LLExporter

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

Parameters:

  • sourceWkb: Workbook. The linelist workbook containing the data to export.

Returns: LLExporter. A fully initialised instance ready for export operations.

Throws:

  • ProjectError.ObjectNotInitialized When the workbook is Nothing.

CreateFromFile #

create-from-file

Create an exporter on a linelist file, opened here

Signature:

Public Function CreateFromFile(ByVal filePath As String, _
                               Optional ByVal password As String = vbNullString) As LLExporter

Opens the linelist file at the given path, read-only and with the given password, and builds an exporter on it. The workbook opened here belongs to this instance, and CloseAll is what closes it: the export walks write into the source's temp sheet, so the copy in memory changes, and a read-only open with no save is how none of that reaches the file. When the file is already open in this Excel session, the open workbook is used as it stands and stays out of CloseAll's reach.

The caller decides the application state. A linelist carries open events, so an open with events running starts them; the form logic applies its busy state, events suppressed, before calling here.

Parameters:

  • filePath: String. Full path of the linelist file.
  • password: Optional String. The password the file opens with. Defaults to none.

Returns: LLExporter. An instance bound to the opened workbook.

Throws:

  • ProjectError.ElementNotFound When no file sits at the given path.
  • ProjectError.ErrorUnexpectedState When the file does not open with the given password, or when the opened workbook misses a linelist sheet.
Public Accessors

OpenedFromFile #

opened-from-file

Whether this instance opened its source workbook itself

Signature:

Public Property Get OpenedFromFile() As Boolean

True on an instance built by CreateFromFile that opened the file, and that is the instance whose CloseAll closes the source. An instance built on a workbook the caller already held answers False and leaves that workbook open.

Returns: Boolean. True when CloseAll closes the source workbook.

LastExportPassword #

last-export-password

Password used in the last export operation

Signature:

Public Property Get LastExportPassword() As String

Returns the password applied to the most recent custom export, or an empty string when no password was used. Modules display this value to the user after a successful export so they can share it securely.

Returns: String. The password, or vbNullString.

LastFailure #

last-failure

What the last export failed on, in the words of the error itself

Signature:

Public Property Get LastFailure() As String

An export that fails raises, and the caller reads a description that names the method and nothing else, because the error crosses out of this class. The number, the source and the text are kept here before the raise, so the caller can log what actually refused.

Every export empties this on entry, so a value here belongs to the export that just ran. It is empty after an export that worked.

Returns: String. The error number, source and text, or vbNullString.

EpiWeekStart #

epi-week-start

First day of the epidemiological week, as the linelist holds it

Signature:

Public Function EpiWeekStart() As String

RNG_EpiWeekStart is a HiddenNames entry and its definition is a quoted string, so the name has no range behind it. Every reader that went through Names("RNG_EpiWeekStart").RefersToRange raised 1004 into a swallow and answered an empty string, which is why the epiweek tag of every export file was blank. The store is what answers it, and this is what AddMetadataTags writes.

Returns: String. The stored value, empty when the workbook carries no such name.

ExportOptionsFor #

export-options-for

The options line a numbered export would write into its file

Signature:

Public Function ExportOptionsFor(ByVal exportNumber As Long, _
                                 Optional ByVal useFilters As Boolean = False) As String

The third read seam of this class, beside EpiWeekStart and ExcludedVariablesFor. It answers exactly what AddMetadataTags writes into the export_options row, so what a file will say about itself can be read without writing a file.

The line is what lets an import know whether a file can be read back at all. variable names: no marks a file whose header row carries the labels a user reads and no variable name anywhere, and header row: 2 marks one carrying both, names on the second row.

Parameters:

  • exportNumber: Long. Which export definition to read (1-based).
  • useFilters: Optional Boolean. Whether the data would come from the filtered companions. Defaults to False.

Returns: String. The options of that export on one line.

MigrationOptions #

migration-options

The options line an export for migration writes into its file

Signature:

Public Function MigrationOptions() As String

Returns: String. The options of a migration export on one line.

ExcludedVariablesFor #

excluded-variables-for

The variables a numbered export leaves out

Signature:

Public Function ExcludedVariablesFor(ByVal exportNumber As Long) As BetterArray

The selection an export makes before it writes anything: the geo variables of every admin level the "admin levels" cell leaves out, the geo p-code and geo concat variables when the export row turns p-codes off, and the personal identifiers when it turns those off. Reading it costs four column reads at most and writes nothing, so a caller can ask what a file will be missing before asking for the file.

Parameters:

  • exportNumber: Long. Which export definition to read (1-based).

Returns: BetterArray. The variable names the export drops.

Export Methods

ExportCustom #

export-custom

Export a numbered custom export definition

Signature:

Public Function ExportCustom(ByVal exportNumber As Long, _
                             ByVal folderPath As String, _
                             Optional ByVal useFilters As Boolean = False) As String

Creates an output workbook containing filtered data, optional metadata sheets, and optional analysis sheets according to the export definition identified by exportNumber on the Exports sheet. Removes p-codes and personal identifiers if configured, prepares a temp dictionary, sorts it, then populates the output workbook with data sheets and formatting. Saves the workbook with an optional password and returns the file path.

Parameters:

  • exportNumber: Long. Which export definition to use (1-based).
  • folderPath: String. Target folder for the saved file.
  • useFilters: Optional Boolean. When True, exports filtered HList data. Defaults to False.

Returns: String. Saved file path, or vbNullString on failure.

ExportMigration #

export-migration

Export all data for migration to another linelist

Signature:

Public Function ExportMigration(ByVal folderPath As String, _
                                Optional ByVal includeShowHide As Boolean = False, _
                                Optional ByVal keepLabels As Boolean = False) As String

Creates an output workbook with full unfiltered data, metadata sheets, dictionary, choices, translations, and metadata tags for re-import. Optionally includes show/hide state with column widths. No password is applied. Returns the saved file path.

Parameters:

  • folderPath: String. Target folder for the saved file.
  • includeShowHide: Optional Boolean. When True, includes show/hide state. Defaults to False.
  • keepLabels: Optional Boolean. When True, marks editable labels for update on import. Defaults to False.

Returns: String. Saved file path, or vbNullString on failure.

ExportAnalysis #

export-analysis

Export analysis worksheets only

Signature:

Public Function ExportAnalysis(ByVal folderPath As String) As String

Creates an output workbook containing only the four analysis sheets (univariate, time series, spatial, spatio-temporal) with their values, formatting, and graphs. No data or metadata sheets are included. Saves as xlsx without password.

Parameters:

  • folderPath: String. Target folder for the saved file.

Returns: String. Saved file path, or vbNullString on failure.

ExportGeo #

export-geo

Export geobase data to a separate workbook

Signature:

Public Function ExportGeo(ByVal folderPath As String, _
                           Optional ByVal onlyHistoric As Boolean = False) As String

Creates an output workbook and delegates to LLGeo.ExportToFile to write the geobase data. When onlyHistoric is True, exports only the historic subset. The file name includes a date stamp and optionally a "_historic" suffix.

Parameters:

  • folderPath: String. Target folder for the saved file.
  • onlyHistoric: Optional Boolean. When True, exports only the historic geobase. Defaults to False.

Returns: String. Saved file path, or vbNullString on failure.

CloseAll #

close-all

Close the workbooks this instance still holds open

Signature:

Public Sub CloseAll()

Closes any output workbook left open after an export error, preventing orphaned minimized windows, and closes the source workbook when this instance opened it through CreateFromFile. Nothing is saved on either close. Safe to call even when no workbook is open.

Internal members (not exported)

Factory

FindOpenWorkbook #

find-open-workbook

The workbook of this session already open on a path, or Nothing

Signature:

Private Function FindOpenWorkbook(ByVal filePath As String) As Workbook

Parameters:

Returns: Workbook. The open workbook, or Nothing when the path is open nowhere.

MissingLinelistSheet #

missing-linelist-sheet

The first sheet a linelist carries and a workbook misses

Signature:

Private Function MissingLinelistSheet(ByVal wkb As Workbook) As String

The export walks read the dictionary, the export definitions, the passwords, the geobase and the temp sheet, so those five names are what marks a workbook as a linelist here.

Parameters:

Returns: String. The first missing sheet name, or an empty string when all five are there.

InternalSheetOf #

internal-sheet-of

An internal worksheet of a workbook under either of its names

Signature:

Private Function InternalSheetOf(ByVal wkb As Workbook, _
                                 ByVal newName As String, _
                                 ByVal oldName As String) As Worksheet

A linelist generated 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 worksheet, or Nothing when the workbook has neither name.

Public Accessors

SourceWorkbook #

source-workbook

Source linelist workbook

Signature:

Public Property Get SourceWorkbook() As Workbook

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

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()

MarkSourceOpened #

mark-source-opened

Record that this instance opened its source workbook

Signature:

Public Sub MarkSourceOpened()

Set at creation only, by CreateFromFile, before the seal.

GuardNotSealed #

guard-not-sealed

Raise when a setup-only property is written after sealing

Signature:

Private Sub GuardNotSealed(ByVal propName As String)

Parameters:

Export Methods

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_KEY, _
                            Optional ByVal allowOldLayout As Boolean = False) As String

A linelist generated before the sheet metadata moved into hidden names carries no name to read, and ValueAsString answers an empty string rather than raising. The old layout wrote the same two words into a formatted cell instead, so the cell is read when the name is absent. The fallback is asked for, not automatic. AddDataSheets wants it: the sheet type is all it needs and the data is lost without it. AddShowHide does not, because the layer it would then go on to write needs table_name as well, and that key has no old-layout fallback. Turning it on there would trade a layer quietly missing for a raise that costs the whole export.

Parameters:

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

OldSheetTypeCell #

old-sheet-type-cell

The sheet type of a linelist older than the hidden names

Signature:

Private Function OldSheetTypeCell(ByVal sh As Worksheet) As String

Hlist and Vlist wrote the sheet type into the third cell of the first row and the table name into the fourth, both formatted LinelistHiddenCell so nobody would see them. The two words are the ones the hidden name carries today, so the cell is read as it stands and nothing is translated.

This is only ever reached when the worksheet carries no sheet_type name at all. A sheet that has one never comes here, so a modern file cannot be read wrong by a cell that happens to hold something else.

Parameters:

Returns: String. The tag the old layout wrote, or an empty string.

Private - Source Object Accessors

SourceDictionary #

source-dictionary

Get a dictionary from the source workbook

Signature:

Private Function SourceDictionary(ByVal nbExp As Long) As LLdictionary

Parameters:

Returns: LLdictionary. Dictionary instance from the Dictionary sheet.

ExportObject #

export-object

Get the export configuration from the source workbook

Signature:

Private Function ExportObject() As LLExport

Returns: LLExport. Export configuration from the Exports sheet.

PasswordsObject #

passwords-object

Get passwords from the source workbook

Signature:

Private Function PasswordsObject() As Passwords

Built once and held, and answering Nothing rather than raising when the file will not take one.

A LINELIST OLDER THAN THE PROTECTION MATRIX

Passwords.Create validates the sheet and demands T_ProtectedSheets, a table this version added. A linelist generated before it carries T_Keys, the five named ranges and no protection matrix, so the factory refuses a password sheet that is complete for everything an export wants from it. The class itself is not loosened -- it is baked into the workbook rather than imported, so a change there reaches nothing until every workbook is re-pasted by hand. The tolerance lives here instead, in a class that imports.

What the absence costs is small and bounded. An export-all file name is composed without a passwords object at all; a numbered export loses its version suffix and cannot be given the private key. Both are guarded where they read.

Returns: Passwords. The held handler, or Nothing when the file carries none.

ReportGeoWarning #

report-geo-warning

Log what the geobase export got past rather than stopped on

Signature:

Private Sub ReportGeoWarning(ByVal geo As LLGeo)

Read after an export that succeeded. LLGeo fills its LastFailure on a refusal it carried on from -- the headers it could not put back -- and leaves it empty otherwise, so a value here is a warning and never the reason for a failure. On a failure the RecordFailure handler already folds the same account into this class''s own, so nothing is said twice.

Parameters:

ReportPasswordsRefusal #

report-passwords-refusal

Say in the workbook''s log that this file carries no usable passwords

Signature:

Private Sub ReportPasswordsRefusal(ByVal refusal As String)

Written as a warning rather than a failure: the export goes on to write its file, and the user needs to know why the name lost its version suffix and why no export of this file can be password protected.

Parameters:

UserLog #

user-log

The log of the workbook being exported, built once

Signature:

Private Function UserLog() As LLLog

Built on first use and held, the way FilteredData and LLGeo hold theirs. A workbook that will not take a log answers Nothing, and the Nothing is the state rather than a raise.

Returns: LLLog. The held log, or Nothing.

RecordExportFailure #

record-export-failure

Keep in words what an export refused on, before the raise leaves

Signature:

Private Sub RecordExportFailure(ByVal what As String, ByVal atStep As String, _
                                ByVal errNumber As Long, _
                                ByVal errSource As String, _
                                ByVal errMessage As String)

An error loses its words on the way out of this class: the caller reads the name of the method it called and nothing of what actually refused. Every export therefore records its own account here, and the caller reads it back through LastFailure.

ExportGeo carried this alone for a while, which is why a failing migration answered an empty LastFailure and the form had nothing to log.

Parameters:

Returns: LLGeo. Geo instance from the Geo sheet.

Private - Output Workbook Management

CreateOutputWorkbook #

create-output-workbook

Create a new minimized output workbook

Signature:

Private Function CreateOutputWorkbook() As Workbook

Returns: Workbook. The newly created workbook.

SaveWorkbook #

save-workbook

Save and close the output workbook

Signature:

Private Sub SaveWorkbook(ByVal outwb As Workbook, ByVal filePath As String, _
                         ByVal fileFormat As Long, ByVal password As String)

Saves the workbook to the specified path with the given format and optional password, then closes it without saving again.

Parameters:

BuildFilePath #

build-file-path

Build a full file path from folder, name, and extension

Signature:

Private Function BuildFilePath(ByVal folderPath As String, ByVal fileName As String, _
                               ByVal fileExt As String) As String

Parameters:

Returns: String. The assembled file path.

Private - Dictionary Preparation

ExcludedVariables #

excluded-variables

The variables a custom export leaves out

Signature:

Private Function ExcludedVariables(ByVal dict As LLdictionary, ByVal expObj As LLExport, _
                                   ByVal exportNumber As Long) As BetterArray

Reads three cells of the export row and answers the names of the variables the export drops: the geo variables of every admin level "admin levels" leaves out, the geo p-code and geo concat variables when "include p-codes" is off, and every variable flagged as a personal identifier when "include personal identifiers" is off.

ONE LEVEL OWNS THREE ROWS

LLdictionary.AppendGeoLines writes a visible name row, a hidden p-code row and a hidden concat row for each of the four levels, and a level left out drops all three. The health facility rows are left alone: hf and geo_pcode_hf belong to no admin level, and an export keeps them whatever the cell asks for.

WHY THIS READS AND NEVER WRITES

The exclusion used to be done by clearing the export column of the linelist's own Dictionary worksheet, so one export with p-codes off took those variables out of that export for good and turning the flag back on could not bring them back. The dictionary is the file the whole linelist is generated from and it is what a migration ships, so the loss travelled to the next linelist. The names answered here are dropped from the copies the export builds, and the source sheet keeps what the setup put in it.

Parameters:

Returns: BetterArray. The variable names to drop, empty when the export keeps everything.

KeptAdminLevels #

kept-admin-levels

The admin levels one export row asks for

Signature:

Private Function KeptAdminLevels(ByVal cellText As String, _
                                 ByRef someDropped As Boolean) As Boolean()

The "admin levels" cell holds the levels joined with a plus, written admin1+admin2. An empty cell asks for every level.

THE CELL IS HONOURED ONLY WHEN THE WHOLE OF IT READS

A cell holding one chunk this cannot read answers every level, the way an empty cell does. An export that silently drops geo columns over a typo is worse than one that keeps them all, and SetupErrors.CheckExports reports that same cell on the setup side, before a linelist is ever built from it.

Parameters:

Returns: Boolean(). One entry per level, 1 to 4, True for a level to export.

AdminLevelOfChunk #

admin-level-of-chunk

The level one chunk of the "admin levels" cell holds

Signature:

Private Function AdminLevelOfChunk(ByVal chunk As String) As Long

The chunk is trimmed and read without regard to case, so Admin2 and admin2 both answer 2. Anything else answers 0, and the caller reads that as a cell it cannot honour. SetupErrors.IsAdminLevelsCell holds this same rule.

Parameters:

Returns: Long. The level, 1 to 4, or 0 when the chunk holds something else.

GeoLevelOf #

geo-level-of

The admin level one dictionary row belongs to

Signature:

Private Function GeoLevelOf(ByVal controlValue As String, _
                            ByVal mainLabel As String) As Long

LLdictionary.AppendGeoLines writes three rows per level and this reads all three back. The visible name row carries the control geo1 to geo4. The two hidden rows carry the control formula, and their level sits in the main label, written geo_pcode_adm2 or geo_concat_adm2. A row belonging to no admin level answers 0, and the health facility rows are among them: hf and geo_pcode_hf name no level.

Parameters:

Returns: Long. The level, 1 to 4, or 0 when the row names none.

ColumnValues #

column-values

Read a one-column range into a 1-based array

Signature:

Private Function ColumnValues(ByVal rng As Range) As Variant

A range of one cell answers a scalar rather than an array, so that case is built by hand. Everything else is one crossing for the whole column.

Parameters:

Returns: Variant. A 1-based array of the cell values.

WithoutExcludedRows #

without-excluded-rows

Drop the excluded variables from a dictionary block

Signature:

Private Function WithoutExcludedRows(ByVal dictData As BetterArray, _
                                     ByVal excluded As BetterArray) As BetterArray

The block carries its headers on the first row, so the variable name column is found by its header and every row below is kept or dropped on the name it holds. The block is answered unchanged when the export excludes nothing or when the block carries no variable name column.

Parameters:

Returns: BetterArray. The block with the excluded rows removed.

TopRowsOf #

top-rows-of

Copy the first rows of a two-dimensional block

Signature:

Private Function TopRowsOf(ByVal block As Variant, ByVal firstRow As Long, _
                           ByVal lastRow As Long, ByVal firstCol As Long, _
                           ByVal lastCol As Long) As Variant

ReDim Preserve reaches the last dimension alone, so a block that lost rows is copied into a block of the right height instead.

Parameters:

Returns: Variant. A block holding the rows asked for.

PrepareTempDictionary #

prepare-temp-dictionary

Write dictionary data to the temp sheet for export processing

Signature:

Private Sub PrepareTempDictionary(ByVal dict As LLdictionary, _
                                  ByVal expName As String, _
                                  ByVal fullExport As Boolean, _
                                  ByVal excluded As BetterArray)

When fullExport is True, copies the entire dictionary. When False, filters on the export column name to include only rows assigned to that export. The variables the export excludes are dropped from the block in memory. Writes the result to the __temp sheet and adds a blue font tag to mark the dictionary as prepared.

Parameters:

ExportDictionaryFromTemp #

export-dictionary-from-temp

Create an export dictionary from the temp sheet

Signature:

Private Function ExportDictionaryFromTemp(ByVal nbExp As Long) As LLdictionary

Parameters:

Returns: LLdictionary. Dictionary built from the __temp sheet.

SortExportDictionary #

sort-export-dictionary

Sort the export dictionary by table name and export column

Signature:

Private Sub SortExportDictionary(ByVal expDict As LLdictionary, ByVal expName As String)

Parameters:

Private - Metadata and Sheets

AddMetadataSheets #

add-metadata-sheets

Add Metadata, Choices, and Translations sheets to the output workbook

Signature:

Private Sub AddMetadataSheets(ByVal outwb As Workbook, ByVal dict As LLdictionary, _
                              ByVal nbExp As Long)

Parameters:

AddDictionaryTranslations #

add-dictionary-translations

Put the dictionary translations into the output, either layout

Signature:

Private Sub AddDictionaryTranslations(ByVal outwb As Workbook)

The current layout keeps them in Tab_Translations on the LinelistTranslation sheet, and LLTranslation.ExportDictionary writes them out from there onto a sheet called Translations.

THE LAYOUT BEFORE THAT ONE

A linelist older than that table keeps its dictionary translations on a worksheet of its own, already called Translations, and carries no Tab_Translations at all. LLTranslation.Create demands that table and refuses the whole sheet without it, so the export used to stop here.

Skipping is not the answer: the Translations sheet of an export file is what carries every translated label to whatever reads the file back, and an export without one loses all of them silently. So the older layout is read where it actually sits and copied straight across, the same way Metadata and Choices are copied above.

Parameters:

CopyOldTranslationsSheet #

copy-old-translations-sheet

Copy the dictionary translations of an older linelist across

Signature:

Private Sub CopyOldTranslationsSheet(ByVal outwb As Workbook, _
                                     ByVal refusal As String)

The destination name is the one ExportDictionary would have used, so a file built this way is shaped like every other export and needs nothing special to read it back.

Parameters:

ReportTranslationsWarning #

report-translations-warning

Log which layout the dictionary translations came from

Signature:

Private Sub ReportTranslationsWarning(ByVal detail As String)

Parameters:

AddDictionary #

add-dictionary

Add the dictionary to the output workbook

Signature:

Private Sub AddDictionary(ByVal outwb As Workbook, ByVal dict As LLdictionary, _
                          ByVal nbExp As Long, ByVal exportColumn As String, _
                          ByVal excluded As BetterArray)

Exports the dictionary filtered by exportColumn, drops the rows this export excludes from the copy, then marks the output dictionary as prepared with a blue font tag.

WHY THE EXCLUDED ROWS ARE DROPPED HERE

The export column of the source sheet decides what this copy carries, and that column is left as the setup wrote it. So the copy arrives holding the p-codes and the personal identifiers the export leaves out, and the rows are taken off the copy. The file then lists the variables its data sheets carry, and the linelist keeps its own dictionary whole.

Parameters:

DropExcludedFromCopy #

drop-excluded-from-copy

Take the excluded variables off an exported dictionary

Signature:

Private Sub DropExcludedFromCopy(ByVal outDict As LLdictionary, _
                                 ByVal excluded As BetterArray)

The rows are gathered into one range and deleted in a single call, so a dictionary with many identifiers costs one crossing rather than one per row.

Parameters:

AddCustomDropdowns #

add-custom-dropdowns

Add custom dropdowns to the Choices sheet in the output workbook

Signature:

Private Sub AddCustomDropdowns(ByVal outwb As Workbook)

Walks the dropdown registry of the source workbook and writes the values of each custom dropdown onto the output Choices sheet. Silently exits when no custom dropdown sheet exists.

The walk goes through DropdownLists.AllDropdowns, so the key carries the dropdown name the registry holds. The header cell of the ListObject was read before, and that cell holds the name with every space turned into an underscore, so a dropdown called contact type went into the file under a name the import side could not update. ChoiceKeys builds the key both ends use.

Parameters:

AddMetadataTags #

add-metadata-tags

Add metadata tags to the Metadata sheet in the output workbook

Signature:

Private Sub AddMetadataTags(ByVal outwb As Workbook, ByVal scope As Byte, _
                            ByVal exportNumber As Long, _
                            ByVal includeShowHide As Boolean, _
                            ByVal keepLabels As Boolean, _
                            ByVal useFilters As Boolean)

Appends export_type, export_date, editable_labels, show_hide, and epiweekstart tags to the Metadata sheet variable/value table. These tags control import behaviour when the exported file is re-imported.

Parameters:

ExportOptionsTag #

export-options-tag

The options of one export, written as one line the import reads back

Signature:

Private Function ExportOptionsTag(ByVal scope As Byte, ByVal exportNumber As Long, _
                                  ByVal useFilters As Boolean) As String

The Exports worksheet stays in the linelist, so a file has no way of saying how it was written unless the export writes it down. Every option is a name: value pair and the pairs are joined with a pipe, so the import side splits on the pipe and then on the colon.

The two pairs the import cannot work without are variable names and header row. A file whose header row carries labels alone holds no variable names anywhere, so it can never be read back; a file carrying both has its variable names on row 2 and reads back once the import knows to look there.

Parameters:

Returns: String. The options of this export on one line.

OnePair #

one-pair

One name: value pair of the export options line

Signature:

Private Function OnePair(ByVal optionName As String, ByVal optionValue As String, _
                         Optional ByVal whenEmpty As String = "no") As String

An option the Exports row left empty is written as no, so the reader on the other side never has to tell an absent option from a false one. An option whose empty cell means something else says so: "admin levels" left empty asks for every level, and no would read as none of them.

Parameters:

Returns: String. The pair.

AddShowHide #

add-show-hide

Export the show/hide choices with their sizes to the output workbook

Signature:

Private Sub AddShowHide(ByVal outwb As Workbook)

Builds a __show_hide sheet in the output workbook and asks ShowHideStore to provision its table. Every data sheet contributes each layer it has: HList and CRF for a data entry sheet, Printed for its companion, VList for an info sheet. The choices the user made are read out of the source workbook first, so the file carries what the user sees rather than what the dictionary authored.

The CRF layer used to be left out of both ends of the migration, so a CRF sheet lost its visibility and its sizes on every round trip.

Parameters:

SaveOneShowHideLayer #

save-one-show-hide-layer

Write one layer of one sheet into the export store

Signature:

Private Sub SaveOneShowHideLayer(ByVal outStore As ShowHideStore, _
                                 ByVal dict As LLdictionary, _
                                 ByVal layer As Byte, _
                                 ByVal sheetName As String, _
                                 ByVal sourceSh As Worksheet)

Reads the entry list from the dictionary, folds in whatever the user chose in the running workbook, then saves it with the sheet so the sizes and the header directions travel too.

Parameters:

CompanionSheet #

companion-sheet

Find a prefixed companion sheet of the source 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.

ImportCurrentShowHide #

import-current-show-hide

Fold the user's live choices into an entry list

Signature:

Private Sub ImportCurrentShowHide(ByVal entries As ShowHide)

Reads the show/hide store of the source workbook. A workbook that has never had the form opened carries an empty table, and the entry list then keeps the state the dictionary authored.

Parameters:

AddAnalysisSheets #

add-analysis-sheets

Add analysis worksheets to the output workbook

Signature:

Private Sub AddAnalysisSheets(ByVal outwb As Workbook, ByVal afterSheetName As String)

Copies values and formats from the four analysis sheets (univariate, time series, spatial, spatio-temporal) in the source workbook to new sheets in the output workbook. Removes gridlines, then asks AnaTabIds to recreate the named ranges and the charts of each sheet.

WHAT THE ERROR TRAP COVERS

The trap covers the clipboard steps alone. It used to cover the chart writing too, and the chart writing raised on its first statement for every scope, so the export reported success and delivered analysis sheets with no named ranges and no charts. Each scope is written under its own trap now, the failures are collected, and the export refuses to report success while one is held.

Parameters:

Throws:

Private - Data Export

AddDataSheets #

add-data-sheets

Add data to each output sheet from source HList/VList worksheets

Signature:

Private Sub AddDataSheets(ByVal outwb As Workbook, ByVal expDict As LLdictionary, _
                          ByVal useLabels As Boolean, ByVal startLine As Long, _
                          ByVal useFilters As Boolean)

Iterates over the unique sheet names in the export dictionary, reads data from the corresponding source sheet, and writes it to the output sheet. For HList sheets, delegates to CustomTable.Export. For VList sheets, delegates to ExportVListData. Applies formatting to each output sheet.

Parameters:

FilteredTableOf #

filtered-table-of

The filtered companion table of one data entry sheet

Signature:

Private Function FilteredTableOf(ByVal inpsh As Worksheet, _
                                 ByVal inpshNames As HiddenNames) As ListObject

The companion holds the rows the filters leave visible, and the export reads it when the user ticks the filtered box. Three things have to be there: the name in the sheet store, the sheet it names, and the table on that sheet. All three used to be read in one chained line, so a workbook missing any of them raised a bare Excel number and the box the user got named neither the sheet nor the piece that was missing.

Parameters:

Returns: ListObject. The companion table.

Throws:

LabelsForWrittenColumns #

labels-for-written-columns

The label row that lines up with the columns an export wrote

Signature:

Private Function LabelsForWrittenColumns(ByVal expDict As LLdictionary, _
                                         ByVal sheetName As String, _
                                         ByVal headersList As BetterArray, _
                                         ByVal skipped As BetterArray) As BetterArray

The dictionary answers one label per variable, in the same order as the variable names, so the two lists match until the export skips one. Dropping the label of every skipped variable is what keeps the row over its data.

Parameters:

Returns: BetterArray. One label per column written, in column order.

ExportVListData #

export-vlist-data

Export VList data (variable/value pairs) to the output sheet

Signature:

Private Sub ExportVListData(ByVal inpsh As Worksheet, ByVal outsh As Worksheet, _
                            ByVal headersList As BetterArray, _
                            ByVal useLabels As Boolean)

Writes one row per variable: the variable name in column A, its label beside it when the header format asks for labels, then the value.

THE THREE HEADER FORMATS ON A VLIST SHEET

Owner decision, 2026-08-01: the shape is variable | label | value, and the label column is written in the two label formats alone.

What it replaces was wrong in two ways at once. The data start ROW was fed into a column index, so the variable names + variable labels format wrote the pair into columns B and C and left column A empty; and in the same format the variable name was overwritten by the label, so the file carried no variable names at all. LLImporter.ImportVListSheet resolves each row through currsh.Range(varName), so every row of such a file landed in "not imported". Column A now always holds the variable name, whatever the format.

Parameters:

Private - Formatting Helpers

FormatOutputSheet #

format-output-sheet

Apply standard formatting to an output data sheet

Signature:

Private Sub FormatOutputSheet(ByVal outsh As Worksheet)

Sets row heights, column widths, font, header styling, and auto-fits columns. Wrapped in error handling to prevent failures on protected or special sheets.

Parameters:

RemoveGridLines #

remove-gridlines

Remove gridlines from an analysis output sheet

Signature:

Private Sub RemoveGridLines(ByVal sh As Worksheet)

Parameters:

Private - Error Handling

ThrowError #

throw-error

Raise a ProjectError-based exception

Signature:

Private Sub ThrowError(ByVal errNumber As ProjectError, ByVal message As String)

Wrapper around Err.Raise that standardises the source to CLASS_NAME.

Parameters:

Throws:

Used in (17 file(s))