VBA将Excel导出为XML

10/11/2015来源:Excel教程人气:3431

  以下代码的功能,是配合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引用。