XLD and snb are experts so if they are confused, I stand no chance of helping you. That never stopped me from trying though.
I am not sure what your level of expertise is. From what I see, like the others, I don't think you understand stored procedures. These are SQL routines stored in a database file like Access. When you say database, what is the type SQLLite, SQLServer, MySQL, Access, etc. It matters greatly, ergo requests for such dtails has been made.
It looks to me that you are just wanting to query an Excel file. In either case if Excel or a true database program, one helper said to use ADO. There are several ways to use ADO. Of course to use ADO, it is all in the details, ergo the many requests for more details from you to better help you, not to deride you.
See if this or the next code which is directly related to stored procedures gives you any ideas to accomplish your goals.
'romperstomper, http://www.excelforum.com/excel-programming/665066-send-results-of-sql-query-to-an-array.html
' Tools > References > Microsoft ActiveX Data Objects 2.8 Library
' or
' Tools > References > Microsoft ActiveX Data Objects 6.0 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
Dim wbName As String
Set cn = New ADODB.Connection
' strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";" & _
' "Extended Properties=""Excel 8.0;HDR=Yes;"""
'XLSM files in Excel 2010 via ConnectionStrings.com:
'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;
'Extended Properties="Excel 12.0 Macro;HDR=YES";
'wbName = ActiveWorkbook.FullName
wbName = """" & "\\matpc37\Excel\Test\Test.xlsm" & """"
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & wbName & ";" & _
"Extended Properties=""Excel 12.0 Macro;HDR=Yes;"""
.Open
End With
strQuery = "SELECT * FROM [Sheet1$];"
Set rst = New ADODB.RecordSet
rst.Open strQuery, cn, adOpenStatic, adLockReadOnly, adCmdText
' dump array of data into variable
'varData = rst.GetRows
Worksheets(2).Range("A1").CopyFromRecordset rst
rst.Close
Set rst = Nothing
' cn.Close
Set cn = Nothing
End Sub
For a stored SQL in Access:
Option Explicit
'http://msdn.microsoft.com/en-us/library/office/aa188518%28v=office.10%29.aspx
'http://www.vbaexpress.com/forum/showthread.php?t=24118
'http://www.vbaexpress.com/forum/showthread.php?t=24575
'http://www.vbaexpress.com/forum/showthread.php?t=23783
'http://www.vbaexpress.com/forum/showthread.php?t=26145
Sub Test()
Dim mdbPath As String, dbName As String, cmdText As String
Dim rngDestination As String
'mdbPath = "E:\ADO\NWind2003.mdb" 'change the path here to suit your needs
'mdbPath = "c:\myfiles\edrive\excel\ado\NWind2003.mdb"
mdbPath = "//matpc10/ExcelVBAExamples/ado/NWind2003.mdb"
dbName = "NWind2003_1" 'change the database name here to suit your needs
cmdText = "Aug94" 'change the stored SQL here to suit your needs
rngDestination = "A1" 'change the destination range here to suit your needs
'Clear previous data
Cells.Delete
InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
'Insert other data to the right of A1 with a blank column separating the two
rngDestination = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 2).Address
cmdText = "Sales by Category"
InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
End Sub
' http://www.vbaexpress.com/forum/showthread.php?t=43307
Sub Test_InsertTableWithStoredSQL()
Dim databaseName() As Variant, cmdText() As Variant, i As Integer
'
databaseName = Array("qOne", "qTwo", "qThree")
cmdText() = Array("Aug94", "Order Subtotals", "Sales by Category")
For i = LBound(databaseName) To UBound(databaseName)
Debug.Print databaseName(i), cmdText(i), Range("A" & Rows.Count).End(xlUp).Offset(1).Address
InsertTableWithStoredSQL "c:\myfiles\edrive\excel\ado\NWind2003.mdb", _
CStr(databaseName(i)), CStr(cmdText(i)), Range("A" & Rows.Count).End(xlUp).Offset(1).Address, _
True
Next i
End Sub
Sub InsertTableWithStoredSQL(mdbPath As String, dbName As String, _
cmdText As String, rngDestination As String, _
Optional bFieldNames = True)
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & mdbPath & ";Mode=ReadWrite;Extended Properties=""" _
, """;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Datab" _
, "ase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";J" _
, "et OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Co" _
, "mpact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("" & rngDestination & ""))
.CommandType = xlCmdTable
.CommandText = Array(cmdText)
.Name = dbName
.FieldNames = bFieldNames
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = mdbPath
.Refresh BackgroundQuery:=False
End With
End Sub
Wether Access or other database, the main thing you need to get right is the connection string. ConnectionStrings.com
For more on ADO, search this site or: http://www.erlandsendata.no/english/...php?t=envbadac