How To Use ADOX with Excel Data from Visual Basic or VBA
However, because an Excel worksheet is not an ordinary database, ADO has some limitations when it is used with Excel. In particular, many ADOX methods either do not work or give unexpected results when they are used with Excel. This article documents many of the limitations of ADOX and Excel. For additional information about the use of ADO with Excel, click the article number below to view the article in the Microsoft Knowledge Base:
- ADOX Catalogs
- ADOX Tables
- ADOX Columns
- Adding Data to Excel Tables and Columns
- Using SQL CREATE, ALTER, and DROP Statements
ADOX CatalogsIn the ADOX object model, the Catalog object represents a database or, in the case of Excel, a workbook.
- You can use an ADO Connection object or connection string to create or open a Catalog object. The following code sample illustrates how to open Book1.xls as an ADOX Catalog:
Dim cat As ADOX.CatalogSet cat = New ADOX.Catalogcat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & App.Path & "\book1.xls;Extended Properties=Excel 8.0"
- The Catalog object has a Create method but does not have a Delete method.
Creating a CatalogYou cannot use the Create method of the ADOX Catalog object to create a new Excel workbook. If you try to do so, you receive the following error message:
ADOX TablesThe ADOX Table object corresponds to either of the Excel objects (that is, a worksheet or a range) that can serve as a container for data.
Creating a TableYou can use ADOX to create new Tables in Excel. Because you cannot create a Table without also creating Columns, the code sample appears in the "ADOX Columns" section.
- Creating a new ADOX table creates both a new worksheet and a new named range on the new worksheet with the name specified. The new named range spans only the column headings. For example, if you create a table (worksheet) named MyTable with two columns, a named range MyTable, which is defined as MyTable!$A$1:$B$1, is also created.
- You can specify a table name that includes a space; however, ADOX replaces the space with an underscore in the worksheet and range names that you create.
- The new worksheet is created as the last sheet in the workbook, following the default number of empty sheets that are specified in Excel Options for a new workbook.
- If you try to create a new table when the workbook is open in Excel, you receive the following error message:Unspecified error.
- If you try to create a table that has no columns defined, it appears to succeed, but in fact nothing is created.
- If you try to change the name of an existing table, you receive the following error message:Operation is not supported for this type of object.
Deleting a TableYou cannot use ADOX to delete a table (worksheet) or named range in Excel, whether or not ADOX created it.
- If you try to delete the worksheet (MyTable$), it appears to succeed without error, but the worksheet remains in the workbook. You can call the Delete method repeatedly without error, but it has no effect.
- If you try to delete the range (MyTable), it appears to succeed without error, but the range remains in the workbook. However, if you try to delete the range a second time, you receive error 3265:Item cannot be found in the collection corresponding to the requested name or ordinal.
- If you try to delete a named range, it leaves the named range definition intact, but any data in the range is deleted.
ADOX ColumnsADOX columns correspond to the columns or fields in a database and neatly correspond to the columns of an Excel worksheet.
ADO Data Types Used with ExcelADO recognizes six data types in an Excel datasource that you can use to create columns:
- adDouble, type 5
- adDate, type 7
- adCurrency, type 6
- adBoolean, type 11
- adVarWChar, type 202
- adLongVarWChar ("memo"), type 203
Columns in General
- You can append additional columns to a previously created table as long as the table does not contain any rows of data.
- You can specify column names that include spaces.
- ADOX always creates column headings that are preceded by a single quotation mark ('), for example, 'F1, 'Col1, 'Col2. However, this does not appear to cause a problem later because the name is retrieved without the quotation mark.
Creating Columns When Creating a TableCreating columns works best when it is done at the same time that a new table is created using ADOX.
The following code creates a new table (worksheet) that contains two columns (one numeric and one text):
Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Dim col As ADOX.Column Set cat = New ADOX.Catalog cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & App.Path & "\book1.xls;Extended Properties=Excel 8.0" Set tbl = New ADOX.Table tbl.Name = "TestTable" Set col = New ADOX.Column With col .Name = "Col1" .Type = adDouble End With tbl.Columns.Append col Set col = Nothing Set col = New ADOX.Column With col .Name = "Col2" .Type = adVarWChar End With tbl.Columns.Append col cat.Tables.Append tbl
Creating Columns in an Existing WorksheetAppending columns to a pre-existing, empty worksheet generates unusual results. When the first ADOX column is appended to the table's Columns collection, ADOX first creates a column header named F1 in Column A of the worksheet and then creates the new ADOX column header in Column B of the worksheet. Thus, ADOX code that appends two new columns results in three column headers. If you try to delete this unwanted, first column from the Columns collection before you append the first ADOX column, it appears to succeed, but the unwanted column is created nonetheless.
Deleting ColumnsYou cannot delete a column from an ADOX table in Excel.
- If the column has no data, this appears to succeed without error, but the column and column header remain.
- If the column contains data, the delete operation fails with the following error message:Invalid operation.
Changing the Data Type of an Existing ColumnAfter a column has been appended to a table, if you try to set its .Type property to a different data type, you receive the following error message:
Adding Data to Excel Tables and ColumnsWhen you are testing ADO code with Excel in the Visual Basic design environment, the first time that you run a recordset operation, you may receive the following error message:
Adding Data to Excel Columns That Are Created by ADOXWhen you use ADO to insert or modify data in the tables and columns that you have created in Excel using ADOX, ADO respects the data types that you specified for those columns, although it is not clear where this information is stored.
- All text string values are stored preceded by a single quotation mark. However, this does not appear to cause a problem later because the value is retrieved without the quotation mark.
- If you try to insert a text string into a column that is defined as numeric, or a number into a text column, you receive error 80040e21:Multiple-step operation generated errors. Check each status value.
Adding Data to Ordinary Excel ColumnsWhen you use ADO to insert or modify data in Excel worksheets or ranges that are not created or modified using ADOX, no restrictions are enforced on the type of data that you can insert.
- Text strings are stored without the preceding single quotation mark.
- Numbers appear left-aligned. In Excel 2002, numbers are flagged with the Smart Tag that warns "Numbers stored as text". This may cause a problem later when you are working with the data, especially if the data has been stored in Excel for numerical analysis.
Using SQL CREATE, ALTER, and DROP StatementsYou can run Microsoft SQL Data Definition Language (DDL) statements such as CREATE, ALTER, and DROP against Excel database objects over an ADO Connection to yield the same results as the corresponding ADOX methods that are described above.
- ADOX works best with Excel when a new table and its columns are all created at the same time.
- ADO works best with Excel data when that data is stored in a worksheet that is created using ADOX.
Article ID: 303814 - Last Review: 12/06/2015 04:03:06 - Revision: 2.2
- kbnosurvey kbarchive kbdatabase kbhowto kbiisam KB303814