Import data from Excel into MS Access [closed]

问题:

I have an excel file with some data, I need to import all the data into MS Access using VBA, and then run a query on it, and finally present the data in a report. How can this be acheived?

回答1:

Reference : http://office.microsoft.com/en-in/excel-help/import-excel-data-to-an-access-database-HP005200852.aspx

Make sure the Microsoft Excel data is in list format: each column has a label in the first row and contains similar facts, and there are no blank rows or columns within the list.

Close the Excel workbook that contains the data you want to use in Microsoft Access.

In Access, open the database where you want to copy the Excel data.

On the Access File menu, point to Get External Data, and then click Import.

In the Import dialog box, click Microsoft Excel in the Files of type box.

In the Look in list, locate the file you want to import, and then double-click the file.

Follow the directions in the Import Spreadsheet Wizard.

Thanks

回答2:

Use docmd.transterspreadsheet as in:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml,tblName, impPath, False, impSheet

where tblName is table to add data to
      impPath is path and file name of excel workbook to import from
      false = no field names in import area (true =field name included in import area)
      sheet = sheet name and range ie "sheet!b5:j15"