Performing SQL queries on an Excel Table within a Workbook with VBA Macro

by

One thing you may be able to do is get the address of the dynamic named range, and use that as the input in your SQL string. Something like:

Sheets("shtName").range("namedRangeName").Address

Which will spit out an address string, something like $A$1:$A$8

Edit:

As I said in my comment below, you can dynamically get the full address (including sheet name) and either use it directly or parse the sheet name for later use:

ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal

Which results in a string like =Sheet1!$C$1:$C$4. So for your code example above, your SQL statement could be

strRangeAddress = Mid(ActiveWorkbook.Names.Item("namedRangeName").RefersToLocal,2)

strSQL = "SELECT * FROM [strRangeAddress]"

More Related Contents:

  1. SQL Query output in VBA is different than in SQL Oracle
  2. Using Excel VBA to export data to MS Access table
  3. Accessing SQL Database in Excel-VBA
  4. Excel function to make SQL-like queries on worksheet data?
  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. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  10. How to run a SQL query on an Excel table?
  11. Export SQL query data to Excel
  12. how to pass parameters to query in SQL (Excel)
  13. How to use system username directly in MS Access query?
  14. Generate sql insert script from excel worksheet
  15. Access – compare two tables and update or insert data in first table
  16. Query for getting value from another record in same table and filter by difference greater than a gap threshold
  17. How to save the result of a SQL query into a variable in VBA?
  18. How to deal with single quote in Word VBA SQL query?
  19. Escaping ‘ in Access SQL
  20. Somehow it’s not copying [closed]
  21. Query to get only numbers from a string
  22. How to create a Calendar table for 100 years in Sql [closed]
  23. Search for a string in all tables, rows and columns of a DB
  24. How to capitalize the first letter of each word in a string in SQL Server
  25. How do I sort a VARCHAR column in SQL server that contains numbers?
  26. Why do you create a View in a database?
  27. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  28. SQL for ordering by number – 1,2,3,4 etc instead of 1,10,11,12
  29. RODBC Temporary Table Issue when connecting to MS SQL Server
  30. Difference between === null and isNull in Spark DataDrame

Leave a Comment