Can I import Excel Data?

Can I import Excel Data?

Author: cg1 excerpt: access911.net
Problem:

Can I import Excel Data?

Answer:

Manual mode: See access help: Help> Import or link data and objects If you manually From workbooks Import or link data in a workbook Before proceeding, make sure that the data in the workbook is arranged in the appropriate form and each field (column) in the workbook) all have the same data type, and each row also has the same field. Open the database, or switch to the "Database" Window of the database. Perform one of the following operations: To import a workbook, point to "get external data" in the "file" menu and click "import ". To connect to a workbook, point to "get external data" in the "file" menu, and click "chain table ". In the "import" (or "Link") dialog box, select "Microsoft Excel (*. xls)" in the "file type" box )". Click the arrow on the right of the "search range" box, select the drive and folder where the workbook file is located, and double-click its icon. Follow the prompts in the import data table wizard dialog box. If you are importing from a workbook of Microsoft Excel 5.0 or later, you can import a worksheet in the workbook. However, it cannot be imported from other multi-workbook files, such as Microsoft Excel 4.0 workbooks. To import these files, you must first save each worksheet as a separate file. Note: You can import or link all data in a workbook, or just data from a specified range of cells. Although you usually create a new table in Microsoft Access for import or link, you can append data to an existing table as long as the title of the workbook matches the field name. Access will try to assign the appropriate data type to the imported fields, but you should check the fields to check whether they are set to the desired data type. For example, in the Access database, the telephone number or zip code field may be imported as a number field, but in Microsoft Access, it should be changed to a text field, because these types of fields are not expected for any calculation. When necessary, check and set field attributes (for example, set the format ). Note that the above article still contains two methods and one extension method: 1. Direct import 2. Use a chain table 3. When you only want to import Excel data to an existing table, you can first link an xls file and then directly use the jet SQL statement to do the following: Insert into [Access Table name] (field1, field2) Select field1, field2 from [XLS linked table]   Programming Method: Use the transferspreadsheet Method Example The following example imports the table to the "employees" table of Microsoft Access within the specified range of the lotus workbook newemps. wk3 and uses the first row of the workbook as the field name. Docmd. transferspreadsheet acimport, 3 ,_ "Employees", "C:/Lotus/newemps. wk3", true, "A1: G12" In Visual Basic, The transferspreadsheet method performs the transferspreadsheet operation. Expression. transferspreadsheet (transfertype, spreadsheettype, tablename, filename, hasfieldnames, range, useoa) Expression is required. Returns the expression of an object in the "applies to" list. Transfertype is optional. Acdatatransfertype can be one of the following acdatatransfertype constants: Acexport Acimport default Aclink If you leave this parameter blank, the default constant (acimport) is used ).   Spreadsheettype is optional. Acspreadsheettype can be one of these acspreadsheettype constants: Acspreadsheettypeexcel3 Acspreadsheettypeexcel4 Acspreadsheettypeexcel5 Acspreadsheettypeexcel7 Acspreadsheettypeexcel8 default Acspreadsheettypeexcel9 default Acspreadsheettypelotuswj2-Japanese version only Acspreadsheettypelotuswk1 Acspreadsheettypelotuswk3 Acspreadsheettypelotuswk4 Note that you can link the data in the Lotus 1-2-3 workbook file, but the data is read-only in Microsoft Access. You can import data from and link to the Lotus. wk4 file, but you cannot export Microsoft Access Data to this spreadsheet format. Microsoft Access no longer supports using this method to import, export, or link to the workbook data of Lotus. wks or Microsoft Excel 2.0. If you leave this parameter blank, the default constant (acspreadsheettypeexcel8) is used ).   Tablename is of the variant type. String expression, which indicates the name of the Microsoft Access Table to which you want to import, export, or link workbook data, alternatively, you can select the query name for Microsoft Access to export the results to the workbook. Filename is of the variant type. String expression, indicating the name and path of the workbook from which you want to import, export, or link. Hasfieldnames can be of the variant type. Use true (-1) to use the first row of the workbook as the field name when importing or linking. Use false (0) to treat the first row of a data table as normal data. If this parameter is left blank, the default value (false) is used ). When you export a Microsoft Access Table or SELECT query data to a workbook, the field name is inserted into the first row of the workbook regardless of the value entered for this parameter. Range is optional. String expression, indicating the valid range or range name of cells in the workbook. This parameter can only be used for import. To import the entire workbook, leave this parameter blank. This parameter must be left blank when exporting to a workbook. If a range is entered, the export fails. Useoa can be of the variant type. Description For more information about how to use this operation and its parameters, see the topic of this operation. Optional parameters in the syntax can be left blank, but must contain commas. If you leave the parameter at the end blank, you do not need to use a comma after the specified last parameter. Note that ActiveX Data Objects (ADO) can also be used to create links. The method is to use the activeconnection attribute of the recordset object.