Linking an Excel Spreadsheet to a Microsoft Access Database

Last Updated on Mon, 27 Jan 2020 | Excel 2002 VBA XML ASP

Vertex42 The Excel Nexus

Get Instant Access

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.

Was this article helpful?

Engram 9 VBA Scripts

» Excel 2002 VBA XML ASP