Pivot Table Help

[ASteele]

I am wondering if it is possible to update a PivotTable, based on data set in
another worsheet? I have to create a summary report with variable data set
in my "Report" worksheet. I can manually update the PivotTable with the
variable data when in the PivotTable worksheet. However, I would like to
keep the PivotTable data manipulation out of the hands of the users. In
other words, I would prefer them to input pertainant information through an
input dialog and print a report based on criteria set in the "Report"
worksheet.

 

[Patrick Molloy]

you can use another excel workbook as if it were another data source. You can
create a new pivot table using the Data/Pivot Table menu an dfollow the wizard

 

[ASteele]

I was hoping to update the PivotTable through VBA. Creating another
PivotTable through the wizard would require the user to know and understand
PivotTables.

 

[Patrick Molloy]

here's some sample coe that you can adapt ....


Sub BuildPivot()

Dim Conn As ADODB.Connection
Dim RST As ADODB.Recordset
Dim strConn As String
Dim SQL As String
Dim ws As Worksheet
Dim cl As Long
Dim pc As PivotCache

Dim sExcelSourceFile As String

sExcelSourceFile = "E:\Excel\Excel_database\Testdatabase.xls"

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel
8.0;"
strConn = strConn & "Data Source="
strConn = strConn & sExcelSourceFile

Set Conn = New ADODB.Connection
Conn.Open strConn

Set RST = New ADODB.Recordset

SQL = "SELECT * from testdata"
RST.Open SQL, Conn, adOpenStatic

Set ws = Worksheets.Add


Set pc = ThisWorkbook.PivotCaches.Add(xlExternal)

Set pc.Recordset = RST
ws.PivotTables.Add pc, Range("B2")
pc.CreatePivotTable ws.Range("B2"), "table1"



Set ws = Nothing


RST.Close

Conn.Close

Set RST = Nothing

Set Conn = Nothing

End Sub

 

[ASteele]

Thanks for the sample code. However, I get a compile error message with the
first line of the code. Is this for Excel 2007. I am using Excel 2003.