This is more something that I'd like some advice on than a problem. I'd like to get an expert's opinion on the use of a Microsoft.ACE.OLEDB data connection within Excel to perform an SQL query on a table in an open Excel workbook to produce output in the same workbook. I believe it is deemed unprofessional to do this, but it seems to be a really useful thing to be able to do. It seems to have served me well, so I'm interested to hear an explanation as to why it's an inadvisable approach and what the best alternative is. One alternative I've experimented with is using a temporary closed workbook to read the data from before deleting it, but it seems excessive incorporating this procedure into a macro. Here is an example of code I might use to perform an SQL query on an open Excel workbook:
Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim ConnStr As String
Dim StrQuery As String
Dim Output As Worksheet
Set Output = ThisWorkbook.Worksheets("Output")
ConnStr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " & ThisWorkbook.FullName & "; Extended Properties='Excel 12.0 Xml; HDR=YES';"
StrQuery = "SELECT * FROM [Table1$]"
rst.Open StrQuery, cnn, adOpenStatic