Hi all, my first post in the forum for a problem that has annoying me for some time now. I am trying to take the union between different sheets to consolidate the data and then create a pivot table that can be refreshed by a click of a button. I have something that works in Excel 2007 but does not in Excel 2010 for some reason I do not understand.
Option Explicit
Sub Create_Pivot_Source()
'Credit to XL-Dennis for the original code ideas.
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String, stSQL As String
Dim i As Long, lnMode As Long
Dim pvc As PivotCache
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Blad1")
stCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & wbBook.FullName & ";" & _
"Extended Properties=""Excel 12.0;HDR=NO"";"
stSQL = "SELECT * FROM [Blad2$] UNION ALL SELECT * FROM [Blad3$]"
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
cnt.Open stCon
rst.Open stSQL, cnt, adOpenStatic, adLockOptimistic
Set pvc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set pvc = Worksheets("Blad1").PivotTables("Pivottabell29").PivotCache
Set pvc.Recordset = rst
I have a pivot table named 29 but I get an error on the last row, after that I thought I should be able to just use refresh and have everything working alright.
I would appreciate if someone has any ideas on this also some references to books where I can find the good information on these things as it seems quite hard to get a detailed description and such also a lot of books are old do not know if they cover the latest 2010 version and such.
Option Explicit
Sub Create_Pivot_Source()
'Credit to XL-Dennis for the original code ideas.
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stCon As String, stSQL As String
Dim i As Long, lnMode As Long
Dim pvc As PivotCache
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Blad1")
stCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & wbBook.FullName & ";" & _
"Extended Properties=""Excel 12.0;HDR=NO"";"
stSQL = "SELECT * FROM [Blad2$] UNION ALL SELECT * FROM [Blad3$]"
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
cnt.Open stCon
rst.Open stSQL, cnt, adOpenStatic, adLockOptimistic
Set pvc = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set pvc = Worksheets("Blad1").PivotTables("Pivottabell29").PivotCache
Set pvc.Recordset = rst
I have a pivot table named 29 but I get an error on the last row, after that I thought I should be able to just use refresh and have everything working alright.
I would appreciate if someone has any ideas on this also some references to books where I can find the good information on these things as it seems quite hard to get a detailed description and such also a lot of books are old do not know if they cover the latest 2010 version and such.
