How to Use VBA to Import Data From Excel Into AccessIn Feburary 2016 19,

In Feburary 2016 19,
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.
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.'
Start by typing the following VBA code to create new sub procedure:Private Sub importExcelData()
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
Type the following to create variables you will use in Access:Dim dbRst As RecordsetDim dbs As DatabaseDim SQLStr As String
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)
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)
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
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
End your procedure by typing the following VBA code:dbRst.Closedbs.ClosexlBk.CloseEnd Sub
Press 'F5' to run the procedure. The data in your Excel workbook has just been imported into your Access table.
In Feburary 2016 19,