(257819) HOWTO: use ADO in Visual Basic or VBA to process Excel Data

Source: Internet
Author: User
Tags dsn ole microsoft access database truncated
This topic describes how to use ActiveX Data Objects (ADO) to process data in a workbook with a data source of Microsoft Excel. This article also focuses on the syntax issues and restrictions specific to excel. This article does not discuss OLAP, PivotTable, or other special usage of Excel Data.

For other information, click the following article number to view the article in the Microsoft Knowledge Base:

303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA (use ADOX to process Excel Data in Visual Basic or VBA) more information

Rows and columns in a Microsoft Excel worksheet are very similar to rows and columns in a database. As long as you remember that Microsoft Excel is not a relational database management system and realize the limitations brought by this fact, you can use Excel and its tools to store and analyze data in many cases.

Microsoft ActiveX Data Objects allows us to view an Excel Workbook as a database. This article discusses how to achieve this through the following sections:

Connect to excel using ADO
Use ADO to retrieve and edit Excel Data
Retrieve Data Source Structure (metadata) from Excel)

Remarks: The test content in this article is carried out by using Microsoft Data Access Components (MDAC) 6.0 on Microsoft Windows 2000 with Visual Basic 2000 Service Pack 3 and Excel 2.5 installed. This article may not confirm or discuss the differences that users may observe when using different versions of MDAC, Microsoft Windows, Visual Basic, or Excel.

Connect to excel using ADO

Ado can use either of the following two ole db providers in MDAC to connect to an Excel data file.

Microsoft jet ole db provider-or-

Microsoft ole db provider for ODBC drivers
How to use Microsoft jet ole db Provider

The jet provider can connect to the Excel Data Source with only two pieces of information: path (including file name), and Excel file version.

Jet provider-use a connection string

Dim cn as ADODB.ConnectionSet cn = New ADODB.ConnectionWith cn.Provider = "Microsoft.Jet.OLEDB.4.0".ConnectionString = "Data Source=C:/MyFolder/MyWorkbook.xls;" & _"Extended Properties=Excel 8.0;".OpenEnd With

Provider version: The JET 4.0 provider must be used; the jet 3.51 provider does not support the jet isam driver. If the jet 3.51 provider is specified, the following error message is displayed during running:

Couldn't find installable isam.

Excel version: For Excel 95 workbooks (Excel version 7.0), Excel 5.0 should be specified; for Excel 97, Excel 2000 or Excel 2002 (XP) workbooks (Excel versions 8.0, 9.0, and 10.0 ), excel 8.0 should be specified.

Jet provider-use the "Data Link Properties" dialog box

If you use the ADO data control or data environment in the applicationData Link PropertyDialog Box to collect required connection settings.

1. InProvider)On the tab, select the JET 4.0 provider. The jet 3.51 provider does not support the jet isam driver. If the jet 3.51 provider is specified, the following error message is displayed during running:

Couldn't find installable isam.
2. InConnectionTab to browse your workbook files. Ignore "User ID" and "password" items because these are not applicable to excel connections. (The password-protected Excel file cannot be opened as the data source. This topic is detailed later .)
3. InAllTab, select from the listExtended Properties)And then clickEdit Value. InputExcel 8.0;, Separated by a semicolon. If this step is ignored, an error message is displayed during connection testing. This is because, if not specified separately, the jet provider expects the Microsoft Access database.
4. ReturnConnectionTab, and then clickTest connection. A message box is displayed, notifying you that the test has been completed successfully.

Other jet provider connection settings

Column Title: By default, the system determines that the first row of the Excel Data Source contains the column title that can be used as the field name. If this is not the case, you must disable this setting. Otherwise, the first row of data will disappear and be used as the field name. The optionalHDR =SetExtended attributes. The default setting (not required) isHDR = Yes. If no column title exists, you must specifyHDR = NoThe provider will name your fields F1, F2, and so on. BecauseExtended attributesA string now contains multiple values, so it must be enclosed in double quotation marks, followed by a pair of double quotation marks, telling Visual Basic to use the first pair of quotation marks as the text value, as shown in the following example (additional spaces are added for easy visibility ).

.ConnectionString = "Data Source=C:/MyFolder/MyWorkbook.xls;" & _"Extended Properties=" " Excel 8.0; HDR=No;" " "

Use Microsoft ole db provider for ODBC drivers

The ODBC driver provider ("ODBC provider" in this article) can connect to the Excel Data Source with only two pieces of information: the driver name, and the path and file name of the workbook.

Important: ODBC connections in Excel are read-only by default. Ado record setLocktypeThe connection-level settings are not overwritten. To edit the data, you must setReadonlySetFalse. Otherwise, the following error message is displayed:

Operation must use an updateable query.

ODBC provider-use a connection string without DSN

Dim cn as ADODB.ConnectionSet cn = New ADODB.ConnectionWith cn.Provider = "MSDASQL".ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _"DBQ=C:/MyFolder/MyWorkbook.xls; ReadOnly=False;".OpenEnd With

ODBC provider-use a connection string with DSN

Dim cn as ADODB.ConnectionSet cn = New ADODB.ConnectionWith cn.Provider = "MSDASQL".ConnectionString = "DSN=MyExcelDSN;".OpenEnd With

ODBC provider-use the "Data Link Properties" dialog box

If you use the ADO data control or data environment in the applicationData Link PropertyDialog Box to collect required connection settings.

1. InProviderTab, selectMicrosoft ole db provider for ODBC drivers.
2. InConnectionTab, select the existing DSN you want to use, or selectUse the connection string. This will open the standard DSN configuration dialog box to collect necessary connection settings. If necessary, do not forget to deselect the default read-only setting, as described above.
3. ReturnConnectionTab, and then clickTest connection. A message box is displayed, notifying you that the test has been completed successfully.

Other ODBC provider connection settings

Column Title: By default, the system determines that the first row of the Excel Data Source contains the column title that can be used as the field name. If this is not the case, you must disable this setting. Otherwise, the first row of data will disappear and be used as the field name. You can add an optionalFirstrowhasnames =. The default setting (not required) isFirstrowhasnames = 1, Where,1 = true. If no column title exists, you must specifyFirstrowhasnames = 0, Where,0 = falseThe driver will name the field F1, F2, and so on. This option is not available in the DSN configuration dialog box.

However, because the ODBC driver has an errorFirstrowhasnamesThe setting does not work. In other words, the Excel ODBC driver (MDAC 2.1 and later) always uses the first row of the specified data source as the field name. For other information about the incorrect column title, click the following article number to view the article in the Microsoft Knowledge Base:

288343 BUG: Excel ODBC driver disregards the firstrowhasnames or header setting (Excel ODBC driver ignores firstrowhasnames or header settings)

Number of rows to be scanned: Excel does not provide detailed architecture information about ADO data like relational databases. Therefore, the driver must scan at least a few lines of existing data to guess the Data Type of each column. The default value of "number of rows to be scanned" is eight (8) rows. You can specify an integer from row (1) to row (16), or specify zero (0) to scan all existing rows. You can addMaxscanrows =Optional, or changed in the DSN configuration dialog boxNumber of rows to be scanned.

However, because the ODBC driver has an error, the current setting of "number of rows to be scanned" (maxscanrows) does not work. In other words, the Excel ODBC driver (MDAC 2.1 and later) always scans the first eight rows in the specified data source to determine the Data Type of each column.

For other information about the "number of rows to be scanned" error, including a simple solution, click the following article number to view the article in the Microsoft Knowledge Base:

189897 xl97: Data truncated to 255 characters with Excel ODBC driver (255 characters are truncated when the Excel ODBC driver is used)

Other settings: If you useData Link PropertyDialog Box to generate a connection string. You may notice that some otherExtended attributesBut these settings are not absolutely necessary, for example:

... DefaultDir=C:/WorkbookPath;DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;PageTimeout=5;

"Sorting order" error message in Visual Basic Editor

When using some versions of MDAC, when your program is connected to the Excel data source for the first time during design, the following error message appears in the Visual Basic Design Environment:

Selected collating sequence not supported by the operating system.

This information only appears in the IDE, rather than in the compiled program. For other information, click the following article number to view the article in the Microsoft Knowledge Base:

246167 PRB: collating sequence error opening ADODB recordset the first time against an Excel XLS (the first time you open the ADODB record set from the Excel xls file, the sorting order error occurs) considerations when using these two ole db providers

Precautions when mixing Data Types

As described above, ADO must guess the Data Type of each column in an Excel worksheet or range. (This is not affected by the Excel cell format settings .) If there are both numeric values and text values in the same column, a serious problem may occur. The jet and ODBC providers return data of the majority type, but for a few data types, the return value is null (null. If the number of two types of data in this column is equal, the provider will give priority to numeric data and discard text data.

For example:

In the eight (8) rows scanned, if the column contains five (5) numeric values and three (3) text values, the provider returns five (5) numbers and three (3) null values.
In the eight (8) rows scanned, if the column contains three (3) numeric values and five (5) text values, the provider returns three (3) and five (5) text values.
In the eight (8) rows scanned, if the column contains four (4) numeric values and four (4) text values, the provider returns four (4) and four (4) null values.

Therefore, if the column contains different types of values, the only solution is to store the numeric values in the column as text, and then use Visual BasicValA function or equivalent function converts it back to a number in a client application.

To solve this problem for read-only data, you can set "IMEX = 1" in the "extended properties" section of the connection string to enableImport Mode. This will be mandatoryImportmixedtypes = textRegistry Settings. However, in this mode, unexpected results may occur during the update operation. For other information about this setting, click the following article number to view the article in the Microsoft Knowledge Base:

194124 PRB: Excel values returned as null using Dao openrecordset (when Dao openrecordset is used, the returned Excel value is null)

Unable to open password-protected workbook

If your Excel Workbook is password-protected, you cannot open it to access its data even if the correct password is provided in the connection settings, unless the workbook file has been opened in the Microsoft Excel application. If you try to do this, the following error message is displayed:

Cocould not decrypt file.

For other information, click the following article number to view the article in the Microsoft Knowledge Base:

211378 XL2000: "cocould not decrypt file" error with password protected file (the "file cannot be decrypted" error occurs when accessing a password-protected file)

Use ADO to retrieve and edit Excel Data

This section describes two ways to process Excel Data:

How to Select data,-and-

How to change data
How to Select data

There are several data selection methods. 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 contained in one of the following objects in the workbook:

The whole worksheet.
The area of the named cells on the worksheet.
The Untitled cell area on the worksheet.

Specified Worksheet

To specify a worksheet as the record source, use the worksheet name, followed by a dollar character, and enclose it in square brackets: for example:

strQuery = "SELECT * FROM [Sheet1$]"

You can also use the waveform character (~) on the keyboard (~) To separate worksheet names. For example:

strQuery = "SELECT * FROM `Sheet1$`"

We recommend that you use square brackets (square brackets) to indicate the names of unknown database objects.

If you omit dollar signs and square brackets, or only dollar signs, the following error message is displayed:

... The Jet Database Engine cocould not find the specified object

If the dollar sign is used but square brackets are omitted, the following error message is displayed:

Syntax Error in from clause.

If you try to use regular single quotes, the following error message is displayed:

Syntax Error in query. Incomplete query clause.

Specified naming Area

To specify the named cell region as the record source, you only need to use the defined name. For example:

strQuery = "SELECT * FROM MyRange"

Untitled Region

To specify an unnamed cell region as the record source, add the area represented by standard Excel row/column notation to the worksheet name and enclose it in square brackets. For example:

strQuery = "SELECT * FROM [Sheet1$A1:B10]"

Notes when specifying a worksheet: The provider determines that the data table starts from a non-empty cell at the top left of the specified worksheet. In other words, the data table can start with 3rd rows and column C, which is no problem. However, in this case, you cannot enter the worksheet title in cell A1 in the upper-left corner of the data.

Precautions when specifying a region: When a worksheet is specified as the record source, the provider adds the new record to the existing record under the worksheet (if there is space available ). When you specify a region (named region or unnamed region), jet also adds the new record to the existing record under the region (if there is space available ). However, if you re-query the original region, the resulting result set does not contain records newly added outside the region.

When you use MDAC earlier than version 2.5, If you specify a naming region, you cannot add a new record to the boundary defined in the region. Otherwise, the following error message is displayed:

Cannot expand named range. Use the ADO Data Control to select Excel Data

In adodcAttributeDialog BoxGeneralAfter specifying the connection settings for the Excel data source, clickRecord sourceTab. If the commandtype is adshorttext, you canCommand textIn the dialog box, enter a SELECT query according to the syntax described above. If the commandtype is adcmdtable and the Jet provider is used, the available naming areas and worksheet names in the selected workbook are displayed in the drop-down list, and the naming areas are listed first.

This dialog box correctly adds the dollar sign after the worksheet name, but does not add necessary square brackets. Therefore, if you select only one worksheet name and clickOK, The following error message is displayed:

Syntax Error in from clause.

You must manually add square brackets before and after the worksheet name. (This combo box can be edited .) If you are using an ODBC provider, you can only see the named area in the drop-down list. However, you can manually enter the worksheet name with the correct separator.

Use the data environment command to select Excel Data

After setting the data environment connection for the Excel data source, create a newCommandObject. If you selectSQL statementAsData SourceYou can enter a query in the text box according to the syntax described above. If you selectDatabase objectsAsData SourceIn the first drop-down list, selectTable. If you are using the jet provider, the available naming area and worksheet name in the selected workbook are displayed in the drop-down list, with the naming area at the top. (When selecting a worksheet name from this position, you do not need to add square brackets before and after the worksheet name as you did when using the ADO Data Control .) If you are using an ODBC provider, you can only see the named area in the drop-down list. However, you can manually enter the worksheet name.

How to change Excel Data: Edit, add, and delete

Edit

You can use the normal ADO method to edit Excel Data. The record set fields corresponding to cells in an Excel worksheet that contain an Excel Formula (starting with "=") are read-only and cannot be edited. Remember that ODBC connections in Excel are read-only by default, unless otherwise specified in connection settings. See the section "use Microsoft ole db provider for ODBC drivers" above.

Add

If there is space available, you can add records to the Excel Record source. However, if you add a new record to a region other than the original one, you will not be able to see these records when you re-query the original region. See the section "precautions when specifying a region" above.

In some casesRecordsetObjectAddnewAndUpdateMethod when a new data row is inserted into an Excel table, ADO may insert the data value into an incorrect column. For other information, click the following article number to view the article in the Microsoft Knowledge Base:

314763 fix: Ado inserts data into wrong columns in Excel (ADO inserts data into wrong columns in Excel)

Delete

When deleting Excel data, there are more restrictions than when deleting data from a relational data source. In a relational database, "rows" have no meaning except a "record", but they are different in an Excel worksheet. You can delete values in fields (cells. But not:

1. Delete a whole record at a time. Otherwise, the following error message is displayed:

Deleting data in a linked table is not supported by this isam.

Only one record can be deleted by clearing the content of each field separately.

2. Delete the values in cells that contain the Excel Formula. Otherwise, the following error message is displayed:

Operation is not allowed in this context.
3. Although the row of the deleted data in the workbook is empty, it cannot be deleted, and the record set will continue to display empty records corresponding to these empty rows.

Notes when using ADO to edit Excel Data: When you insert text data in an Excel file using ADO, a single quotation mark is placed before the text value. This may cause problems when processing new data later.

Retrieve Data Source Structure (metadata) from Excel)

You can use ADO to retrieve data about the structure of an Excel Data Source (table and field. Although at least the same number (few) of fields are returned when two ole db providers are used, the results are still slightly different. You can use ADOConnectionObjectOpenschemaTo retrieve the metadata. This method returns an adoRecordsetObject. You can also use the more powerful Microsoft ActiveX Data Objects extensions for Data Definition Language and Security (ADOX) library to retrieve metadata. However, for Excel data sources ("tables" are actually a worksheet or named area, and "fields" belong to several simple data types), although ADOX is more powerful, it is useless.

Query table information

Among the various objects provided by relational databases (tables, views, stored procedures, and so on), the Excel Data Source only provides objects equivalent to tables, it consists of the worksheet in the specified workbook and the defined naming area. The naming area is regarded as a "table", while the worksheet is considered as a "system table ". Except for the "table_type" attribute, too many useful table information cannot be retrieved. Use the following code to retrieve the list of available tables in the 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. The value is "Last modified Date ". In other words, "date_created" is not reliable.

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 documentOpenschemaYou can retrieve the list of worksheets by specifying the following additional conditions:

Set rs = cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "System Table"))

Unfortunately, for an Excel data source, if the MDAC version is higher than 2.0, no matter which provider is used.

Query Field Information

Each field (column) in an Excel Data source belongs to one of the following data types:

Number (ADO data type 5, addouble)
Currency (ADO Data Type 6, adcurrency)
Logical or boolean values (ADO Data Type 11, adboolean)
Date (when using jet, It is ADO Data Type 7, addate; when using ODBC, It is Data Type 135, addbtimestamp)
Text (a type of ADO ad... Char, such as 202, advarchar; 200, advarwchar, or similar)

For numeric columns, the returned numeric_precision is always 15 (the maximum precision in Excel); For text columns, the returned character_maximum_length is always 255 (the maximum display width of the text in the Excel column, but not the maximum length ). DivisionData_typeToo many useful field information cannot be retrieved. Use the following code to retrieve the 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 numeric fields, eight (8) have data; for text fields, nine (9) have data. Useful fields may be:

Table_name
Column_name
Ordinal_position
Data_type

The record set returned by the ODBC provider contains 29 fields. For numeric fields, 10 (10) have data; for text fields, 11 have data. The useful fields are the same as described above.

Enumeration tables and fields and their attributes

You can use Visual Basic code (as shown in the following example) to enumerate tables and columns in an Excel data source, as well as available information about each table and column. In this example, the result is output to a list1 list box on the same form.

Dim cn As ADODB.ConnectionDim rsT As ADODB.RecordsetDim intTblCnt As Integer, intTblFlds As IntegerDim strTbl As StringDim rsC As ADODB.RecordsetDim intColCnt As Integer, intColFlds As IntegerDim strCol As StringDim t As Integer, c As Integer, f As IntegerSet cn = New ADODB.ConnectionWith cn.Provider = "Microsoft.Jet.OLEDB.4.0".ConnectionString = "Data Source=" & App.Path & _"/ExcelSrc.xls;Extended Properties=Excel 8.0;"'.Provider = "MSDASQL"'.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _"DBQ=" & App.Path & "/ExcelSrc.xls; ".CursorLocation = adUseClient.OpenEnd WithSet rsT = cn.OpenSchema(adSchemaTables)intTblCnt = rsT.RecordCountintTblFlds = rsT.Fields.CountList1.AddItem "Tables:" & intTblCntList1.AddItem "--------------------"For t = 1 To intTblCntstrTbl = rsT.Fields("TABLE_NAME").ValueList1.AddItem vbTab & "Table #" & t & ":" & strTblList1.AddItem vbTab & "--------------------"For f = 0 To intTblFlds - 1List1.AddItem vbTab & rsT.Fields(f).Name & _vbTab & rsT.Fields(f).ValueNextList1.AddItem "--------------------"Set rsC = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, strTbl, Empty))intColCnt = rsC.RecordCountintColFlds = rsC.Fields.CountFor c = 1 To intColCntstrCol = rsC.Fields("COLUMN_NAME").ValueList1.AddItem vbTab & vbTab & "Column #" & c & ": " & strColList1.AddItem vbTab & vbTab & "--------------------"For f = 0 To intColFlds - 1List1.AddItem vbTab & vbTab & rsC.Fields(f).Name & _vbTab & rsC.Fields(f).ValueNextList1.AddItem vbTab & vbTab & "--------------------"rsC.MoveNextNextrsC.CloseList1.AddItem "--------------------"rsT.MoveNextNextrsT.Closecn.Close

Use the Data View window

When you create a data link for an Excel data source in the "Data View" Window of Visual Basic, the information displayed in the "Data View" is the same as the information that can be retrieved by programming as described above. Note that the jet provider columns the worksheet and naming area under the "table", while the ODBC provider only displays the naming area under the "table. If the ODBC provider is used and no naming area is defined, NO content is displayed in the table list.

Excel restrictions

Using Excel as a data source is limited by the internal limitations of Excel workbooks and worksheets. These restrictions include, but are not limited to, the following:

Worksheet size: 65,536 rows, 256 Columns
Cell content (text): 32,767 characters
Number of worksheets in the workbook: limited by available memory
Number of names in the workbook: limited by available memory
For more information about how to use ADO. Net to retrieve and modify records in an Excel Workbook in Visual Basic. net, click the following article number 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 (in Visual Basic. use ADO. net. net to retrieve and modify records in an Excel Workbook)

For other information, click the following article number to view the article in the Microsoft Knowledge Base:

295646 HOWTO: transfer data from ADO data source to excel with ADO (use ADO to transfer data from ADO data source to excel) 246335 HOWTO: transfer Data from ADO recordset to excel with automation (use automation to transfer data from ADO record set to excel) 247412 info: methods for transferring data to excel from Visual Basic (transfer data from Visual Basic to excel) Example 278973: 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 an Excel Workbook)

For more information, see the following Microsoft Training and Certification Tutorials:

Microsoft configuration1301 mastering Office 2000 solution development (proficient in Office 2000 solution development)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.