How To Use ADOX with Excel Data from Visual Basic or VBA

• ADOX Catalogs
• Creating a Catalog


• ADOX Tables
• Creating a Table
• Deleting a Table


• ADOX Columns
• ADO Data Types Used with Excel
• Columns in General
• Creating Columns When Creating a Table
• Creating Columns in an Existing Worksheet
• Deleting Columns
• Changing the Data Type of an Existing Column


• Adding Data to Excel Tables and Columns
• Adding Data to Excel Columns That Are Created by ADOX
• Adding Data to Ordinary Excel Columns


• Using SQL CREATE, ALTER, and DROP Statements
• Conclusion

Back to the top

ADOX Catalogs

• 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.Catalog
  Set cat = New ADOX.Catalog
  cat.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 Catalog

Back to the top

ADOX Tables

Creating a Table

• 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 Table

• 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.

Back to the top

ADOX Columns

ADO Data Types Used with Excel

• 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 Table

    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 Worksheet

Deleting Columns

• 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 Column

Back to the top

Adding Data to Excel Tables and Columns

Adding Data to Excel Columns That Are Created by ADOX

• 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 Columns

• 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.

Back to the top

Using SQL CREATE, ALTER, and DROP Statements

Back to the top

Conclusion

• 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.

Back to the top