SetupImport

Moves the content of a setup workbook in and out. Check validates the setup sheets, Import reads the Dictionary, Choices, Exports, Analysis and Translations sheets from a file, ImportFromWorkbook reads them from an open workbook, Export writes them out, and Clean empties them. Path and SetExportFolder say where files are read from and written to, LastExportFile names the file the last export produced, ProgressObject takes the label the form shows progress on, and DisplayPrompts decides whether the class puts a message in front of the user.

HOW A SHEET IS MOVED

A sheet travels either as a whole table through CustomTable, or through the domain manager that owns it: LLdictionary, LLChoices, LLExport, Analysis and SetupTranslationsTable. Excel state is held by an ApplicationState for the length of the run, and the worksheets are unprotected and protected again through Passwords.

THREE RULES HOLD ACROSS THE CLASS

A cleanup block reached by falling off the end of the work runs under its own handler, with the failure handler disarmed first.
Every failure handler copies Err.Number, Err.Source and Err.Description into locals as its first three statements. Cleanup calls run On Error Resume Next and clear Err, so an error read after them belongs to the cleanup.
Sheet names are compared without regard to case. Excel matches worksheet names that way itself, so "choices" reaches the same code as "Choices".

Factory helpers

Create #

create

Create a ready-to-use service instance

Signature:

Public Function Create(ByVal importPath As String, Optional ByVal progressDisplay As Object) As SetupImport

Creates a new SetupImport instance and assigns the path and the display through the property setters, which validate both.

The validation is left to the setters on purpose. Create runs on the predeclared default instance, so a validator called here writes its answer (this.hasCaption) onto that default instance and the new object keeps a stale value until a setter fills it in.

Parameters:

importPath: String. Path to the source setup workbook.
progressDisplay: Optional Object. UI element receiving textual updates (must expose Caption or Value).

Returns: SetupImport. Configured service instance.

Public configuration

Path #

path

Retrieve configured import path.

Signature:

Public Property Get Path() As String

Returns: String. The configured import path.

ProgressObject #

progress-object

Retrieve the progress display object.

Signature:

Public Property Get ProgressObject() As Object

Returns: Object. The progress display object.

DisplayPrompts #

display-prompts

Retrieve the display prompts toggle.

Signature:

Public Property Get DisplayPrompts() As Boolean

Returns: Boolean. True when prompts are enabled.

SetExportFolder #

set-export-folder

Provide an explicit folder for exports, bypassing the folder picker.

Signature:

Public Sub SetExportFolder(ByVal folderPath As String)

The folder is consumed by one export. Export clears it when it closes the export workbook, so a second export on the same instance needs a second call to this method.

Parameters:

folderPath: String. The folder path to use for exports.

LastExportFile #

last-export-file

Path of the last export produced by the service.

Signature:

Public Property Get LastExportFile() As String

Returns the full file path of the last export workbook saved by the service. Returns an empty string when no export has occurred, and when the caller supplied its own workbook to Export.

Returns: String. The last export file path, or empty.

Core workflow

Check #

check

Validate the requested import operation before execution.

Signature:

Public Sub Check(ByVal importDictionary As Boolean, _
                 ByVal importChoices As Boolean, _
                 ByVal importExports As Boolean, _
                 ByVal importAnalysis As Boolean, _
                 ByVal importTranslations As Boolean, _
                 Optional ByVal cleanSetup As Boolean = False)

Ensures at least one import option is selected, verifies the import path is configured and the file exists, then attempts to open and immediately close the source workbook to confirm it is readable.

Parameters:

importDictionary: Boolean. Flag requesting dictionary import.
importChoices: Boolean. Flag requesting choices import.
importExports: Boolean. Flag requesting exports import.
importAnalysis: Boolean. Flag requesting analysis import.
importTranslations: Boolean. Flag requesting translations import.
cleanSetup: Optional Boolean. When True, performs a clean-only run. Defaults to False.

Throws:

ProjectError.InvalidArgument When no import option is selected.
ProjectError.ElementNotFound When no file sits at the configured path.
ProjectError.SomethingWentWrong When the source workbook cannot be opened.

Clean #

clean

Clean target worksheets ahead of a fresh import.

Signature:

Public Sub Clean(ByVal pass As Passwords, ByVal sheetsList As BetterArray)

Iterates over the requested sheets, unprotects each one, clears all ListObject data and worksheet comments, then re-protects. A sheet the host workbook does not carry is skipped, which is what Import does with the same list.

Parameters:

pass: Passwords. Password handler for worksheet protection.
sheetsList: BetterArray. Worksheets participating in the clean-up.

Throws:

ProjectError.ObjectNotInitialized When no password handler is supplied.
ProjectError.InvalidArgument When the sheets list is missing or empty.

Import #

import

Perform the setup import from the configured workbook into the host workbook.

Signature:

Public Sub Import(ByVal pass As Passwords, ByVal sheetsList As BetterArray)

Opens the source workbook, iterates over each requested sheet, matches ListObjects between source and host, and delegates import to CustomTable.Import. Handles sheet protection/unprotection and export column alignment via PrepareImport.

PrepareImport renames two Choices columns for the length of the run and PostImport puts the names back. PostImport runs exactly once per call, whichever way the run ends: the success path guards it with its own handler and the failure path checks postImportDone. A second run would leave two columns named "Translated Label" and no "Formula Label".

Parameters:

pass: Passwords. Password handler for worksheet protection.
sheetsList: BetterArray. Worksheets participating in the import.

Throws:

ProjectError.ObjectNotInitialized When no password handler is supplied.
ProjectError.InvalidArgument When the sheets list is missing or empty.

Workbook-driven import

ImportFromWorkbook #

import-from-workbook

Perform the setup import leveraging dedicated worksheet classes.

Signature:

Public Sub ImportFromWorkbook(ByVal pass As Passwords, Optional ByVal sheetsList As BetterArray = Nothing)

Uses domain-specific classes (LLdictionary, LLChoices, LLExport, Analysis, SetupTranslationsTable) to import each worksheet from the source workbook. Falls back to the default sheets list when none is provided.

The sheet list is resolved before anything else, and PrepareImport is handed the resolved list from inside the protected block. PrepareImport exits on a missing list, so handing it the raw argument used to skip the export-row sync on every call that named no sheets - and the run still imported Exports into a host table that was too short.

Parameters:

pass: Passwords. Password handler for worksheet protection.
sheetsList: Optional BetterArray. Worksheets to import. Defaults to all five setup sheets.

Throws:

ProjectError.ObjectNotInitialized When no password handler is supplied.

Workbook driven Export

Export #

export-to-workbook

Export all setup worksheets to a new workbook.

Signature:

Public Sub Export(Optional ByVal outwb As Workbook)

Creates or reuses an export workbook, then exports each setup worksheet (Dictionary, Choices, Exports, Analysis, Translations) using domain-specific managers, along with hidden names and formatting data.

The caller owns any workbook it supplies through outwb. On that path the service writes the sheets and stops: it saves nothing, leaves LastExportFile empty, leaves the workbook open, and removes no sheet from it. A workbook the service creates itself is saved to the configured folder, closed, and stripped of the blank sheets Workbooks.Add gave it.

Parameters:

outwb: Optional Workbook. Pre-existing target workbook. When Nothing, creates a new one.

Internal members (not exported)

State and constants

Class_Initialize #

initialize

Set default state on construction.

Signature:

Private Sub Class_Initialize()

Public configuration

Path #

path-set

Store the path to the setup workbook.

Signature:

Public Property Let Path(ByVal value As String)

Parameters:

value: String. The path to assign.

ProgressObject #

progress-object-set

Store the progress display object reference.

Signature:

Public Property Set ProgressObject(ByVal value As Object)

Parameters:

value: Object. The progress display to store.

Throws:

ProjectError.InvalidArgument When the object exposes neither Caption nor Value.

DisplayPrompts #

display-prompts-set

Toggle UI prompts displayed during operations.

Signature:

Public Property Let DisplayPrompts(ByVal state As Boolean)

When False, suppresses folder-picker dialogs and other interactive prompts. Primarily used by automated tests.

Parameters:

state: Boolean. True to enable prompts.

Application state coordination

ApplicationScope #

application-scope

Guard Excel with the reusable ApplicationState scope.

Signature:

Private Function ApplicationScope() As ApplicationState

EnterBusyState #

enter-busy-state

Apply the busy state when heavy work starts.

Signature:

Private Sub EnterBusyState()

One scope covers a whole operation, including any inner call that enters the busy state again. The depth counter is what makes that work: only the outermost call restores, so an inner return can no longer put Calculation back to automatic in the middle of the work.

LeaveBusyState #

leave-busy-state

Restore Excel configuration when the outermost call completes.

Signature:

Private Sub LeaveBusyState(Optional ByVal silent As Boolean = False)

RestoreApplicationState #

restore-application-state

Restore Excel and release the scope.

Signature:

Private Sub RestoreApplicationState(ByVal silent As Boolean)

The scope is released once it has restored. ApplicationState captures its snapshot once per object, so a released scope is what makes the next operation record the settings as they are then.

Workbook-driven import

ImportDictionaryUsingClass #

import-dictionary

Import dictionary worksheet using LLdictionary.

Signature:

Private Sub ImportDictionaryUsingClass(ByVal pass As Passwords, _
                                       ByVal hostWorkbook As Workbook, _
                                       ByVal importWorkbook As Workbook)

ImportChoicesUsingClass #

import-choices

Import choices worksheet using LLChoices.

Signature:

Private Sub ImportChoicesUsingClass(ByVal pass As Passwords, _
                                    ByVal hostWorkbook As Workbook, _
                                    ByVal importWorkbook As Workbook)

ImportExportUsingClass #

import-exports

Import export specifications via LLExport.

Signature:

Private Sub ImportExportUsingClass(ByVal pass As Passwords, _
                                   ByVal hostWorkbook As Workbook, _
                                   ByVal importWorkbook As Workbook)

ImportAnalysisUsingClass #

import-analysis

Import analysis worksheet content via Analysis class.

Signature:

Private Sub ImportAnalysisUsingClass(ByVal pass As Passwords, _
                                     ByVal hostWorkbook As Workbook, _
                                     ByVal importWorkbook As Workbook)

ImportTranslationsUsingClass #

import-translations

Import translations worksheet using CustomTable and DataSheet.

Signature:

Private Sub ImportTranslationsUsingClass(ByVal pass As Passwords, _
                                         ByVal hostWorkbook As Workbook, _
                                         ByVal importWorkbook As Workbook)

ResolveTranslationsList #

resolve-translations-list

Resolve the translations ListObject from a worksheet.

Signature:

Private Function ResolveTranslationsList(ByVal sheetRef As Worksheet, _
                                         Optional ByVal preferred As ListObject = Nothing) As ListObject

Preparation helpers

PrepareImport #

prepare-import

Ensure the exports and dictionary sheets are aligned prior to import.

Signature:

Private Sub PrepareImport(ByVal pass As Passwords, _
                          Optional ByVal sheetsList As BetterArray, _
                          Optional ByVal forceSync As Boolean = True, _
                          Optional ByVal impStartRow As Long = EXPORT_HOST_START_ROW, _
                          Optional ByVal impStartColumn As Long = EXPORT_HOST_START_COLUMN, _
                          Optional ByVal renameChoices As Boolean = False)

PostImport #

post-import

Restore choices column names after import completes.

Signature:

Private Sub PostImport(ByVal pass As Passwords, _
                        Optional ByVal sheetsList As BetterArray)

Validation helpers

ResolveImportSheets #

resolve-import-sheets

Resolve the requested sheets set, defaulting to core setup worksheets.

Signature:

Private Function ResolveImportSheets(ByVal sheetsList As BetterArray) As BetterArray

DefaultImportSheets #

default-import-sheets

Default sheet list covering the core setup worksheets.

Signature:

Private Function DefaultImportSheets() As BetterArray

SheetListKeys #

sheet-list-keys

Build one lower-cased key string out of a sheets list.

Signature:

Private Function SheetListKeys(ByVal sheetsList As BetterArray) As String

The list is walked once per call and every later question is answered with InStr. The separator sits on both sides of each name, so a short name cannot match inside a longer one.

SheetInKeys #

sheet-in-keys

Answer whether a sheet name sits in a key string.

Signature:

Private Function SheetInKeys(ByVal sheetKeys As String, ByVal sheetName As String) As Boolean

SameSheetName #

same-sheet-name

Compare two worksheet names the way Excel does.

Signature:

Private Function SameSheetName(ByVal leftName As String, ByVal rightName As String) As Boolean

EnsureImportSelection #

ensure-import-selection

Ensure at least one import option is selected.

Signature:

Private Sub EnsureImportSelection(ByVal importDictionary As Boolean, _
                                  ByVal importChoices As Boolean, _
                                  ByVal importExports As Boolean, _
                                  ByVal importAnalysis As Boolean, _
                                  ByVal importTranslations As Boolean, _
                                  ByVal cleanSetup As Boolean)

EnsureImportPathConfigured #

ensure-import-path-configured

Ensure the import path has been configured.

Signature:

Private Sub EnsureImportPathConfigured(ByVal value As String)

ValidatePasswords #

validate-passwords

Validate availability of requested passwords object.

Signature:

Private Sub ValidatePasswords(ByVal pass As Passwords)

EnsureSheetsList #

ensure-sheets-list

Ensure the sheets list is initialised.

Signature:

Private Function EnsureSheetsList(ByVal sheetsList As BetterArray) As BetterArray

ValidateImportPath #

validate-import-path

Confirm the import path string is valid.

Signature:

Private Sub ValidateImportPath(ByVal pathValue As String)

ValidateProgressDisplay #

validate-progress-display

Ensure the progress display object exposes a Caption or Value property.

Signature:

Private Sub ValidateProgressDisplay(ByVal progressDisplay As Object)

An object that carries neither property is refused. The refusal used to sit below a plain Exit Sub, on a label nothing could jump to, so any object at all was accepted and the writers below then wrote to nothing.

TestProperty #

test-property

Test whether an object supports a named property.

Signature:

Private Function TestProperty(ByVal probeObject As Object, _
                              ByRef hasProp As Boolean, _
                              ByVal propName As String) As Boolean

The probe reads the property. Writing to it was how this used to work, so asking a label whether it had a caption erased the caption. A name the probe does not know answers False.

EnsureImportFileExists #

ensure-import-file-exists

Ensure the import workbook file exists before opening.

Signature:

Private Sub EnsureImportFileExists(ByVal filePath As String)

Workbook helpers

EnsureImportWorkbook #

ensure-import-workbook

Obtain the imported workbook, opening it when needed.

Signature:

Private Function EnsureImportWorkbook() As Workbook

EnsureExportWorkbook #

ensure-export-workbook

Obtain or create the export workbook, prompting for a folder when needed.

Signature:

Private Function EnsureExportWorkbook() As Workbook

The sheets the new workbook is born with are captured here, by reference, so the export can drop exactly those at the end. Deleting Worksheets(1) instead assumed one blank sheet and assumed every manager appends after the last one.

CaptureWorksheets #

capture-worksheets

Hold every worksheet of a workbook in a Collection.

Signature:

Private Function CaptureWorksheets(ByVal workbookRef As Workbook) As Collection

RemoveExportBaseSheets #

remove-export-base-sheets

Drop the blank sheets the export workbook was created with.

Signature:

Private Sub RemoveExportBaseSheets(ByVal targetWorkbook As Workbook)

Only a workbook the service created carries captured sheets, so a workbook the caller supplied keeps every sheet it had. The delete stops while one sheet is left, because a workbook cannot hold zero worksheets.

EnsureSavedExport #

ensure-saved-export

Save the export workbook to the configured path.

Signature:

Private Sub EnsureSavedExport()

The format is stated. Without it Excel writes in whatever default format the machine is set to, so a machine set to .xlsb produced a binary payload under an .xlsx name.

DictionaryFormatHeaders #

dictionary-format-headers

Header names the dictionary import carries its formatting for.

Signature:

Private Function DictionaryFormatHeaders() As BetterArray

BuildExportFilePath #

build-export-file-path

Build the stamped export file path from the configured folder.

Signature:

Private Function BuildExportFilePath() As String

Whatever follows the last dot of the host name is dropped, so a host saved as .xlsm or .XLSB no longer carries its extension inside the new name. The stamp carries the time as well as the date, so two exports in one day are two files.

CloseImportWorkbook #

close-import-workbook

Close and release the imported workbook.

Signature:

Private Sub CloseImportWorkbook()

CloseExportWorkbook #

close-export-workbook

Close and release the export workbook.

Signature:

Private Sub CloseExportWorkbook()

The folder is cleared with the workbook, so a second export on the same instance needs a fresh SetExportFolder call.

CloseWorkbook #

close-workbook

Close workbook safely without raising errors.

Signature:

Private Sub CloseWorkbook(ByVal workbookRef As Workbook)

TryWorksheet #

try-worksheet

Resolve a worksheet, answering Nothing when it is absent.

Signature:

Private Function TryWorksheet(ByVal workbookRef As Workbook, ByVal sheetName As String) As Worksheet

TryListObject #

try-list-object

Resolve a ListObject, answering Nothing when it is absent.

Signature:

Private Function TryListObject(ByVal sheetRef As Worksheet, ByVal listName As String) As ListObject

FirstListObject #

first-list-object

Resolve the first ListObject of a worksheet.

Signature:

Private Function FirstListObject(ByVal sheetRef As Worksheet) As ListObject

Worksheet helpers

CleanWorksheetTables #

clean-worksheet-tables

Clean all ListObjects within a worksheet.

Signature:

Private Sub CleanWorksheetTables(ByVal targetSheet As Worksheet)

ClearWorksheetComments #

clear-worksheet-comments

Remove the classic comments from a worksheet.

Signature:

Private Sub ClearWorksheetComments(ByVal targetSheet As Worksheet)

Range.ClearComments covers classic comments. Threaded comments are reached through Worksheet.CommentsThreaded, which Mac Excel does not carry.

ProtectWorksheet #

protect-worksheet

Protect worksheet with default rules.

Signature:

Private Sub ProtectWorksheet(ByVal pass As Passwords, ByVal sheetName As String)

Every protect call in this class comes through here. Passwords.Protect defaults both flags to True, so a bare call left the Choices sheet allowing shapes and row deletion at the end of every import.

Messaging helpers

WriteInfo #

write-info

Display informational text through the progress object.

Signature:

Private Sub WriteInfo(ByVal message As String)

WriteProgress #

write-progress

Update a textual progress bar representation.

Signature:

Private Sub WriteProgress(ByVal percentage As Long)

The percentage is clamped on both sides in plain VBA. A worksheet function call for a two-value minimum is a round trip into Excel, and it left the low side open, so a negative percentage reached String$ and raised error 5.

ProgressPercent #

progress-percent

Turn a done-out-of-total pair into a percentage.

Signature:

Private Function ProgressPercent(ByVal completed As Long, ByVal total As Long) As Long

BuildOpenFailureMessage #

build-open-failure-message

Combine failure details when opening a workbook fails.

Signature:

Private Function BuildOpenFailureMessage(ByVal sourcePath As String, ByVal failureDetails As String) As String

Error handling

ReportAndThrow #

report-and-throw

Report message then raise a project error.

Signature:

Private Sub ReportAndThrow(ByVal errNumber As Long, ByVal message As String)

Parameters:

errNumber: Long. A ProjectError code.
message: String. Text shown to the user and carried by the error.

ThrowError #

throw-error

Raise a ProjectError-based exception.

Signature:

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

The code is typed Long, which is the repo rule: an Enum in a parameter list misbehaves on Mac Excel, and every call site already passes a ProjectError member that widens to Long on its own.

Parameters:

errNumber: Long. A ProjectError code.
message: String. Text carried by the error.

Class_Terminate #

terminate

Cleanup resources on destruction.

Signature:

Private Sub Class_Terminate()

Used in (7 file(s))

HeadlessBuild.bas
SetupHelpers.bas
SetupRibbon.bas
TestApplicationState.bas
ProgressDisplayStub.cls
SetupImportTestFixture.bas
TestSetupImport.bas