Integrating Excel and Access

Pulling Data in with Excel Alone

This next example shows how to do the same thing without involving Microsoft Access. It will look similar to the Access version, though. Open Excel and get into a new Workbook. Rename Sheet1 to Connection Info by double-clicking on the Sheet Name tab, right-click on Sheet2, select Delete, and do the same for Sheet3.

Next, in cell A1 type username, and in cell A2 type password. For cell B1, go to Format → Cells, go to the Protection tab, and uncheck the box for Locked. For cell B2 go to Format → Cells, and on the Protection tab, uncheck the box for Locked, but check the box for Hidden. Still for Cell B2, go to the Font tab, and for Color select White (or whatever background color you are using on your worksheet). Go to Tools → Macros → Visual Basic Editor, or press Alt+F11. Right-click on the VBAProject of your workbook and select Insert → Module. It defaults to Module1; select Insert → Procedure, and enter the code in Example 7-4.

Example 7-4. Procedure to pull data from SQL Server into Excel

Public Sub OpenSQLWriteExcel() Dim adocn As ADODB.Connection Dim adoconnrs As ADODB.Recordset Dim adors As ADODB.Recordset Dim adofld As ADODB.Field Dim ConnString As String Dim xlwb As Excel.Workbook Dim xlws As Excel.Worksheet Dim xlrng As Excel.Range Dim x As Integer Dim UserName As String Dim Password As String UserName = Sheets("Connection Info").Range("B1").Value Password = Sheets("Connection Info").Range("B2").Value Set adoconnrs = New ADODB.Recordset adoconnrs.Open ...