VBA将Excel导出为XML

以下代码的功能，是配合select，将Excel导出为XML文件。

Const stSQL As String = "SELECT * FROM [Report]"

Dim stCon As String

stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & ThisWorkbook.FullName & ";" & _

"Extended Properties=""Excel 8.0;HDR=Yes"";"

Dim rst As New ADODB.Recordset

Dim str As New ADODB.Stream

With rst

.CursorLocation = adUseClient

.Open stSQL, stCon, adOpenStatic, adLockReadOnly, adCmdText

.Save str, adPersistXML

.Close

With str

.SaveToFile "C:dzwebs.xml", adSaveCreateOverWrite

.Close

End With

End With

Set str = Nothing

Set rst = Nothing

注意，要使用如上的代码，需要添加MS ADO Library引用。