How to Connect Excel to SQL Using Visual Basic

Written by darrin koltow
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Connect Excel to SQL Using Visual Basic
Connection strings enable Visual Basic to connect Excel with SQL. (Jupiterimages/ Images)

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:


  1. 1

    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.

  2. 2

    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.

  3. 3

    Save the file as "C:\MyDatabase.xlsx," then close the file.

  4. 4

    Press "Control," followed by "N" to create a new spreadsheet. You'll store your SQL program in this sheet.

  5. 5

    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.

  6. 6

    Click the "Insert" menu, then click the "Module" item. This action creates a new code window for you to enter your SQL program listing.

  7. 7

    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.

  8. 8

    Paste the following program into the new code window.

    Sub sqlVBAExample()

    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"


    Range("D10").CopyFromRecordset objRecSet



    Set objRecSet = Nothing

    Set objConnection = Nothing

    End Sub

  9. 9

    Click one of the program's statements, then press "F5" to run the program.

  10. 10

    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.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.