关于宏函数控制透视表布局的问题

snipersun

用宏函数添加的xlDataField默认是行显示的，我要让他列显示，不知道是用那个函数

祥情请看test.xsl,里面sheet2是默认的样式,sheet3是我要的样式

2qfPAfys.rar (10.26 KB, 下载次数: 18)

2006-7-20 16:42 上传

点击文件名下载附件
关于宏函数控制透视表布局的问题

[此贴子已经被作者于2006-7-20 16:42:19编辑过]

snipersun

没人知道？！！

我把代码贴出来

Sub start()
    Dim cnnConn As ADODB.Connection
    Dim rstRecordset As ADODB.Recordset
    Dim cmdCommand As ADODB.Command
   
     'Open the connection.
    Set cnnConn = New ADODB.Connection
    With cnnConn
        .ConnectionString = _
            "Provider=Microsoft.Jet.OLEDB.4.0"
        .Open "E:\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb"
    End With
 'cnnConn.Open strConn
    ' Set the command text.
    Set cmdCommand = New ADODB.Command
    Set cmdCommand.ActiveConnection = cnnConn
    With cmdCommand
        .CommandText = "select * from 订单"
        .CommandType = adCmdText
        .Execute
    End With
   
    ' Open the recordset.
    Set rstRecordset = New ADODB.Recordset
    Set rstRecordset.ActiveConnection = cnnConn
    rstRecordset.Open cmdCommand
   
    ' Create a PivotTable cache and report.
    'Dim objPivotCache As PivotCache
    Set objPivotCache = ThisWorkbook.PivotCaches.Add( _
        SourceType:=xlExternal)
    Set objPivotCache.Recordset = rstRecordset
   
    'Dim myTable As PivotTable
    Worksheets(2).Cells.Clear
    Set myTable = objPivotCache.CreatePivotTable(Worksheets(2).Range("a1"), "table", True)

    With myTable
        .SmallGrid = False
        '.RowGrand = False
        .PrintTitles = True
        .MergeLabels = True
        '.ColumnGrand = False
        .EnableFieldList = False
        With .PivotFields("货主地区")
            .Orientation = xlRowField
            .Position = 1
            .Subtotals(1) = False
        End With
        With .PivotFields("货主城市")
            .Orientation = xlRowField
            .Position = 2
        End With
        With .PivotFields("雇员ID")
            .Orientation = xlDataField
            .Position = 1
        End With
        With .PivotFields("运货费")
            .Orientation = xlDataField
            .Position = 1
        End With
    End With

    Worksheets(2).Cells.Font.Size = 9
    ' Close the connections and clean up.
    cnnConn.Close
    Set cmdCommand = Nothing
    Set rstRecordset = Nothing
    Set cnnConn = Nothing
End Sub