Class ModelRelationship (Excel VBA)

The class ModelRelationship represents a relationship, currently in the data model, between two tables.

Item

To use a ModelRelationship class variable it first needs to be instantiated, for example

Dim mrel as ModelRelationship
Set mrel = ActiveWorkbook.Model.ModelRelationships(Index:=1)

The following procedures can be used to set variables of type ModelRelationship: ModelRelationships.Add and ModelRelationships.Item.

For Each

Here is an example of processing the ModelRelationship items in a collection.

Dim mrel As ModelRelationship
For Each mrel In ActiveWorkbook.Model.ModelRelationships
	
Next mrel

Active

When True, the relationship is active. When False, this relationship is inactive.

ActiveWorkbook.Model.ModelRelationships(1).Active = True

Delete

Deletes the relationship.

ActiveWorkbook.Model.ModelRelationships(1).Delete

ForeignKeyColumn

Contains the ModelTableColumn object representing the foreign key column on the many side of the one-to-many relationship.

Dim mtcForeignKeyColumn As ModelTableColumn
Set mtcForeignKeyColumn = ActiveWorkbook.Model.ModelRelationships(1).ForeignKeyColumn

ForeignKeyTable

Contains the ModelTable object representing the table on the many side of the one-to-many relationship.

Dim mdltForeignKeyTable As ModelTable
Set mdltForeignKeyTable = ActiveWorkbook.Model.ModelRelationships(1).ForeignKeyTable

PrimaryKeyColumn

Contains the ModelTableColumn object representing the primary key column in the table on the one side of the one-to-many relationship.

Dim mtcPrimaryKeyColumn As ModelTableColumn
Set mtcPrimaryKeyColumn = ActiveWorkbook.Model.ModelRelationships(1).PrimaryKeyColumn

PrimaryKeyTable

Contains the ModelTable object representing the table on the one side of the one-to-many relationship.

Dim mdltPrimaryKeyTable As ModelTable
Set mdltPrimaryKeyTable = ActiveWorkbook.Model.ModelRelationships(1).PrimaryKeyTable

ModelRelationships.Add

Adds a new relationship to the model.

Add (ForeignKeyColumn, PrimaryKeyColumn)

Dim mrel As ModelRelationship
Set mrel = ActiveWorkbook.Model.ModelRelationships.Add(ForeignKeyColumn:=, PrimaryKeyColumn:=)

Arguments

The following arguments are required:

ForeignKeyColumn (ModelTableColumn) - A ModelTableColumn object representing the foreign key column in the table on the many side of the one-to-many relationship.

PrimaryKeyColumn (ModelTableColumn) - A ModelTableColumn object representing the primary key column in the table on the one side of the one-to-many relationship.

ModelRelationships.Count

Returns a Long value that represents the number of ModelRelationship objects in a ModelRelationships object.

Dim lngCount As Long
lngCount = ActiveWorkbook.Model.ModelRelationships.Count

ModelRelationships.DetectRelationships

Detects model relationships in the specified PivotTable object.

DetectRelationships (PivotTable)

PivotTable: The PivotTable in which to detect model relationships.

ActiveWorkbook.Model.ModelRelationships.DetectRelationships PivotTable:=

ModelRelationships.Item

Returns a single object from the ModelRelationships object.

Item (Index)

Index: The index number for the object.

Dim mrel As ModelRelationship
Set mrel = ActiveWorkbook.Model.ModelRelationships(Index:=1)