excel| data can I import Excel data?
Author: cg1 Excerpt from: access911.net
Problem:
Can I import Excel data into
Reply:
Manual method:
Check out Access Help:
Help-> Import or link Data and objects
If you manually
From a spreadsheet
Import or link data in a spreadsheet
Before proceeding, make sure that the data in the spreadsheet must be arranged in the appropriate table format, and that each field (column) of the spreadsheet has the same data type, and each row has the same field.
Open the database, or switch to the Database window of the Open database.
Do one of the following:
To import a spreadsheet, on the File menu, point to Get External data, and then click Import.
To link a spreadsheet, on the File menu, point to Get External data, and then click Link table.
In the import (or link) dialog box, select Microsoft Excel (*.xls) in the File type box.
Click the arrow to the right of the Look in box, select the drive and folder where the spreadsheet file is located, and then double-click its icon.
Follow the instructions in the Import Datasheet wizard dialog box. If you are importing from a Microsoft Excel version 5.0 or later workbook, you can import a worksheet in the workbook. However, you cannot import from other multiple spreadsheets, such as the Microsoft Excel version 4.0 workbook file. To import these files, you must first save each worksheet as a separate file.
Attention
You can import or link all the data in a spreadsheet, or just the data from a specified range of cells. Although users usually create a new table in Microsoft Access to import or link, you can append data to an existing table as long as the spreadsheet column headings match the name of the table field.
Access will attempt to assign the appropriate data type to the imported fields, but you should check the fields to make sure they are set to the desired data type. For example, in an Access database, a phone number or postal code field may be imported as a number field, but it should be changed to a text field in Microsoft access because any calculation of these types of fields is not desirable. You should also check and set field properties, such as formatting, if necessary.
Note that the above article still contains two practices and an extended approach:
1, directly with the import
2, with the link table
3, when you just want to import Excel data to the existing table, you can first link to an XLS file, and then directly with the Jet SQL statement to do:
insert into [Access table name] (field1,field2) Select field1,field2 from [XLS link table]
Programming Method:
Please use the TransferSpreadsheet method
Example
The following example imports a table into a "Employees" table in Microsoft Access in the specified range of Lotus spreadsheet Newemps.wk3 and uses the first row in the spreadsheet as the field name.
Docmd.transferspreadsheet Acimport, 3, _
"Employees", "C:\Lotus\Newemps.wk3", True, "A1:g12"
In Visual Basic, the TransferSpreadsheet method performs a transferspreadsheet operation.
Expression. TransferSpreadsheet (TransferType, Spreadsheettype, TableName, FileName, Hasfieldnames, Range, Useoa)
expression Required. Returns an expression for an object in the Applies To list.
TransferType Optional AcDataTransferType.
AcDataTransferType can be one of the following AcDataTransferType constants:
Acexport
Acimport default
Aclink
If you leave this argument blank, the default constant (Acimport) is used.
Spreadsheettype Optional Acspreadsheettype.
Acspreadsheettype can be one of these Acspreadsheettype constants:
AcSpreadsheetTypeExcel3
AcSpreadsheetTypeExcel4
AcSpreadsheetTypeExcel5
AcSpreadsheetTypeExcel7
AcSpreadsheetTypeExcel8 Default
AcSpreadsheetTypeExcel9 Default
AcSpreadsheetTypeLotusWJ2-only available in Japanese version
AcSpreadsheetTypeLotusWK1
AcSpreadsheetTypeLotusWK3
AcSpreadsheetTypeLotusWK4
Note You can link the data in the Lotus 1-2-3 spreadsheet file, but the data is read-only in Microsoft Access. Can be imported from and linked to Lotus. WK4 files, but you cannot export Microsoft Access data to this spreadsheet format. Microsoft access also no longer supports using this method on Lotus. Import, export, or link WKS or Microsoft Excel version 2.0 spreadsheet data.
If you leave this argument blank, the default constant (ACSPREADSHEETTYPEEXCEL8) is used.
TableName Optional Variant type. A string expression that represents the name of the Microsoft Access table to which you want to import the spreadsheet data, export the spreadsheet data, or link the spreadsheet data, or the Microsoft Access select query whose results you want to export to the spreadsheet.
FileName Optional Variant. A string expression that represents the name and path of the spreadsheet to import from, export to, or link to.
Hasfieldnames optional Variant type. Using True (-1) You can use the first row in the spreadsheet as the field name when you import or link. Use False (0) to treat the first row of the datasheet as plain data. If you leave this argument blank, the default value (False) is applied. When you export a Microsoft Access table or select query data to a spreadsheet, the field name is inserted into the first row of the spreadsheet, regardless of what value you entered for the parameter.
Range is an optional Variant. A string expression that represents the valid range or range name of a cell in a spreadsheet. This parameter can be used only for import. To import an entire spreadsheet, leave this argument blank. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.
Useoa optional Variant type.
Description
For more information about how this action and its parameters are used, see the action topic.
Optional arguments in the syntax allow you to leave blank, but you must include a comma for the parameter. If you leave the argument at the end blank, you do not need to use a comma after the last argument you specify.
Note You can also use ActiveX Data Objects (ADO) to create links. The method is to use the ActiveConnection property of the Recordset object.
