The spreadsheet application Microsoft Excel has Visual Basic, a programming language, built into it. Developers use this language to create structured query language commands, better known as "SQL commands." The general steps in connecting Excel to external data sources through SQL queries include: creating a connection to the database; running the query; transferring the fetched data; and closing the connection. Using VB to run Excel SQL queries lets developers use data from a variety of sources external to Excel. These sources include databases created in Access, SQL Server and other database applications.
- Skill level:
Other People Are Reading
Open Excel, then type a list of names in one column and ages in an adjacent column. Label the tops of these columns with the text "Names" and "Ages." This step creates a database of sample data for your SQL program to fetch. You can create a table with different data if you'd like.
Drag a selection region around the table, then type "MyTable" in the text box to the left of the formula bar. This creates a range name for the sample data, which your program will need to access the data.
Save the file as "C:\MyDatabase.xlsx," then close the file.
Press "Control," followed by "N" to create a new spreadsheet. You'll store your SQL program in this sheet.
Press "Alt," followed by "F11" to enter the VB development environment, commonly abbreviated as the "IDE." Developers write and sometimes execute VB programs in this IDE.
Click the "Insert" menu, then click the "Module" item. This action creates a new code window for you to enter your SQL program listing.
Click the "Tools" menu heading, then click "References." Place a check in the checkbox labelled "Microsoft ActiveX Data Objects." This action makes visible the database objects that Excel needs to run the SQL query.
Paste the following program into the new code window.
Dim objConnection As ADODB.Connection
Dim objRecSet As ADODB.Recordset
Set objConnection = New ADODB.Connection
objConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myDatabase.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Set objRecSet = New ADODB.Recordset
objRecSet.ActiveConnection = objConnection
objRecSet.Source = "Select * From my Table"
Set objRecSet = Nothing
Set objConnection = Nothing
Click one of the program's statements, then press "F5" to run the program.
Press "Alt," and then "F11" to return to the Excel spreadsheet. The spreadsheet will display the results of the SQL connection that your program established. The results include the database table you created earlier.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for