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

Other Excel Objects

Programming with the Excel object model is generally intuitive if you are familiar with Excel. For example, cell, range, columns, rows, etc. are all referred to by their common names. While the syntax might not be intuitive, the Visual Basic Editor helps you write it (provided that you use early binding). For these examples, assume that you used the earlier code to create an Excel application, workbook, and worksheet. Let's say that you want to put the text "Employee Data" in cell B4 and you want it to be bold with a border around it. You also want that column width to fit the text. You could do this in a number of ways.

The Range Property and Object

Using a Range object and the Range property of a worksheet is probably the most straightforward way of doing this task. Use the Dim statement to create a variable for an Excel range, or simply use xlWs.Range("B4").value = "Employee Data". Your choice depends on what you need to do with the range. Since we have a stated goal of formatting this range, I suggest using the Range object as a variable. The code to do this in Access is in Example 5-6.

Example 5-6. Using the range object

Public Sub GetExcel() Dim xlApp As Excel.Application Dim xlWb As Excel.Workbook Dim xlWs As Excel.Worksheet Dim xlRng As Excel.Range Set xlApp = New Excel.Application xlApp.Visible = True Set xlWb = xlApp.Workbooks.Add Set xlWs = xlWb.Worksheets.Add xlWs.Name = "MyNewWorksheet" Set xlRng = xlWs.Range("B4") With xlRng .Value = "Employee Data" ...
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