Linking an Excel Spreadsheet to a Microsoft Access Database

You can link an Excel spreadsheet to a Microsoft Access database by using the TransferSpreadsheet method (refer to the "Retrieving Data with the TransferSpreadsheet Method" section in this chapter for the details on working with this method). The following example procedure links the spreadsheet shown in Figure 15-19 to the Northwind database. After opening the Access database with the OpenCurrentDatabase method, the procedure uses the TransferSpreadsheet method of the Microsoft Access DoCmd object to create a linked table named ExcelSheet from the specified range of cells (A1:D7) located in the mySheet worksheet in the Chap15.xls spreadsheet file. Notice that the -1 argument in the DoCmd statement indicates that the first row of the spreadsheet contains column headings. Next, the procedure opens the linked table in Edit mode, so the user can add or modify data. If you change back to Excel after adding one or more records, you'll notice that the changes made in the linked Access table are immediately available in Excel.

Chap15.xls

MBOl

A

B

C

D

T

1

School No

Equipment Type

Serial Number

Manufacturer

2

A30B100

Workstation

ZTD230898898

IBM

3

ASCI I3100

Monitor

MDT12ZTK-89

■'SON¥

4

A903100

Printer

234-23JXT

LEXMARK

5

A40C100

Workstation

GRT12456232

DELL

6

A40C100

Monitor

TRU145ZDT

NEC

7

A4ÛC100

Printer

TER19ERE

HP

zl\

a

H 4

► n \ Sheet 1 / Sheets / Sheet3 \mySheet/ | i \

1

UM

Figure 15-19: The LinkExceLToAccess VBA procedure links this spreadsheet to the Northwind database in Microsoft Access.

Sub LinkExcel_ToAccess()

Dim objAccess As Access.Application Dim strName As String strName = "Linked_ExcelSheet"

Set objAccess = New Access.Application

With objAccess

.OpenCurrentDatabase "C:\Program Files\Microsoft Office\" _

& "Office\Samples\Northwind.mdb" .DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, strName, _ "C:\Chap15.xls", _ -1, "mySheet!A1:D7" .DoCmd.OpenTable strName, acViewNormal, acEdit End With End Sub

Figure 15-20: A Microsoft Excel spreadsheet can be linked to a Microsoft Access database.
0 0

Post a comment