Lm th? no ? s? d?ng cc thnh ph?n Web PivotTable vn ph?ng v?i VB

D?ch tiu ? D?ch tiu ?
ID c?a bi: 235542
Bung t?t c? | Thu g?n t?t c?

? Trang ny

TM T?T

Bi vi?t ny ch?ng t? lm th? no ? s? d?ng vn ph?ng PivotTable Web thnh ph?n ? hi?n th? thng tin trn m?t h?nh th?c Visual Basic.

THNG TIN THM

C?u ph?n Web Office l m?t b? i?u khi?n cho php lu d? li?u vn ph?ng. B?i v? cc thnh ph?n Web l i?u khi?n, b?n c th? ?t chng vo m?t Visual Basic l?p v t? ?ng ho cc thnh ph?n ? hi?n th? d? li?u.

Xy d?ng m?u t? ?ng ha

  1. B?t ?u Visual Basic v t?o ra m?t d? n m?i EXE tiu chu?n. Form1 ?c t?o ra theo m?c ?nh.
  2. D?i Project|Tham khao, thm m?t tham chi?u ?n cc Microsoft ActiveX Data Objects 2.1 Library v sau nh?p vo Ok.
  3. Bo ch cc CTRL + T phm ? hi?n th? cc Thm cc thnh ph?n h?p tho?i. Thm cc thnh ph?n Web Microsoft Office, v sau Nh?p vo Ok.

    LU ?: Office XP bao g?m c? Office 2000 v Office XP Web Cc thnh ph?n. ? s? d?ng Office 2003 c?u ph?n Web, thm Microsoft Office Web Th vi?n thnh ph?n 11,0. ? s? d?ng cc thnh ph?n Office 2000 Web, thm cc Microsoft Office Web thnh ph?n 9,0 th vi?n. ? s? d?ng Office XP Web Cc thnh ph?n, thm th vi?n Microsoft Office Web thnh ph?n 10,0.
  4. Thm m?t PivotTable m?u.
  5. Trong c?a s? m? cho Form1, chn o?n m? sau:
    Option Explicit
    
    Dim cnnConnection As Object
    
    Private Sub Form_Load()
    
       Dim strProvider As String
       Dim view As PivotView
       Dim fsets As PivotFieldSets
       Dim c As Object
       Dim newtotal As PivotTotal
       
       strProvider = "Microsoft.Jet.OLEDB.4.0"
       ' Create an ADO object
       Set cnnConnection = CreateObject("ADODB.Connection")
       ' Set the provider and open the connection to the database
       cnnConnection.Provider = strProvider
       cnnConnection.Open "C:\pivottest.mdb"
       ' Set the pivot table's connection string to the cnnConnection's connection string
       PivotTable1.ConnectionString = cnnConnection.ConnectionString
       ' SQL statement to get everything from table1
       PivotTable1.CommandText = "Select * from table1"
       
       ' Get variables from the pivot table
       Set view = PivotTable1.ActiveView
       Set fsets = PivotTable1.ActiveView.FieldSets
       Set c = PivotTable1.Constants
       
       ' Add Category to the Row axis and Item to the Column axis
       view.RowAxis.InsertFieldSet fsets("Category")
       view.ColumnAxis.InsertFieldSet fsets("Item")
       
       ' Add a new total - Sum of Price
       Set newtotal = view.AddTotal("Sum of Price", view.FieldSets("Price").Fields(0), c.plFunctionSum)
       view.DataAxis.InsertTotal newtotal
       view.DataAxis.InsertFieldSet view.FieldSets("Price")
          
       ' Set some visual properties
       PivotTable1.DisplayExpandIndicator = False
       PivotTable1.DisplayFieldList = False
       PivotTable1.AllowDetails = False
    End Sub
    
    Private Sub Form_Terminate()
      ' Remove reference to the ADO object
      Set cnnConnection = Nothing
    End Sub
    
    
    Private Sub PivotTable1_DblClick()
       Dim sel As Object
       Dim pivotagg As PivotAggregate
       Dim sTotal As String
       Dim sColName As String
       Dim sRowName As String
    
       Dim sMsg As String
       
       ' Get the selection object you double-clicked on
       Set sel = PivotTable1.Selection
       ' If it is a aggregate, you can find information about it
       If TypeName(sel) = "PivotAggregates" Then
          ' Select the first item
    
          Set pivotagg = sel.Item(0)
          ' Display the value
          MsgBox "The cell you double-clicked has a value of '" & pivotagg.Value & "'.", vbInformation, "Value of Cell"
          
          ' Get variables from the cell
          sTotal = pivotagg.Total.Caption
          sColName = pivotagg.Cell.ColumnMember.Caption
          sRowName = pivotagg.Cell.RowMember.Caption
          
          ' Display the row and column name
          sMsg = "The value is " & sTotal & " by " & sRowName & " by " & sColName
          MsgBox sMsg, vbInformation, "Value Info"
       End If
    End Sub
    					
  6. Sau , t?o ra m?t c s? d? li?u m?u b?ng cch s? d?ng Microsoft Access. B?t ?u truy c?p v ch?n Tr?ng truy c?p c s? d? li?u. Lu cc t?p tin nh c:\pivottest.mdb. B?m p T?o b?ng theo quan i?m thi?t k?. Nh?p d? li?u sau:

    Thu g?n b?ng nyBung r?ng b?ng ny
    Tn tr?ngKi?u d? li?u
    Kho?n m?cVn b?n
    Th? lo?iVn b?n
    GiS?
  7. ng c?a s? v lu cc thay ?i nh Table1. Ch?n Khng ? t?o m?t phm. B?m p vo Table1 v nh?p d?i y d? li?u:

    Thu g?n b?ng nyBung r?ng b?ng ny
    Kho?n m?cTh? lo?iGi
    Mng tayPh?n c?ng5
    o s miQu?n o23
    BaPh?n c?ng16
    Bnh sandwichTh?c ph?m5
    Qu?nQu?n o31
    ? u?ngTh?c ph?m2
  8. ng truy c?p ? lu t?p tin.
  9. Bo ch cc F5 ch?a kha ? ch?y cc d? n. Sau khi ch?y, pivot b?ng nn c. B?m-p vo ngy m?t t? bo v?i d? li?u cho th?y gi tr? c?a cc t? bo v cc t? bo c?t v d?ng tn.

THAM KH?O

? bi?t thm thng tin v? vn ph?ng t? ?ng ha, truy c?p vo cc Pht tri?n vn ph?ng Microsoft h? tr? trang web t?i:
http://support.Microsoft.com/ofd

Thu?c tnh

ID c?a bi: 235542 - L?n xem xt sau cng: 21 Thang Tam 2011 - Xem xt l?i: 2.0
T? kha:
kbautomation kbhowto kbweb kbmt KB235542 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:235542

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com