Expose .NET DataTable properties to VBA via COM Interface

Okay, this probably isn't the most elegant (or complete, at this point) solution; but I think it's the route I am going to go.

Instead of converting the whole thing to an ADODB Recordset (and duplicating any iterations), I just threw out the DataTable class entirely and wrote my own Recordset class as a COM Wrapper for the a generic Data Reader (via the IDataReader interface) and added a new Field class to manage the type conversion and set up Fields as an array of Field (since interop hates generics)

It basically creates a forward-only ADODB Recordset (same limitations) but has the benefit of only loading one row at a time, so the bulk of the data can be handled as managed code until you know what they want to do with it (I'm going to add methods for ToArray, ToAccessDB, ToFile, etc that use the reader) while still allowing the ability to iterate through the Recordset from excel/access/vbscript/vb6 (if that's really what they want to do.. mostly needed that for legacy support anyway) Here is an example, in case anyone else has to do this again; somewhat modified for brevity:

Public Interface IRecordset
    ReadOnly Property CursorPosition As Integer
    ReadOnly Property FieldCount As Integer
    ReadOnly Property Fields As Field()

    Function ReadNext() As Boolean
    Sub Close()
End Interface

 <System.ComponentModel.DesignerCategory("")> _
 <ClassInterface(ClassInterfaceType.None)> _
 <ComDefaultInterface(GetType(IRecordset))> _
 <Guid("E7AFBBB6-CB20-44EC-9CD2-BC70B94CD8B7")> _
 Public Class Recordset : Implements IRecordset : Implements IDisposable
    Private _Reader = Nothing
    Private _FieldCount As Integer = Nothing
    Private _Fields() As Field
    Public ReadOnly Property CursorPosition As Integer Implements IRecordset.CursorPosition...
    Public ReadOnly Property FieldCount As Integer Implements IRecordset.FieldCount...
    Public ReadOnly Property Fields As Field() Implements IRecordset.Fields...

    Friend Sub Load(ByVal reader As IDataReader)
        _Reader = reader
        _FieldCount = _Reader.FieldCount
        _Fields = Array.CreateInstance(GetType(DataColumn), _FieldCount)
        For i = 0 To _FieldCount - 1
            _Fields(i) = New Field(i, Me)
        Next
    End Sub

    'This logic kinda sucks and is dumb.
    Public Function ReadNext() As Boolean Implements IRecordset.ReadNext
        _EOF = Not _Reader.Read()
        If _EOF Then Return False
        _CursorPosition += 1
        For i = 0 To _FieldCount - 1
            _Fields(i)._Value = _Reader.GetValue(i).ToString
        Next
        Return True
    End Function

From here you just need to define some type like Field or Column and add an interop wrapper for that type:

Public Interface IField
    ReadOnly Property Name As String
    ReadOnly Property Type As String
    ReadOnly Property Value As Object
End Interface

<System.ComponentModel.DesignerCategory("")> _
<ClassInterface(ClassInterfaceType.None)> _
<Guid("6230C670-ED0A-48D2-9429-84820DC2BE6C")> _
<ComDefaultInterface(GetType(IField))> _
Public Class Field : Implements IField
    Private Reader As IDataReader = Nothing
    Private Index As Integer = Nothing
    Public ReadOnly Property Name As String Implements IField.Name
        Get
            Return Reader.GetName(Index)
        End Get
    End Property
    Public ReadOnly Property Value As Object Implements IField.Value
        Get
            Return Reader.GetValue(Index)
        End Get
    End Property
    Public ReadOnly Property Type As String Implements IField.Type
        Get
            Return Reader.GetDataTypeName(Index).ToString
        End Get
    End Property
    Sub New(ByVal i As Integer, ByRef r As IDataReader)
        Reader = r
        Index = i
    End Sub
End Class

All of this is rather silly, but it seems to work well.

Note: I've only been using .Net for about 4 days now, so this might be terrible, please feel free to comment on anything extremely stupid I might be doing.