Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page