Home > Excel, VBA > VBA function for returning Column names, type as a dictionary

VBA function for returning Column names, type as a dictionary

May 18th, 2011 admin

February 16th, 2010

I needed the column names of a sql server 2008 table.
So I wrote a function who would return a dictionary with the column names as the key and the type as the item.

‘Set Reference to Microsoft Scripting Runtime
‘Set Reference to Microsoft ActiveX Data Objects 2.7 Library

Function ColumnNames(sConnection As String, sTable As String) As Dictionary

   Dim cConnection As ADODB.Connection
   Dim rsRecordset As ADODB.Recordset
   Dim sSQL As String
   Dim dColumnNames As Dictionary

   sSQL = sSQL & “SELECT sc.name AS column_name, st.name AS datatype ”
   sSQL = sSQL & “FROM sysobjects so ”
   sSQL = sSQL & “JOIN syscolumns sc ON so.id = sc.id ”
   sSQL = sSQL & “JOIN systypes st ON sc.xtype = st.xtype ”
   sSQL = sSQL & “WHERE so.xtype = ‘U’ ”
   sSQL = sSQL & “AND st.name <> ’sysname’ ”
   sSQL = sSQL & “AND so.name = ‘” & sTable & “‘”

   Set dColumnNames = New Dictionary

   Set cConnection = New ADODB.Connection

   cConnection.Open sConnection
   Set rsRecordset = New ADODB.Recordset
   Set rsRecordset.ActiveConnection = cConnection

   rsRecordset.Open sSQL

   Do While Not rsRecordset.EOF And Not rsRecordset.BOF

      dColumnNames.Add rsRecordset.Fields(0).Value, rsRecordset.Fields(1).Value


   Set rsRecordset = Nothing
   Set cConnection = Nothing

   Set ColumnNames = dColumnNames

End Function

Source file: M_ColumnNames

Categories: Excel, VBA Tags: ,
Comments are closed.