How to Connect Excel to SQL Using Visual Basic

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. 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. Save the file as 'C:\MyDatabase.xlsx,' then close the file.
4. Press 'Control,' followed by 'N' to create a new spreadsheet. You'll store your SQL program in this sheet.
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. 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. Click the 'Tools' menu heading, then click 'References.' Place a check in the checkbox labeled 'Microsoft ActiveX Data Objects.' This action makes visible the database objects that Excel needs to run the SQL query.
8. Paste the following program into the new code window.Sub sqlVBAExample()Dim objConnection As ADODB.ConnectionDim objRecSet As ADODB.RecordsetSet objConnection = New ADODB.ConnectionobjConnection.ConnectionString = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myDatabase.xlsx;Extended Properties=''Excel 12.0 Xml;HDR=YES'';'objConnection.OpenSet objRecSet = New ADODB.RecordsetobjRecSet.ActiveConnection = objConnectionobjRecSet.Source = 'Select * From myTable'objRecSet.OpenRange('D10').CopyFromRecordset objRecSetobjRecSet.CloseobjConnection.CloseSet objRecSet = NothingSet objConnection = NothingEnd Sub
9. Click one of the program's statements, then press 'F5' to run the program.
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.