Creating an index for an existing field in an Excel table using VBA Code

2018-02-02 21:25:13

I've been attempting to create an index for an existing field in an Excel table on a worksheet named "CUS_ID-CHANNEL3". There are two columns in the table; "CUS_ID" and "CHANNEL"

The table (purely for experimental purposes) looks like this:

CUS_ID CHANNEL

CustA Business Services

CustB Business Services

CustC Business Services

CustD Business Services

CustE Corporate

CustF Corporate

CustG Corporate

I would like to create an index for the CUS_ID field.

Here's the code I've attempted to use:

Sub CreateIndex()

Dim DBS As DAO.Database

Dim DBSRS As DAO.Recordset

Dim TD As DAO.TableDef

Dim ind As DAO.Index

Set DBS = OpenDatabase("X:\Rowan\Bad Debt Provision\Bad Debt Provision Tool.xlsm", False, True, "Excel 8.0;")

Set TD = DBS.TableDefs("'CUS_ID-CHANNEL3$'")

Set ind = TD.CreateIndex("PrimaryKey")

With ind

.Fields.Append .CreateField("CUS_ID")

.Primary = True

End With

TD.Indexes.Append ind '<-error occurs here

DB