none
C# DataTable in VBA RRS feed

  • Question

  •  

    I want to use DotNet DataTable in VBA. Is it possible or not. If yes then how??
    Thursday, February 14, 2008 6:41 AM

Answers

  •  

    Actually, this is not upgrading issue. I make a function in C# that return datatable, my concern is how to use this datatable in VBA. Thats my problem. If I use datatable columns as a property then it's look fine but that's not easy too if we got 200 rows in C# datatable. I think if I use datareader regarding datatable in C#, then what happened? What do you think??
    Saturday, February 16, 2008 4:55 AM

All replies

  • Hello,

     

    You could build a helper assembly that exposes some functionality of the ADO.NET classes through wrapper classes, and register it for COM use and then reference it from VBA (Tools->References). I'm not sure this is such a great idea due to its hard work required but it's possible. Here's a small example:

     

     

    C# Code Snippet

    using System;

    using System.Data;

    using System.Runtime.InteropServices;

     

    namespace DotNetDataInterop

    {

        [ComVisible(true)]

        [ClassInterface(ClassInterfaceType.None)]

        public class DataTableWrapper : DataTable, IDataTable

        {

            public DataTableWrapper()

            {

            }

        }

     

        [ComVisible(true)]

        public interface IDataTable

        {

            // These are the properties that System.Data.DataTable defines

      bool CaseSensitive { get; set; }

            string DisplayExpression { get; set; }

            bool HasErrors { get; }

            bool IsInitialized { get; }

        }

    }

     

     

    I hope there's a better way to do it

     

     

    Thursday, February 14, 2008 7:24 AM
  •  

    OK thats fine. I got all the DataTable properties from this way in VBA. but can i directly bind datatable in vba?
    Friday, February 15, 2008 6:52 AM
  •  

    Hi,

     

    I don't think so; or if you can it's not that easy. A DataTable object is disconnected from it's original data source. As far as I know VBA works with RecordSet objects which are connected to the data store. Implementing that interface to access the DataTable (much like a DataTableReader, except that this is read-only) would be a big pain and error prone. Look for adodb.dll (%PROGRAMFILES%\Microsoft.NET\Primary Interop Assemblies\adodb.dll) if you still want to choose this way.

    Why not upgrading to .NET? http://migrationguide.artinsoft.com/default.aspx

    Friday, February 15, 2008 12:58 PM
  •  

    Actually, this is not upgrading issue. I make a function in C# that return datatable, my concern is how to use this datatable in VBA. Thats my problem. If I use datatable columns as a property then it's look fine but that's not easy too if we got 200 rows in C# datatable. I think if I use datareader regarding datatable in C#, then what happened? What do you think??
    Saturday, February 16, 2008 4:55 AM