What does it do? It indicates that our records are in Sheet1. So what happens here? First we run the Execute command with our SELECT query: Having connected to our Data Source Excel Worksheet we can now run a SQL SELECT Query: Excel Connection String (XLS, XLSM etc.).Looking for other Connection Strings to XLS or Access files? Check out. You can find more information on the ADODB.Connection Object on MSDN. The Open command executes the connection. The ConnectionStrings defines the Connection properties, like the path to the Queries File (example above is for ThisWorkbook) or if the first row contains a header (HDR). The Provider is the Drive which is responsible for running the query. "Extended Properties=""Excel 12.0 Xml HDR=YES"" " ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & " " & _ This is the same Driver which runs SQL Queries on MS Access Databases: So what is happening in the macro above? Let us break it down: Connecting to the Data Sourceįirst we need to connect via the ADODB Driver to our Excel Worksheet. ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & " " & "Extended Properties=""Excel 12.0 Xml HDR=YES"" " Set cn = CreateObject("ADODB.Connection") The VBA Code is below: Sub RunSELECT()ĭim cn As Object, rs As Object, output As String, sql as String On the right see my Excel Worksheet and the Message Box with the similar output from my VBA Macro.
Let see how to run a simple SELECT SQL Query in Excel VBA on an example Excel Worksheet. Want to learn how to create a MS Query manually? See my MS Query Tutorial Using SQL in VBA example Want to create SQL Queries directly from Excel instead? See my Excel SQL AddIn