в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:04Z

1 версия

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

==Creating a Custom XML List==



<source lang="vb">

Sub CreateXMLList()
Dim oMyMap As XmlMap
Dim strXPath As String
Dim oMyList As ListObject
Dim oMyNewColumn As ListColumn
ThisWorkbook.XmlMaps.Add (ThisWorkbook.Path & "\Myschema.xsd")
Set oMyMap = ThisWorkbook.XmlMaps("EmployeeSales_Map")
Range("A1").Select
Set oMyList = ActiveSheet.ListObjects.Add
strXPath = "/EmployeeSales/Employee/Empid"
oMyList.ListColumns(1).XPath.SetValue oMyMap, strXPath
Set oMyNewColumn = oMyList.ListColumns.Add
strXPath = "/EmployeeSales/Employee/InvoiceNumber"
oMyNewColumn.XPath.SetValue oMyMap, strXPath
Set oMyNewColumn = oMyList.ListColumns.Add
strXPath = "/EmployeeSales/Employee/InvoiceAmount"
oMyNewColumn.XPath.SetValue oMyMap, strXPath
oMyList.ListColumns(1).Name = "EmployeeId"
oMyList.ListColumns(2).Name = "Invoice Number"
oMyList.ListColumns(3).Name = "Invoice Amount"
End Sub
</source>

==Inspecting a ListObject==



<source lang="vb">

" Example using various list properties
Sub ListInfo()
Dim myWorksheet As Worksheet
Dim lo As ListObject
Dim lc As ListColumn
Set myWorksheet = ThisWorkbook.Worksheets("ListObjects")
Set lo = myWorksheet.ListObjects(1)
For Each lc In lo.ListColumns
Debug.Print lc.Name
Debug.Print lc.Index
Debug.Print lc.Range.Address
Debug.Print GetTotalsCalculation(lc.TotalsCalculation)
Next
Debug.Print lo.HeaderRowRange.Address
Debug.Print lo.DataBodyRange.Address
If Not lo.InsertRowRange Is Nothing Then
Debug.Print lo.InsertRowRange.Address
Else
Debug.Print "N/A"
End If
If lo.ShowTotals Then
Debug.Print lo.TotalsRowRange.Address
Else
Debug.Print "N/A"
End If
Debug.Print lo.Range.Address
Debug.Print lo.ShowTotals
Debug.Print lo.ShowAutoFilter
Set lc = Nothing
Set lo = Nothing
Set myWorksheet = Nothing
End Sub
Function GetTotalsCalculation(xlCalc As XlTotalsCalculation) As String
Select Case xlCalc
Case Is = XlTotalsCalculation.xlTotalsCalculationAverage
GetTotalsCalculation = "Average"
Case Is = XlTotalsCalculation.xlTotalsCalculationCount
GetTotalsCalculation = "Count"
Case Is = XlTotalsCalculation.xlTotalsCalculationCountNums
GetTotalsCalculation = "CountNums"
Case Is = XlTotalsCalculation.xlTotalsCalculationMax
GetTotalsCalculation = "Max"
Case Is = XlTotalsCalculation.xlTotalsCalculationMin
GetTotalsCalculation = "Min"
Case Is = XlTotalsCalculation.xlTotalsCalculationNone
GetTotalsCalculation = "None"
Case Is = XlTotalsCalculation.xlTotalsCalculationStdDev
GetTotalsCalculation = "StdDev"
Case Is = XlTotalsCalculation.xlTotalsCalculationSum
GetTotalsCalculation = "Sum"
Case Is = XlTotalsCalculation.xlTotalsCalculationVar
GetTotalsCalculation = "Var"
Case Else
GetTotalsCalculation = "Unknown"
End Select
End Function
</source>

==To create a table from cells A1:F6, and assuming the table has column headers==



<source lang="vb">

Sub table()
ActiveSheet.ListObjects.add(xlSrcRange, range("$A$1:$F$6"), , xlYes).name = "Table1"
End Sub
</source>

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

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

в 16:33, 26 мая 2010

Admin: 1 версия