в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:19Z

1 версия

Новая страница

==Compatibility Mode==



<source lang="vb">

Sub wkbkSave()
Dim xlVersion As String
Dim myxlOpenXMLWorkbook As String

myxlOpenXMLWorkbook = "51"

xlVersion = Application.Version

Select Case xlVersion
Case Is = "9.0", "10.0", "11.0"
ActiveWorkbook.SaveAs FileName:="LegacyVersionExcel.xls"
Case Is = "12.0"
ActiveWorkbook.SaveAs FileName:="Excel2007Version", _
FileFormat:=myxlOpenXMLWorkbook
End Select
End Sub
</source>

==Creating Your Own XSD==



<source lang="vb">

Sub Create_XSD()
Dim StrMyXml As String, MyMap As XmlMap
Dim StrMySchema As String
StrMyXml = "<EmployeeSales>"
StrMyXml = StrMyXml & "<Employee>"
StrMyXml = StrMyXml & "<Empid>999</Empid>"
StrMyXml = StrMyXml & "<FirstName>Text</FirstName>"
StrMyXml = StrMyXml & "<LastName>Text</LastName>"
StrMyXml = StrMyXml & "<InvoiceNumber>999</InvoiceNumber>"
StrMyXml = StrMyXml & "<InvoiceAmount>999</InvoiceAmount>"
StrMyXml = StrMyXml & "</Employee>"
StrMyXml = StrMyXml & "<Employee></Employee>"
StrMyXml = StrMyXml & "</EmployeeSales>"
Application.DisplayAlerts = False
Set MyMap = ThisWorkbook.XmlMaps.add(StrMyXml)
Application.DisplayAlerts = True
StrMySchema = ThisWorkbook.XmlMaps(1).Schemas(1).XML
Open "C:\StrMySchema.xsd" For Output As #1
Print #1, StrMySchema
Close #1
End Sub
</source>

==Exporting to an XML File==



<source lang="vb">

Sub ExportToXmlFile()
ActiveWorkbook.XmlMaps("EmployeeSales_Map").Export URL:=ThisWorkbook.Path & "\Exported.xml"
End Sub
</source>

==Export to XML file==



<source lang="vb">

Option Compare Database
Private Sub ExportTestsData()
Dim objad As AdditionalData
Set objad = Application.CreateAdditionalData
objad.Add "Questions"
objad(Item = "Questions").Add "Answers"
Application.ExportXML acExportTable, "Tests", "c:\tests4.xml", "c:\tests4.xsd", AdditionalData:=objad
End Sub
</source>

==Get Schema==



<source lang="vb">

Sub GetSchema()
Dim MySchema As String
"Get the schema
MySchema = ActiveWorkbook.XmlMaps("EmployeeSales_Map").Schemas(1).XML
Open "C:\MySchema.xsd" For Output As #1
Print #1, MySchema
Close #1
End Sub
</source>

==Importing Data into an Existing XML Map==



<source lang="vb">

Sub ImportXmlFromFile()
ThisWorkbook.XmlMaps("EmployeeSales_Map").Import(ThisWorkbook.Path & "\EmployeeSales.xml")
End Sub
</source>

==Leveraging DOM and XPath to Manipulate XML Files==



<source lang="vb">

Sub Load_ReadXMLDoc()
Dim oMyDoc As DOMDocument
Set oMyDoc = New DOMDocument
oMyDoc.async = False
oMyDoc.Load (ThisWorkbook.Path & "\SalesByRegion.xml")
Debug.Print oMyDoc.XML
Set oMyDoc = Nothing
End Sub
</source>

==Programmatically Changing XML Map Properties==



<source lang="vb">

Sub ChangeXmlMapProperties()
With ActiveWorkbook.XmlMaps("EmployeeSales_Map")
.name = "New_Name"
.ShowImportExportValidationErrors = False
.SaveDataSourceDefinition = True
.AdjustColumnWidth = True
.PreserveColumnFilter = True
.PreserveNumberFormatting = True
.AppendOnImport = False
End With
ThisWorkbook.XmlMaps(1).name = "EmployeeSales_Map"
End Sub
</source>

==Refresh Your XML Data==



<source lang="vb">

Sub RefreshXML()
ThisWorkbook.XmlMaps("EmployeeSales_Map").DataBinding.Refresh
End Sub
</source>

==returns a workbook object with the XML data mapped to your spreadsheet:==



<source lang="vb">

Sub ImportXMLtoList()
Dim strTargetFile As String
Application.DisplayAlerts = False
strTargetFile = ThisWorkbook.Path & "\Employee.xml"
Workbooks.OpenXML FileName:=strTargetFile, LoadOption:=xlXmlLoadImportToList
Application.DisplayAlerts = True
End Sub
</source>

==Using DOM with ADO to Convert Excel Data to XML==



<source lang="vb">

Sub Convert_Excel_Data_to_XML()
Dim oMyconnection As Connection
Dim oMyrecordset As Recordset
Dim oMyXML As DOMDocument
Dim oMyWorkbook As String
Set oMyconnection = New Connection
Set oMyrecordset = New Recordset
Set oMyXML = New DOMDocument
oMyWorkbook = Application.ThisWorkbook.FullName
oMyconnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & oMyWorkbook & ";" & _
"Extended Properties=excel 8.0;" & _
"Persist Security Info=False"
oMyrecordset.Open "Select * from [Sheet1$A1:D43]", oMyconnection, adOpenStatic
oMyrecordset.save oMyXML, adPersistXML
oMyXML.save (ThisWorkbook.Path & "\Output.xml")
oMyrecordset.Close
Set oMyconnection = Nothing
Set oMyrecordset = Nothing
Set oMyXML = Nothing
End Sub
</source>

==XML transform==



<source lang="vb">

Private Sub TransformData()

Application.TransformXML "c:\tests2.xml", "c:\transform.xsl", "c:\tests5.htm"
End Sub
</source>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Excel/Excel XML - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия