summary
This article describes how to use ActiveX Data Objects (ADO to handle data from the Microsoft Excel spreadsheet. This article also focuses on Excel's grammar issues and restrictions. This article does not discuss OLAP or PivotTable technology, or other special usage of Excel data.
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
303814 HOWTO: USE Adox with Excel Data from Visual Basic or VBA (using Adox Processing Excel data in Visual Basic or VBA)
More information
Introduction The rows and columns in the Microsoft Excel worksheet are very similar to the rows and columns in the database. As long as the user remembers that Microsoft Excel is not a relational database management system, and recognizes the limitations of this fact, Excel and its tools can be stored and analyzed in many cases.
Microsoft ActiveX Data Objects allows us to see the Excel workbook as a database. This article discusses how to achieve this in the following sections:
• Connect to Excel using ADO • Use ADO to search and edit Excel data • Retrieve data source structure from Excel (metadata)
Note: Tests for this article is performed on Microsoft Data Access Components 2.5 on Microsoft Data Access Components (MDAC) 2.5 on Microsoft Windows 2000 systems with Visual Basic 6.0 Service Pack 3 and Excel 2000. Different phenomena that users may observe when using different versions of MDAC, Microsoft Windows, Visual Basic, or Excel, may not be confirmed or discussed.
Connect to ExceLado to connect to ExceLado any of the following two OLE DB providers in the MDAC can be connected to the Excel data file.
• Microsoft Jet Ole DB Provider - or - • Microsoft Ole DB Provider for ODBC Drivers
How to use the Microsoft Jet OLE DB ProviderJet provider only need to connect to an Excel data source: path (including file name), and EXCEL file versions.
JET Provider - Using Connection Strings
DIM CN as adodb.connection
Set cn = new adodb.connection
WITH CN
.Provider = "Microsoft.jet.OleDb.4.0"
.Connectionstring = "Data Source = C: /myfolder/myworkbook.xls;" & _
"Extended Properties = Excel 8.0;"
.Open
End with
Provider version: You must use Jet 4.0 provider; JET 3.51 provider does not support Jet ISAM drivers. If the Jet 3.51 provider is specified, the following error message appears at runtime:
Couldn't Find Installable ISAM.
Excel version: For Excel 95 Workbook (Excel Version 7.0), you should specify Excel 5.0; for Excel 97, Excel 2000 or Excel 2002 (XP) workbook (Excel version 8.0, 9.0, and 10.0), Excel 8.0 should be specified. JET Provider - Use the Data Link Properties dialog
If you use the ADO DATA control or data environment in your application, you will appear
The Data Link Properties dialog box collects the required connection settings.
1. On the Provider tab, select the JET 4.0 provider; Jet 3.51 Provider does not support Jet ISAM drivers. If the Jet 3.51 provider is specified, the following error message appears at runtime:
Couldn't Find Installable ISAM.
2. On the Connection tab, browse to your workbook file. Ignore the "User ID" and "Password" items because these do not apply to the Excel connection. (Unable to open the password-protected Excel file as a data source. This article is related to the details of this topic.) 3. On all tabs, select Extended Properties from the list and click Edit Value. Enter Excel 8.0; use a semicolon (;) to separate it with any other existing items. If this step is ignored, an error message will appear when the connection is tested. This is because if you don't specify separately, the Jet Provider expects the Microsoft Access database. 4. Return to the Connection tab and click Test Connection. A message box will appear to inform you that the test has been successfully completed.
Other Jet Provider Connection Settings
Column Title: By default, the system considers the first line of the Excel data source contains a column header that can be used as a field name. If this is not this, the setting must be turned off, otherwise, the first line of data will "disappear" and is used as a field name. This can be selected by optional
HDR = Settings Add to the connection string
Extend attributes are implemented. Default settings (no need to specify)
HDR = yes. If there is no column title, you need to specify
HDR = NO; Provider will name your fields f1, F2, and so on. because
The extended attribute string now contains multiple values, so you must enclose it with a double quotation number, plus a pair of double quotes, telling Visual Basic as a text value, as shown in the following example (for easy see, Additional spaces have been added).
.Connectionstring = "Data Source = C: /myfolder/myworkbook.xls;" & _
"Extended Properties =" "Excel 8.0; HDR = NO;" ""
Using Microsoft OLE DB Provider for ODBC Drivers for ODBC drivers ("ODBC provider" herein) also can connect to Excel data sources: driver name, and workbook And file name.
Important: Excel's ODBC connection is read-only. ADO record set
The LockType property does not overwrite this connection level setting. If you want to edit data, you must be in the connection string or DSN configuration.
Readonly is set to
False. Otherwise, the following error message will appear:
Operation Must Use An Updateable Query.odbc Provider - Connection Strings with DSN
DIM CN as adodb.connection
Set cn = new adodb.connection
WITH CN
.Provider = "msdasql"
.Connectionstring = "driver = {Microsoft Excel Driver (* .xls)};" & _
"DBQ = C: /myfolder/myworkbook.xls; readonly = false;"
.Open
End with
ODBC Provider - Connection Strings with DSN
DIM CN as adodb.connection
Set cn = new adodb.connection
WITH CN
.Provider = "msdasql"
.Connectionstring = "dsn = myexceldsn;"
.Open
End with
ODBC Provider - Using the Data Link Properties dialog This If you use the ADO Data control or data environment in your application, the Data Link Properties dialog box will appear, collect the required connection settings.
1. On the Provider Tab, select Microsoft Ole DB Provider for ODBC Drivers. 2. On the Connection tab, select the existing DSN you want to use, or choose to use the connection string. This will open the Standard DSN Configuration dialog box to collect the required connection settings. Don't forget to deselect the default read-only setting as described above if needed. 3. Return to the Connection tab and click Test Connection. A message box will appear to inform you that the test has been successfully completed.
Other ODBC Provider Connection Settings
Column Title: By default, the system considers the first line of the Excel data source contains a column header that can be used as a field name. If this is not this, the setting must be turned off, otherwise, the first line of data will "disappear" and is used as a field name. This can be added to the connection string
FirstrowhasNames = set is implemented. Default settings (no need to specify)
FIRSTROWHASNAMES = 1, where
1 = TRUE. If there is no column header, you need to specify
FIRSTROWHASNAMES = 0, where
0 = false; the driver is named f1, F2, and so on. There is no such option in the DSN Configuration dialog box.
However, because there is an error in the ODBC driver, it is currently specified
FIRSTROWHASNAMES is set to work. In other words, the Excel ODBC driver (MDAC 2.1 and higher) always uses the first line of the specified data source as a field name. For additional information about column header errors, click the article number below to see the article in the Microsoft Knowledge Base:
288343 BUG: Excel ODBC Driver Disregards The FivStrowhasNames or Header Setting (Excel ODBC Driver ignore FIRSTROWHASNAMES or HEADER settings)
The number of rows to be scanned: Excel does not provide detailed architectural information about its data as ADO like a relational database. Therefore, the driver must at least scan a few lines of existing data, in order to guess the data type of each column. The default value of "The number of rows to scan" is eight (8) rows. You can specify an integer value from one (1) to the sixteen (16) row, or specify zero (0), scan all existing rows. This can be changed by adding maxScanRows = optional settings in the connection string or change in the DSN Configuration dialog.
The number of lines to be scanned is implemented.
However, since there is an error in the ODBC driver, it is currently specified that the "maxScanrows to be scanned" setting does not work. In other words, the Excel ODBC driver (MDAC 2.1 and later) always scans the first 8 rows in the specified data source to determine the data types of each column.
Additional information about the "Row to Scan" error, including a simple solution, click the article number below to view the article in the Microsoft Knowledge Base:
189897 XL97: Data Truncated to 255 Characters with Excel ODBC Driver (when using the Excel ODBC driver, the data is truncated to 255 characters)
Other settings: If you are using
Data Link Properties dialog box generates a connection string, you may notice that some of the connection strings will be added
Extended attribute settings, but these settings are not absolutely necessary, for example:
... defaultdir = C: / WorkbookPath; driverid = 790; Fil = Excel 8.0; MaxBuffersize = 2048; PageTimeout = 5;
The "Sort Order" error message in the Visual Basic editor When you use some version of MDAC, the following error message will appear when your program is connected to the Excel data source when designing, the Visual Basic design environment:
SELECTED Collating Sequence Not Supported by The Operating System.
This information only appears in the IDE without appearing in a compiled program. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
246167 PRB: Collating Sequence Error Opening AdoDB RecordSet The First Time Against An Excel XLS (First Open ADODB Record Set from Excel XLS Files, Sorting Order Error)
Problems should be considered when using these two OLE DB providers
Messages should be noted when mixing data types
As mentioned above, the ADO must speculate in the data type of each column in the Excel worksheet or range. (This is not affected by the Excel cell format.) If there are both digital values in the same column, there is a text value, there will be serious problems. The Jet and ODBC providers will return data for most types, but for a few data types, the NULL value is returned. If the number of two types of data in this column is equal, the provider will give priority to digital data and abandon text data.
E.g:
• In the scanned eight (8) line, if the column contains five (5) numeric values and three (3) text values, the provider will return five (5) numbers and three (3) empty value. • In the scanned eight (8) line, if the column contains three (3) numeric values and five (5) text values, the provider will return three (3) null values and five (5) Text value. • In the scanned eight (8) row, if the column contains four (4) numeric values and four (4) text values, the provider will return four (4) numbers and four (4) empty value. Therefore, if the column contains different types of values, the only solution is to store the numeric values in the column as text, then use the Visual BasicVal function or equivalent function when needed to convert it back in the client application. digital.
For read-only data, to resolve this issue, set "IMEX = 1" in the "Extended Properties" section of the connection string, enable
Import mode. This will force usage
ImportMixedTypes = Text Registry Settings. However, in this mode, an unexpected result may occur when performing an update operation. For additional information about this setting, click the article number below to see the article in the Microsoft Knowledge Base:
194124 PRB: EXCEL VALUES RETURNED AS NULL Using Dao OpenRecordset (When using DAO OpenRecordset, the return value is NULL)
Unable to open password-protected workbook
If your Excel workbook is password protection, even if the correct password is provided in the connection settings, it cannot be opened to access its data unless the workbook file is opened in the Microsoft Excel application. If you try to do this, the following error message will appear:
Could Not Decrypt File.
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
211378 xl2000: "Could Not Decrypt File" error with password protected file (When password-protected file, "unknovable file" error)
Use ADO to search and edit Excel data This section discusses two ways to process Excel data:
• How to select data, - and - • How to change the data
How to select data for several ways to select data. you can:
• Use the code to select Excel data. • Use the ADO DATA control to select Excel data. • Use the data environment command to select Excel data.
Use the code to select Excel data Excel data may be included in the following objects in the workbook:
• Whole worksheet. • Named units on the worksheet. • Unnamed units on the worksheet.
Designated worksheet
To specify a worksheet as a recording source, use the name of the worksheet, with a dollar character, and enclose it by square brackets: for example:
StrQuery = "SELECT *" "
It is also possible to separate the worksheet name using the tall square quotation character (`) under the keyboard. E.g:
strquery = "SELECT * FROM` Sheet1 $ `"
Microsoft recommends using square brackets, which is a standard convention for indicating unknown database object names. If you omit all the dollar symbols and square brackets, or only omit the US dollar symbol, the following error message will appear:
... The Jet Database Engine Could Not Find The Specified Object If the dollar symbol is used, the square bracket will appear, and the following error message will appear:
SYNTAX ERROR in from Clause.
If you try to use normal single quotes, the following error message will appear:
SYNTAX ERROR in Query.Incomplete Query Clause.
Specify a naming area
To specify a named cell area as a record source, simply use the definition name. E.g:
strquery = "SELECT * from myrange"
Specify the unnamed area to specify the unnamed cell area as the recording source, add the area represented by the standard Excel row / column representation after the work table name, and enclose it with square brackets. E.g:
Strquery = "SELECT * FROM [Sheet1 $ A1: B10]"
Taste When you specify a worksheet: Provider considers that the data table begins with the least left non-empty cells above the specified worksheet. In other words, the data table can start from line 3, and C, which is no problem. However, in this case, a worksheet title cannot be typed in the A1 cell above the left of the data. Task For the specified area: Specify a worksheet as a recording source, the provider adds a new record to the following (if there is accessible space). When a specified area (naming area or unnamed area) is specified, JET also adds a new record to the existing record in the area (if available space). However, if the query is re-executed on the original area, the result set does not include a new record that is added to the area. When using the 2.5 version of MDAC, if the naming area is specified, the new record cannot be added to the boundaries defined by the area, otherwise the following error message will appear:
Cannot Expand named Range.
Select Excel data in AdoDC using the ADO DATA control
Properties dialog
After the Connection Settings of the EXCEL data source is specified, click
Record the source tab. If the selected CommandType is adcmdtext,
In the Command Text dialog box, enter a SELECT query according to the syntax described above. If the selected CommandType is adcmdtable, and uses the Jet Provider, the name of the available naming area and worksheet in the selected workbook is displayed in the drop-down list, and the naming area is in front.
This dialog will correctly add the US dollar sign after the worksheet name, but will not add the necessary square brackets. So if you only choose a worksheet name, and click
OK, the following error message will appear:
SYNTAX ERROR in from Clause.
You must manually add square brackets before and after the worksheet name. (This combo box allows editing.) If you are using an ODBC provider, you can only see the named area in this drop-down list. However, you can manually enter a worksheet name with the correct separator.
Create a new one after using the data environment command to select the Excel data to set the data environment of the Excel data source.
Command object. If you choose
SQL statement as
The data source can enter a query in the text box in the text box in the text box in the text box. If you choose
Database object
Data source, please select in the first drop-down list
table. If you are using a Jet Provider, the name of the available naming area and worksheet in the selected workbook will appear in the drop-down list, and the named area is in front. (When you select a worksheet name from this location, you don't need to add square brackets as before and after using the ADO Data control.) If you are using an ODBC provider, you can only see named in this drop-down list. area. However, you can manually enter the worksheet name. How to change Excel data: editing, add, and delete
edit
You can use a normal ADO method to edit Excel data. The recording set field containing the EXCEL formula (in "=" start) in the Excel worksheet is read-only and cannot be edited. Remember that the ODBC connection of Excel is read-only unless otherwise specified in connection settings. See "Using Microsoft Ole DB Provider for ODBC Drivers" above.
Add to
If there is a free space, you can add a record to an Excel record source. However, if the new record is added to the original designated area, then these records are not seen when the query is re-query the originally specified area. See "Task For Note" as in the specified area "section.
In some cases, use ADO
Recordset object
AddNew and
When the update method inserts the new data line into the Excel table, the ADO may insert the data value into the error column. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
314763 FIX: ADO INSERTS DATA INTO WRONG COLUMNS in Excel (ADO Insert Data into Excel)
delete
When you delete Excel data, the restricted restriction is more than the data is removed from the relational data source. In the relational database, "line" does not have other meaning except a "record"; but in the Excel worksheet. You can delete the value in the field (cell). But you can't:
1. Delete a whole record once, otherwise the following error message will appear:
DELETING DATA IN A Linked Table is not supported by this ISAM.
A record can only be removed by clearing the content of each field, respectively. 2. Delete the value in the cell containing the Excel formula, otherwise the following error message will appear:
Operation is not allowed in this context.
3. Although the data that has been deleted in the spreadsheet is now the row is now blank, it cannot be deleted, and the recordset will continue to display empty records corresponding to these spaces.
Taste When using ADO editing Excel data: When using ADO inserting text data in Excel, there is a single quotes in front of the text value. This may result in problems when new data is processed later.
Retrieving data source structure (metadata) from Excel uses ADO to retrieve data about the structure of Excel data sources (tables and fields). Although the useful information of the same number (very little) field is returned at least when using two OLE DB providers, there is still a fine difference between the two. Can use ADO
CONNECTION object
OpenSChema method to retrieve these metadata. This method returns an ADO
Recordset object. You can also use more powerful Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (Adox) libraries to retrieve metadata. But for Excel data sources ("Table" is actually a worksheet or naming area, and "field" belongs to several simple data types), although Adox is more powerful, but no use of martial arts. The query table information is in the various objects provided by the relational database (table, view, stored procedures, etc.), and the Excel data source only provides objects equivalent to the table, which consists of worksheets and defined naming areas in the specified workbook. . The naming area is considered "table", and the worksheet is considered "System Table". In addition to the "Table_Type" attribute, it is less useful for table information. Use the following code to retrieve a list of available tables in your workbook:
SET RS = cn.openschema (adschematables)
The record set returned by the JET Provider contains nine (9) fields, but only four (4) fields have data:
• Table_name • Table_Type ("Table", or System Table ") • Date_created • Date_Modified For a given table, the two date fields always display the same value, which is" last modified date ". In other words, "DATE_CREATED" is unreliable.
The record set returned by the ODBC provider also contains nine (9) fields, but only three (3) fields have data:
• Table_catalog, the folder where the workbook is located. • Table_name. • Table_Type, as described above. For example, according to the ADO document, just
The following additional conditions can be retrieved in the OpenSchema method, you can retrieve a list of worksheets:
SET RS = CN.OpenSchema (Adschematables, Array (Empty, Empty, Empty, "System Table"))
But unfortunately, for Excel data sources, if the MDAC version used is above 2.0, no matter which provider is used, this method is not available.
Query Field Information EXCEL Data Source is one of the following data types:
• Digital (ADO Data Type 5, Addouble) • Currency (ADO Data Type 6, Adcurrency) • Logic or Boolean (ADO Data Type 11, Adboolean) • Date (use Jet, for ADD Data Type 7, Addate; use ODBC Data type 135, addbtimestamp) • Text (an ADO AD ... CHAR type, for example, 202, advarchar; 200, advarwchar, or similar type) For numeric columns, returned Numeric_PRecision is always 15 (which is excel) Maximum accuracy); For text columns, the return type of Character_Maximum_length is always 255 (which is the maximum display width of the text in the Excel column, but not the maximum length). except
Data_type properties, retrieving too many useful field information. Use the following code to retrieve a list of available fields in the table: SET RS = cn.openschema (Adschematables, Array (Empty, Empty, "TableName", EMPTY))
The record set returned by the Jet Provider contains 28 fields. For digital fields, eight (8) have data; for text fields, nine (9) has data. Useful fields are likely to:
• Table_name • Column_name • ORDINAL_POSITION • DATA_TYPEODBC Provider Returns 29 fields. For digital fields, ten (10) has data; for text fields, 11 have data. Useful fields are the same as described above.
Enumeration tables and fields and their properties You can use Visual Basic code (as shown in the following example) to enumerate the tables and columns in the Excel data source, as well as available information about individual tables and columns. This example outputs its result to a list box List1 on the same form.
DIM CN as adodb.connection
DIM RST As Adodb.Recordset
DIM INTTTBLCNT AS INTEGER, INTTTBLFLDS AS INTEGER
DIM STRTBL AS STRING
DIM RSC as adoDb.recordset
DIM INTCOLCNT AS INTEGER, INTCOLFLDS AS INTEGER
DIM STRCOL AS STRING
DIM T AS INTEGER, C AS INTEGER, F AS INTEGER
Set cn = new adodb.connection
WITH CN
.Provider = "Microsoft.jet.OleDb.4.0"
.Connectionstring = "data source =" & app.path& _
"/Excelsrc.xlsrc.xls;Extended Properties = Excel 8.0;"
'.Provider = "msdasql"
'.Connectionstring = "driver = {Microsoft Excel Driver (* .xls)};" & _
"DBQ =" & app.path & "/excelsrc.xls;"
.CURSORLOCATION = aduseclient
.Open
End with
SET RST = cn.openschema (adschematables)
INTTTBLCNT = RST.Recordcount
INTTBLFLDS = RST.FIELDS.COUNT
List1.additem "Tables:" & INTTBLCNT
List1.additem "--------------------"
For t = 1 to INTTBLCNT
StrtBL = RST.Fields ("Table_name"). Value
List1.additem vbtab & "Table #" & T & T & T & T & T & T & T & T & T & T & T & T & T & T & T & T & T & T & T): "& StrtBL
List1.additem vbtab & "--------------------"
For f = 0 to intectBLFLDS - 1
List1.additem vbtab & rst.fields (f) .Name &_
VBTAB & RST.FIELDS (f) .value
NEXT
List1.additem "--------------------"
SET RSC = CN.OpenSchema (Adschemacolumns, Array (Empty, Empty, Strtbl, EMPTY))
INTCOLCNT = rsc.recordcount
IntColflds = rsc.fields.count
For c = 1 to intColcnt
Strcol = rsc.fields ("column_name"). Value
List1.additem Vbtab & Vbtab & "Column #" & C & ":" & strcol
List1.additem vbtab & vbtab & "--------------------"
For f = 0 to intColflds - 1
List1.additem vbtab & vbtab & rsc.fields (f) .Name &_
VBTAB & RSC.Fields (f) .value
NEXT
List1.additem vbtab & vbtab & "--------------------"
Rsc.movenext
NEXT
Rsc.close
List1.additem "--------------------"
Rst.movenext
NEXT
Rst.Close
Cn.close
When you create an Excel data source in Visual Basic, the information displayed in the "Data View" window shown in "Data View" is the same as the information described above with the information described above. In particular, the JET provider will list the worksheet and naming area in "table", while the ODBC provider displays the named area only under "Table". If you are using an ODBC provider, and no content will be displayed in the "Table" list.
Excel's restrictions use Excel as the data source to be limited to the internal limit of the Excel workbook and worksheet. These limits include, but are not limited to the following:
• Worksheet size: 65,536 lines, 256 columns • Cell content (text): 32,767 characters • Worksheets in your workbook: Available Memory Limit • Number of Names in Workbook: Available Memory Limit
reference
For more information on how to retrieve and modify the records in the Excel workbook in Visual Basic .NET, click the article number below to view the article in the Microsoft Knowledge Base:
316934 HOW TO: USE ADO.NET TO RETRIEVE AND MODIFY RECORDS in an Excel Workbook with Visual Basic .NET (using ADO.NET in Visual Basic .NET) Use ADO.NET to retrieve and modify records in the Excel workbook)
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
295646 HOWTO: TRANSFER DATA from ADO Data Source To Excel with ado (using ADO to send data from ADO data source to Excel)
246335 HOWTO: TRANSFER DATA from ADO RecordSet To Excel with Automation (using Automation to send data from ADO record collection to Excel)
247412 Info: Methods for Transferring Data To Excel from visual Basic (send data from Visual Basic to Excel)
278973 Example: Excelado Demonstrates How To Use Ado To Read and Write Data in Excel Workbooks (ExceLado demonstrates how to use ADO to read and write data in the Excel workbook)
For more information, see the following Microsoft Training and Certification Tutorial:
Microsoft Corporation
1301 Mastering Office 2000 Solution Developments (Proficiency in Office 2000 Solution Development)
