ต้องการดึงข้อมูลจากเอกเซลไฟล์อื่นมา pivot ด้วย VBA ช่วยดู code ให้หน่อยครับ

[zern]

โจทย์ที่ต้องการคือ มีข้อมูลอยู่ใน excel อีกไฟล์ ซึ่งข้อมูลมีหลายล้าน row จึงต้องการนำข้อมูลเหล่านั้นมา pivot ในไฟล์เดียวด้วย VBA
เขียนโค้ดดึงข้อมูลมาได้ แต่ไม่รู้ว่าจะ pivot โดยไม่วางบนตารางเอกเซลยังไง ไม่รู้ว่าต้องประกาศ object อะไรเพิื่อเก็บค่านั้นมา pivot น่ะครับ

ผมได้แนบไฟล์ 2 ไฟล์
1 test query >> เป็นไฟล์ข้อมูลเพื่อนำไป pivot
2 pivotmillionrow >> เป็นไฟล์สำหรับแสดงผล pivot ที่อิงข้อมูลจากไฟล์แรก ซึ่งเขียน macro ไว้ในไฟล์นี้

แนบโค้ดเพิ่มให้ดูในกระทู้ ซึ่งจุดที่ผิดแน่ๆคือ SrcData = CopyFromRecordset rs แต่ไม่มีไอเดียว่าต้องใส่ที่ถูกยังไง

test Query.xlsx

(9.13 KiB) Downloaded 9 times

pivotmillionrow.xlsm

(21.13 KiB) Downloaded 6 times

Code: Select all

Sub CreatePivotTable()
'PURPOSE: Creates a brand new Pivot table on a new worksheet from data in the ActiveSheet
'Source: www.TheSpreadsheetGuru.com
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

strFile = "D:/test Query.xlsx"
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = [D1] '& " Union " & [D2]
rs.Open strSQL, cn

Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As Object

'Determine the data range you want to pivot
  SrcData = CopyFromRecordset rs
  
'Create a new worksheet
  Set sht = Sheets.Add

'Where do you want Pivot Table to start?
  StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

'Create Pivot Cache from Source Data
  Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=SrcData)

'Create Pivot table from Pivot Cache
  Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=StartPvt, _
    TableName:="PivotTable1")

End Sub

[zern]

มั่วไปมั่วมาได้ละครับ แต่คราวนี้มีปัญหาว่าใช้ slicer ไม่ได้ มีวิธีให้ใช่ pivot slicer ได้มั้ยครับ

Code: Select all

Sub CreatePivotTable2()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim splitsql As Variant

strFile = "D:/test Query.xlsx"
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = [H1] '& " Union " & [D2]
rs.Open strSQL, cn


splitsql = strSQL

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "OLEDB;" & cn
.CommandType = xlCmdSql
.CommandText = splitsql
.CreatePivotTable TableDestination:= _
"Sheet1!R1C14", TableName:="PivotTable", _
DefaultVersion:=xlPivotTableVersion10
End With

End Sub

[snasui]

ยังไม่พบวิธีที่สร้าง Slicer จากการดึงข้อมูลด้วย ADODB ครับ