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!
SQL = theAnswerToDisplayInCell
But that might not relate to your issue. – Jake Bathman