Excel - VBA object model - ModelTableColumn object .DataType property

Question

Dear Microsoft support,

I have a question ref. to ModelTableColumn.DataType from Excel/VBA object model.
According to official documentaion (https://learn.microsoft.com/en-us/office/vba/api/excel.modeltablecolumn.datatype), this property:

"

Returns the XlParameterDataType enumeration of the ModelTableColumn

".

XlParameterDataType (https://learn.microsoft.com/en-us/office/vba/api/excel.xlparameterdatatype) is an enumeration with values range from -8 (xlParamTypeWChar) to xlParamTypeVarChar (12).

My questions are:

(1) when I'm reading (VBA) ModelTableColumn.DataType values for a table in my model, for some ModelTableColumn objects I see values, which are not in XlParameterDataType range: for example, ModelTableColumn.DataType=130, ModelTableColumn.DataType=20. Is it a bug? Or should any specific type conversion be applied to read value properly? (according to documentaion value is INT32 type - isnt it equal to VBA Long?)

(2) for some ModelTableColumn objects I see that values are not reflecting actual data type of this particular model column: for example, column contains Boolean values (TRUE/FALSE) and ModelTableColumn.DataType = 11 (xlParamTypeTimestamp). So - should values of ModelTableColumn.DataType really reflect actual data type assigned to model column?

Excel version is: Excel 2016 16.0.4393.1000 MSO (16.0.5080.1000) 64-bit.
Model table is created by PowerQuery (based on WorkbookQuery).

Answer 1

I just encountered this same issue in a C# Excel VSTO Workbook project. Unfortunately, the documentation doesn't illuminate the root issue.

Debug.Assert(20 == Globals.ThisWorkbook.Connections["TheQueryName"].ModelTables["TheTableName"].ModelTableColumns["TheColumnName"].DataType);

My setup involves an OLEDB connection to SQL Server. DataType=20 is assigned only to the primary key field. And DataType=130 is assigned to all the other fields.