关于三个条数据引用（求救） - Office中国论坛/Access中国论坛

Sub MyQuery()
Dim cnn As Object
Set cnn = CreateObject("ADODB.Connection")
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
Dim sql As String
Dim mybook As String
mybook = ThisWorkbook.FullName
With cnn
If Application.Version = "11.0" Then
.Provider = "microsoft.jet.oledb.4.0"
.ConnectionString = "extended properties=""excel 8.0;HDR=YES;"";data source=" & mybook
Else
.Provider = "microsoft.ACE.oledb.12.0"
.ConnectionString = "extended properties=""excel 12.0;HDR=YES;"";data source=" & mybook
End If
.Open
End With
sql = "transform sum(数量) select 料号,批号 from [" & ThisWorkbook.Path & "" & "出库表.xls].[Sheet1$] group by 料号,批号 pivot 部门"
rs.Open sql, cnn, 1, 3
With Worksheets("Sheet1")
.Cells.ClearContents
For j = 0 To rs.Fields.Count - 1
.Cells(1, j + 1) = rs.Fields(j).Name
Next
.Range("a2").CopyFromRecordset rs
End With
End Sub

复制代码