How to Use VBA to Import Data From Excel Into Access

1. Launch Microsoft Office Excel and type 'data1' in A2, and 'data2' in B2. Press 'Ctrl' and 'S' to open the 'Save As' dialog Window and save the workbook in 'C:\Temp\' as 'dataToImport.xlsx.' Click 'Save' and close Excel.
2. Launch Microsoft Office Access, click 'Blank Database' and click the 'Create' button. Click 'Database Tools,' and click 'Visual Basic' to open the VB Editor Window. Click the 'Insert' menu and then click 'Module' to insert a new code module. Click the 'Tools' menu, click 'References,' and check the box next to 'Microsoft Excel
Object Library.'
3. Start by typing the following VBA code to create new sub procedure:Private Sub importExcelData()
4. Type the following to create variables you will use to read Excel:Dim xlApp As Excel.ApplicationDim xlBk As Excel.WorkbookDim xlSht As Excel.Worksheet
5. Type the following to create variables you will use in Access:Dim dbRst As RecordsetDim dbs As DatabaseDim SQLStr As String
6. Type the following to define database objects and also define the Excel workbook to use:Set dbs = CurrentDbSet xlApp = Excel.ApplicationSet xlBk = xlApp.Workbooks.Open('C:\Temp\dataToImport.xlsx')Set xlSht = xlBk.Sheets(1)
7. Create a new table with two columns in Access to import data from Excel. Type the following VBA code to create the table using the 'DoCmd' object:SQLStr = 'CREATE TABLE excelData(columnOne TEXT, columnTwo TEXT)'DoCmd.SetWarnings FalseDoCmd.RunSQL (SQLStr)
8. Open the table you just created by using a the Recordset object. Type the following to open the table and add a new row:Set dbRst = dbs.OpenRecordset('excelData')dbRst.AddNew
9. Type the following to get values from the Excel workbook, save them to your table and update the record:xlSht.Range('A2').SelectdbRst.Fields(0).Value = xlSht.Range('A2').ValuexlSht.Range('B2').SelectdbRst.Fields(1).Value = xlSht.Range('B2').ValuedbRst.Update
10. End your procedure by typing the following VBA code:dbRst.Closedbs.ClosexlBk.CloseEnd Sub
11. Press 'F5' to run the procedure. The data in your Excel workbook has just been imported into your Access table.