Pivot Tables and VBA: Changing Contents in Data Area and updating the Data Source

**kyrgyzstanart** · 07-06-2008, 03:42 AM

Hello all,

I need:

1. Change some of the content in the data area of a Pivot Table. I know how to do that w/ the EnableDataValueEditing property.
2. Write-back these changes to the Data Source. Got no clue how to do that...

Example,

1. Data source is:

Name Qty
Mike 50
Mike 100
Mike 200

2. Pivot Table:

Row Labels Sum of Qty
Mike 350

3. Manual Change in the Data area:

Row Labels Sum of Qty
Mike 35

4. Data Source is updated like this:

Name Qty
Mike 5
Mike 10
Mike 20

Is there any way to do that w/ VBA and Excel 2007?

**DaveyB** · 07-06-2008, 06:33 PM

Well yeh you could but the way I know how to do it would be ugly but it would work. (I can't check my code before pasting though as I don't have access to access). he he

You can use a change event on the sheet to detect the change and use ADODB to build the database connection by adding a component to an invisible form.

Once this is done you could use code similar to this

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

Private Sub Worksheet_Change(ByVal Target As Range)
cn.Open "Driver={Microsoft Access Driver (*.Mdb)};DBQ=C:\database.mdb"

Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM table WHERE..", cn, adOpenKeyset, adLockOptimistic

rs.AddNew
rs!fieldName = Range(Target.Address).value
rs.Update
rs.Close
cn.Close

End Sub

Please let me know how it go's

**kyrgyzstanart** · 07-06-2008, 06:54 PM

Is there no way to do that w/ VBA and Excel 2007 only? I am not good in Access.

**DaveyB** · 07-06-2008, 07:00 PM

Yeh, it would be a whole lot easier as well. I misread what you wanted to do.

What file are you linking to as you data source? I assumed you were linking to an external data source like a database but I guess I was wrong csv, xls, xla???

Could you upload what you are wanting to change and I will take a look at it.

Chances are it will be tomorrow now as its very late over here.

**kyrgyzstanart** · 07-07-2008, 07:49 AM

DaveyB

Hi DaveyB, please, find the table in the attached file. There are two Worksheets:

1. DATA_SOURCE containing the data on which the pivot table is based.
2. PIVOT_TABLE - the pivot table itself.

The pivot table shows 350 for Mike - which is a sum of Qty for all "Mikes" in the DATA_SOURCE. I would like to be able to put 35 in the PIVOT_TABLE instead of 350 and then to see this change in the DATA as well (proportionally).

Thx!

PS: I hope you are using Excel 2007, otherwise the pivot table won't work, but you can create a Pivot table based off the data yourself if you use Office 2003.