XLNS

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(ByVal sConnection As String, ByVal 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’ AND st.name <> ’sysname’ 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
rsRecordset.MoveNext

Loop

Set rsRecordset = Nothing
cConnection.Close
Set cConnection = Nothing

Set ColumnNames = dColumnNames

End Function

I use this method for a long time now a just reccently discovered nobody else does.

I’ve got 2 tables, lets call them tbMaster ans tbSlave.  Most people use a new vlookup for every column they want to retrieve from tbSlave. What I usually do is use vlookup as an array formula.

For example if I want to get the 4th, 7th and 9th column from tbSlave I select 3 columns in tbMaster and type     =VLOOKUP(tbMaster[[#This Row],[Name]],tbSlave,{4,7,9},0) followed by Ctrl Shift Enter

Besides the normal vlookup disadvantages in my opinion the array solution is better