2

In VBA I want to query one Excel table using SQL statements and get the results to another Excel table. I need some direction to what to read because I am confused. Excuse me for asking many different questions in one post at once, but the questions cover one subject.

The code below (grabbed from here) looks simple and promissing:

Sub idee()
  ActiveWorkbook.Sheets.Add
  With ActiveSheet.QueryTables.Add(“ODBC;DSN=Excel-bestanden;DBQ=E:TheDatabook.xls”,[H1])
    .CommandText = “SELECT name, number FROM TheData WHERE number =1″
    .Refresh False
  End With
End Sub

However I would like to use there proper connection string (replacing this: ODBC;DSN=Excel-bestanden;DBQ=E:TheDatabook.xls) and replace named range TheData with QueryTable or ListObject table and I would like to get the output to QueryTable or ListObject table.

Improve this question
2
  • 1
    You need to reverse that, so activesheet.querytables(1).commandtext for exampe or activesheet.querytables(1).refresh to refresh it. ADO benefits would be the data in an a more controllable object, you can have a 0 check on your refresh to say whether anything returned, triggers off the refresh when new items added/deleted, filtering, finding etc. Finding out information about database, show tables/queries etc, Reading about ADO would be best, as everyone will use differently in their life, too much to write :) Commented Nov 16, 2015 at 12:52
  • I ended up using the idea presented here: stackoverflow.com/a/33807584/1903793 Commented Aug 14, 2018 at 10:12
Related questions
6
excel vba - query on a spreadsheet
3
Need help in vba-excel and Sql query
3
SQL Query against Excel Spreadsheet
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.