Connect Pivot Table Web Compoment on WebPage

[hglamy]

Hello,

while experimenting with the OWC's of MS Office 2003 Beta,
I try to figure out how to get a Pivot Table connected to data
in a Spreadsheet on the same WebPage - and I don't get anywhere.

Any assistance will be appreciated.

Thank you in advance,

Regards,

H.G. Lamy

 

[Dan Ricker]

OWC 9/10/11 PivotTable Control does not have the
capability of binding to the respective OWC 9/10/11
SpreadSheet control.

The OWC 9/10/11 Chart can bind to the respective
OWC9/10/11 SpreadSheet control.

**************
I've thought about this one before...

The only thing I could think of (but have not tried) is
using an intermediate ADODB.RecordSet object and hooking
up SpreadSheet Events to keep things sync'ed.

''' Sample RecordSet build code
''' Warning Air Code (created in my mind and never run)
Public Function GetADODBRecordSetFromSpreadSheet()
Dim objRS
Dim lRecID
Dim vntTmp

On Error Resume Next

Set GetADODBRecordSet = Nothing
''' Create the recordset. If can't create then fail
Set objRS = CreateObject("ADODB.Recordset")
If Err.Number <> 0 Then
Msgbox "Create Recordset object error: " & CStr
(Err.Number) & " " & Err.Description
Set objRS = Nothing
Exit Function
End If

For Each objCell in SpreadSheet1.Sheets
(1).UsedRange.Resize(1, 0).Cells '' or something like that
''' Add The desired fields to the record set
objRS.Fields.Append objCell.Value,
fnGetDataTypeFromCell(objCell)
Next

objRS.Open

If Err.Number <> 0 Then
Msgbox "Open Recordset error: " & CStr(Err.Number)
& " " & Err.Description
objRS.Close
Set objRS = Nothing
Exit Function
End If

For lRow = 2 to SpreadSheet1.UsedRange.Rows.Count
objRS.AddNew
For lCol = 1 to SpreadSheet.Sheets
(1).UsedRange.Columns.Count
objRS.Fields(lCol).Value = SpreadSheet1.Sheets
(1).Cells(lRow,lCol).Value
objRS.Update
If Err.Number <> 0 Then
Msgbox "Row " & CStr(lRow) & " Col" & CStr
(lCol) & " Data error: " & CStr(Err.Number) & " " &
Err.Description
objRS.Close
Set objRS = Nothing
Exit Function
End If
Next
Next

objRS.UpdateBatch
Set GetADODBRecordSetFromSpreadSheet = objRS

End Function