Excel function to make SQL-like queries on worksheet data?

by

You can use Get External Data (despite its name), located in the ‘Data’ tab of Excel 2010, to set up a connection in a workbook to query data from itself. Use From Other Sources From Microsoft Query to connect to Excel

Once set up you can use VBA to manipulate the connection to, among other thing, view and modify the SQL command that drives the query. This query does reference the in memory workbook, so doesn’t require a save to refresh the latest data.

Here’s a quick Sub to demonstrate accessing the connection objects

Sub DemoConnection()
    Dim c As Connections
    Dim wb As Workbook
    Dim i As Long
    Dim strSQL As String
    
    Set wb = ActiveWorkbook
    Set c = wb.Connections
    For i = 1 To c.Count
        ' Reresh the data
        c(i).Refresh 
        ' view the SQL query
        strSQL = c(i).ODBCConnection.CommandText
        MsgBox strSQL
    Next
End Sub

More Related Contents:

  1. SQL Query output in VBA is different than in SQL Oracle
  2. Performing SQL queries on an Excel Table within a Workbook with VBA Macro
  3. Using Excel VBA to export data to MS Access table
  4. Accessing SQL Database in Excel-VBA
  5. Using Excel VBA to run SQL query
  6. How do I call an Oracle stored procedure from an Excel VBA script?
  7. Can anyone help me with this SQL Query?
  8. How do I use parameters in VBA in the different contexts in Microsoft Access?
  9. How to return a result from a VBA function
  10. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  11. How to run a SQL query on an Excel table?
  12. Export SQL query data to Excel
  13. How can I combine multiple rows into a comma-delimited list in SQL Server 2005?
  14. Sql Server deterministic user-defined function
  15. What is the difference between function and procedure in PL/SQL?
  16. how to pass parameters to query in SQL (Excel)
  17. How to use system username directly in MS Access query?
  18. Call dynamic SQL from function
  19. Generate sql insert script from excel worksheet
  20. how to transform comma separated column into multiples rows in db2
  21. Solution to “cannot perform a DML operation inside a query”?
  22. Function for week of the month in mysql
  23. Access – compare two tables and update or insert data in first table
  24. Query for getting value from another record in same table and filter by difference greater than a gap threshold
  25. Functions with variable number of input parameters
  26. Function return sys_refcursor call from sql with specific columns
  27. Functions vs Stored Procedures
  28. How to save the result of a SQL query into a variable in VBA?
  29. How to deal with single quote in Word VBA SQL query?
  30. Escaping ‘ in Access SQL

Leave a Comment