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

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

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.

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

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.

Using SQL CREATE, ALTER, and DROP Statements

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.