I am trying to make an excel macro that will give me the following function in Excel:
=SQL("SELECT heading_1 FROM Table1 WHERE heading_2='foo'")
Allowing me to search (and maybe even insert) data in my Workbook's Tables using SQL queries.
This is what I have done so far:
Sub SQL()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = "SELECT * FROM [Sheet1$A1:G3]"
rs.Open strSQL, cn
Debug.Print rs.GetString
End Sub
My script works like a charm with hardcoded ranges such as the one in the snippet above. It also works very well with static named ranges.
However, it won't work with either dynamic named ranges or TABLE NAMES which is the most important to me.
The closest I have found of an answer is this guy suffering from the same affliction: http://www.ozgrid.com/forum/showthread.php?t=72973
Help anyone?
Edit
I have cooked this so far, I can then use the resulting name in my SQL queries. The limitation is that I need to know on which sheet the tables are. Can we do something about that?
Function getAddress()
myAddress = Replace(Sheets("Sheet1").Range("Table1").address, "$", "")
myAddress = "[Sheet1$" & myAddress & "]"
getAddress = myAddress
End Function
Thanks!
Public Function GetRange(ByVal sListName As String) As String
Dim oListObject As ListObject
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
For Each ws In wb.Sheets
For Each oListObject In ws.ListObjects
If oListObject.Name = sListName Then
GetRange = "[" & ws.Name & "$" & Replace(oListObject.Range.Address, "$", "") & "]"
Exit Function
End If
Next oListObject
Next ws
End Function
In your SQL use it like this
sSQL = "Select * from " & GetRange("NameOfTable") & ""
found this and it worked for me.
strSQL = "SELECT * FROM DataTable"
'Where DataTable is the Named range
How can I run SQL statements on a named range within an excel sheet?
Just answering the second part of your question about getting the name of the sheet where a table is:
Dim name as String
name = Range("Table1").Worksheet.Name
Edit:
To make things more clear: someone suggested that to use Range on a Sheet object. In this case, you need not; the Range where the table lives can be obtained using the table's name; this name is available throughout the book. So, calling Range alone works well.
I am a beginner tinkering on somebody else's code so please be lenient and further correct my errors. I tried your code and played with the VBA help The following worked with me:
Function currAddressTest(dataRangeTest As Range) As String
currAddressTest = ActiveSheet.Name & "$" & dataRangeTest.Address(False, False)
End Function
When I select data source argument for my function, it is turned into Sheet1$A1:G3 format. If excel changes it to Table1[#All] reference in my formula, the function still works properly
I then used it in your function (tried to play and add another argument to be injected to WHERE...
Function SQL(dataRange As Range, CritA As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim currAddress As String
currAddress = ActiveSheet.Name & "$" & dataRange.Address(False, False)
strFile = ThisWorkbook.FullName
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = "SELECT * FROM [" & currAddress & "]" & _
"WHERE [A] = '" & CritA & "' " & _
"ORDER BY 1 ASC"
rs.Open strSQL, cn
SQL = rs.GetString
End Function
Hope your function develops further, I find it very useful. Have a nice day!
Building on Joan-Diego Rodriguez's routine with Jordi's approach and some of Jacek Kotowski's code - This function converts any table name for the active workbook into a usable address for SQL queries.
Note to MikeL: Addition of "[#All]" includes headings avoiding problems you reported.
Function getAddress(byVal sTableName as String) as String
With Range(sTableName & "[#All]")
getAddress= "[" & .Parent.Name & "$" & .Address(False, False) & "]"
End With
End Function
Hi recently looked into this and had issues referencing the named table (list object) within excel
if you place a suffix '$' on the table name all is well in the world
Sub testSQL()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
' Declare variables
strFile = ThisWorkbook.FullName
' construct connection string
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
' create connection and recordset objects
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
' open connection
cn.Open strCon
' construct SQL query
strSQL = "SELECT * FROM [TableName$] where [ColumnHeader] = 'wibble';"
' execute SQL query
rs.Open strSQL, cn
Debug.Print rs.GetString
' close connection
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
Source: Stackoverflow.com