ADOX 創建EXCEL

ADOX Catalog
在 ADOX 對象模型中，Catalog 對象表示數據庫或 Excel 中的工作簿。 ? 可以使用 ADO Connection 對象或連接字符串創建或打開 Catalog 對象。下面的代碼示例說明了如何將 Book1.xls 作為 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"
                    

? Catalog 對象有一個Create 方法，但沒有Delete 方法。

創建 Catalog
不能使用 ADOXCatalog 對象的 Create 方法創建新的 Excel 工作簿。如果嘗試這樣做，將收到以下錯誤信息：
“Operation is not supported for this type of object”（對象不支持此操作）。
不過，可以通過下述方法創建新的 Excel 工作簿：為 Catalog 指定新的工作簿文件名，并使用下面討論的技術在新的 Catalog 中附加至少一個 Table。此方法只是用您作為 ADOX 表創建的工作表來創建新工作簿；換言之，將不添加通過 ExcelOptions（選項）對話框為新 Excel 工作簿指定的默認數量的空白表。
回到頂端

ADOX 表
ADOX Table 對象對應于可作為數據容器的 Excel 對象（即工作表或范圍）之一。
創建表
可以使用 ADOX 在 Excel 中創建新表。如果不同時創建列，則無法創建 Table，因此“ADOX 列”部分顯示了代碼示例。 ? 創建新的 ADOX 表時，不僅會創建一個新工作表，而且會在這個新工作表上用指定的名稱創建一個新的指定范圍。新的指定范圍僅包括列標題。例如，如果創建名為 MyTable 的兩列表（工作表），則還會創建一個名為 MyTable 的范圍（被定義為 MyTable!$A$1:$B$1）。
? 指定的表名中可以有空格；但是，ADOX 會將您創建的工作表和范圍名稱中的空格替換為下劃線。
? 創建的新工作表將作為工作簿中的最后一張表，位于在 Excel “選項”中為新工作簿指定的默認數量的空白表后。
? 當工作簿在 Excel 中打開時，如果試圖創建新表，將收到以下錯誤信息：
“Unspecified error”（未定錯誤）。
? 如果試圖創建未定義列的表，看起來似乎是成功的，但實際上并未創建任何東西。
? 如果試圖更改現有表的名稱，將收到以下錯誤信息：
“Operation is not supported for this type of object”（對象不支持此操作）。

刪除表
在 Excel 中，不論 ADOX 是否創建了表（工作表）或指定的范圍，都不能使用 ADOX 將其刪除。 ? 如果試圖刪除工作表 (MyTable$)，看起來似乎是成功的，沒有錯誤，但該工作表仍保留在工作簿中?？梢灾貜驼{用 Delete 方法而不會引發錯誤，但這是無效的。
? 如果試圖刪除范圍 (MyTable)，看起來似乎是成功的，沒有錯誤，但該范圍仍保留在工作簿中。但是，如果試圖再次刪除該范圍，將收到錯誤 3265：
“Item cannot be found in the collection corresponding to the requested name or ordinal”（在對應所請求的名稱或序號的集合中找不到項目）。
? 如果試圖刪除指定的范圍，指定范圍定義將完整地保留下來，但范圍中的所有數據都將被刪除。

回到頂端

ADOX 列
ADOX 列對應于數據庫中的列或字段并整齊地對應于 Excel 工作表中的列。
用于 Excel 的 ADO 數據類型
ADO 能夠識別可用于創建列的 Excel 數據源中的六種數據類型：

? adDouble，類型 5
? adDate，類型 7
? adCurrency，類型 6
? adBoolean，類型 11
? adVarWChar，類型 202
? adLongVarWChar ("memo")，類型 203  

常規列
? 只要先前創建的表不包含任何數據行，就可以將其他列附加到該表。
? 指定的列名中可以有空格。
? ADOX 在創建列標題時，總是會在前面加上一個單引號 (')，例如 'F1、'Col1、'Col2。不過，由于檢索名稱時不包括這個引號，這似乎不會在以后導致問題。

創建表時創建列
在使用 ADOX 創建新表時創建列的效果最佳。

下面的代碼創建一個含有兩列（一個數字列和一個文本列）的新表（工作表）：     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
                
在現有工作表中創建列
向預先存在的空白工作表中附加列將產生不尋常的結果。當第一個 ADOX 列附加到表的 Columns 集合時，ADOX 首先在工作表的列 A 中創建一個名為 F1 的列標題，然后在工作表的列 B 中創建新的 ADOX 列標題。因此，附加兩個新列的 ADOX 代碼產生三個列標題。如果在附加第一個 ADOX 列之前，試圖從 Columns 集合中刪除這個多余的第一列，看起來似乎是成功的，但其實仍然已創建了這個多余的列。
刪除列
在 Excel 中，不能從 ADOX 表中刪除列。 ? 如果列中沒有數據，這看起來似乎是成功的，沒有錯誤，但仍然會保留列和列標題。
? 如果列中有數據，刪除操作將失敗并出現以下錯誤信息：
“Invalid operation”（無效操作）。

更改現有列的數據類型
列附加到表之后，如果試圖將列的 .Type 屬性設置為另一數據類型，將收到以下錯誤信息：
“Operation is not allowed in this context”（此上下文中不允許操作）。
回到頂端

向 Excel 表和列中添加數據
當您在 Visual Basic 設計環境中用 Excel 測試 ADO 代碼時，首次運行記錄集操作時可能會收到以下錯誤信息：
“Selected collating sequence not supported by the operating system”（操作系統不支持選擇的排序）。
可以忽略此錯誤信息；編譯應用程序后不再出現此錯誤。有關其他信息，請單擊下面的文章編號，查看 Microsoft 知識庫文章：
246167 (http://support.microsoft.com/kb/246167/EN-US/) PRB:Collating Sequence Error Opening ADODB Recordset the First Time Against an Excel XLS（首次在 Excel XLS 中打開 ADODB 記錄集時的排序錯誤）
向 ADOX 所創建的 Excel 列中添加數據
當您使用 ADO 在 Excel 中用 ADOX 創建的表和列中插入或修改數據時，ADO 將考慮您為那些列指定的類型，但我們不清楚這些信息存儲在何處。 ? 所有的文本字符串值在存儲時，前面都會帶一個單引號。不過，由于檢索值時不包括這個引號，這似乎不會在以后導致問題。
? 如果試圖在被定義為數字的列中插入文本字符串，或者將數字插入文本列，將收到錯誤 80040e21：
“Multiple-step operation generated errors”（多步操作產生錯誤）。“Check each status value”（檢查每個狀態值）。

向普通 Excel 列中添加數據
當使用 ADO 在不是用 ADOX 創建或修改的 Excel 工作表或區域中插入或修改數據時，可以插入的數據類型是不受限制的。 ? 存儲文本字符串時，開頭不加單引號。
? 數字按左對齊方式顯示。在 Excel 2002 中，數字帶有一個“智能標簽”標記，用以警告“數字是以文本存儲的”。在以后處理數據時，這可能會導致問題，特別是當 Excel 中存儲的數據是用于數值分析時。

回到頂端

使用 SQL CREATE、ALTER 和 DROP 語句
對 Excel 數據庫對象運行 Microsoft SQL 數據定義語言 (DDL) 語句（如 CREATE、ALTER 和 DROP）（通過 ADOConnection ）可以產生與上述的相應 ADOX 方法相同的結果。
回到頂端

結論
? 當同時創建新表及其所含的列時，最適合用 ADOX 操作 Excel。
? 當數據存儲在用 ADOX 創建的工作表中時，最適合用 ADO 操作 Excel 數據。

原文轉自：http://www.anti-gravitydesign.com