none
How to create a relationship between 2 tables using VBA (Excel) RRS feed

  • Question

  • So I have two tables, one is called Table1, and the other - Table2. Not so hard, let's assume we have ID fields, a name field, and a contact field. 

    So to create a relationship normally you would join on the ID field, very easy when using the GUI. 

    But how do you do it in VBA? 
    Thursday, April 28, 2016 10:52 AM

Answers

  • Hi

    following way you can add relationship.

    Sub addRelationship()
    
    Dim rel As ModelRelationships
    Dim mo As Model
    Set mo = ActiveWorkbook.Model
    
    Dim PFname As String
    Dim FFname As String
    
    PFname = ">*SpendSortKey*<"
    FFname = ">*SpendSortKey*<"
    
    Dim primaryTableName As String
    Dim primaryFieldName As ModelTableColumn
    
    Dim foreignTableName As String
    Dim foreignFieldName As ModelTableColumn
    
    primaryTableName = "Shuttle_Replica"
    foreignTableName = "SpendSortReplica"
    
    Set rel = mo.ModelRelationships.Add(ForeignKeyColumn:=mo.ModelTables(foreignTableName).ModelTableColumns(FFname), PrimaryKeyColumn:=mo.ModelTables(primaryTableName).ModelTableColumns(PFname))
    End Sub

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 29, 2016 1:40 AM
    Moderator

All replies

  • You have not indicated what version of Excel you are using so the following link will only apply if you have Excel 2013 or Excel 2016. AFAIK it cannot be done with earlier versions of Excel.

    https://support.office.com/en-us/article/Create-a-relationship-between-tables-in-Excel-fe1b6be7-1d85-4add-a629-8a3848820be3?ui=en-US&rs=en-US&ad=US&fromAR=1


    Regards, OssieMac

    Thursday, April 28, 2016 11:09 AM
  • Like this:

     Sub CreateRelationship()
         ActiveWorkbook.Model.ModelRelationships.Add ActiveWorkbook.Model.ModelTables( _
             "Table1").ModelTableColumns("ID"), ActiveWorkbook.Model. _
             ModelTables("Table2").ModelTableColumns("ID")
     End Sub


    Best regards, George

    • Proposed as answer by André Santo Thursday, April 28, 2016 5:20 PM
    • Unproposed as answer by André Santo Thursday, April 28, 2016 5:20 PM
    Thursday, April 28, 2016 12:57 PM
  • Hi

    following way you can add relationship.

    Sub addRelationship()
    
    Dim rel As ModelRelationships
    Dim mo As Model
    Set mo = ActiveWorkbook.Model
    
    Dim PFname As String
    Dim FFname As String
    
    PFname = ">*SpendSortKey*<"
    FFname = ">*SpendSortKey*<"
    
    Dim primaryTableName As String
    Dim primaryFieldName As ModelTableColumn
    
    Dim foreignTableName As String
    Dim foreignFieldName As ModelTableColumn
    
    primaryTableName = "Shuttle_Replica"
    foreignTableName = "SpendSortReplica"
    
    Set rel = mo.ModelRelationships.Add(ForeignKeyColumn:=mo.ModelTables(foreignTableName).ModelTableColumns(FFname), PrimaryKeyColumn:=mo.ModelTables(primaryTableName).ModelTableColumns(PFname))
    End Sub

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, April 29, 2016 1:40 AM
    Moderator