技術(shù) 點(diǎn)
- 技術(shù)
- 點(diǎn)
- V幣
- 點(diǎn)
- 積分
- 35
|
2#
發(fā)表于 2015-4-5 15:47:46
|
只看該作者
- 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(數(shù)量) 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
復(fù)制代碼 |
本帖子中包含更多資源
您需要 登錄 才可以下載或查看,沒有帳號?注冊
x
|