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.
Post a comment