You didn't say what database, so here's an ADO sample to get data from a workbook into an array (requires a reference to the Microsoft ActiveX Data Objects 2.n library):
Sub GetData()
' Sample demonstrating how to return a recordset from a workbook
Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset, strConn As String
Dim varData As Variant
Set cn = New ADODB.Connection
' strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";" & _
' "Extended Properties=""Excel 8.0;HDR=Yes;"""
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & ActiveWorkbook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""
.Open
End With
strQuery = "SELECT * FROM [Sheet1$];"
Set rst = New ADODB.Recordset
rst.Open strQuery, strConn, adOpenStatic, adLockReadOnly, adCmdText
' dump array of data into variable
varData = rst.GetRows
rst.Close
Set rst = Nothing
' cn.Close
Set cn = Nothing
End Sub
Bookmarks