关于三个条数据引用（求救）

fxl447098457

1. Sub MyQuery()
   
2.   Dim cnn As Object
   
3.   Set cnn = CreateObject("ADODB.Connection")
   
4.   Dim rs As Object
   
5.    Set rs = CreateObject("ADODB.Recordset")
   
6.   Dim sql As String
   
7.   Dim mybook As String
   
8.   mybook = ThisWorkbook.FullName
   
9.   With cnn
   
10.     If Application.Version = "11.0" Then
    
11.       .Provider = "microsoft.jet.oledb.4.0"
    
12.       .ConnectionString = "extended properties=""excel 8.0;HDR=YES;"";data source=" & mybook
    
13.     Else
    
14.       .Provider = "microsoft.ACE.oledb.12.0"
    
15.       .ConnectionString = "extended properties=""excel 12.0;HDR=YES;"";data source=" & mybook
    
16.     End If
    
17.     .Open
    
18.   End With
    
19.   sql = "transform sum(数量) select  料号,批号 from [" & ThisWorkbook.Path & "" & "出库表.xls].[Sheet1$] group by 料号,批号 pivot 部门"
    
20.   rs.Open sql, cnn, 1, 3
    
21.   With Worksheets("Sheet1")
    
22.     .Cells.ClearContents
    
23.     For j = 0 To rs.Fields.Count - 1
    
24.       .Cells(1, j + 1) = rs.Fields(j).Name
    
25.     Next
    
26.     .Range("a2").CopyFromRecordset rs
    
27.   End With
    
28. End Sub
    

复制代码