Importing an Excel Spreadsheet to a Microsoft Access Database

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

In the previous section, you learned how to link your Excel spreadsheet to an Access database. Importing your spreadsheet data is just as easy. You can even use the same VBA procedure you used for linking with one minor change: simply replace the acLink constant with aclmport, and you are done. The following procedure imports the spreadsheet presented in Figure 15-19 (see the previous section) into the Northwind database.

Sub ImportExcel_ToAccess()

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

Set objAccess = New Access.Application

With objAccess

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

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

End Sub

Was this article helpful?

0 0

Post a comment