WorksheetMonkey

Hello all,

I am developing an excel vsto application where I am importing some XML data from a webservice on one sheet (called DataSheet) and where a Pivot table is created on a second sheet (called PivotSheet). Once the data has been imported I can then flip to the PivotSheet and create my table.

The problem occurs when I fetch new data (from webservice with different filtering options).

Scenario:

1. I click a button and some data is imported into my DataSheet.

2. I flip to my PivotSheet and build my pivot table manually and it uses the data from the DataSheet.

3. If my initial query returned 10 rows of data, and my second query (after changing some filtering options in my "Import form") returns 20 rows, the pivot table will not read the 20 rows, it will only use 10 rows which is the number of the previous query. Here is a snippet of my code:

Excel.Range mDataRange = null;

private void CreatePivotSheet()

{

// get the used range

mDataRange = (Excel.Range)Globals.DataSheet.UsedRange;

// call the pivotablewizard if no pivot has previously been created using the usedrange as the source

if (mPivotIsLoaded == false)

{

Globals.PivotSheet.PivotTableWizard(

Excel.XlPivotTableSourceType.xlDatabase,

mDataRange,

Globals.PivotSheet.Range["A1", missing], "PivotTable1",

false, false, true, false, missing, missing,

false, false, Excel.XlOrder.xlDownThenOver, missing,

missing, missing);

mPivotIsLoaded = true;

}

else

{

// if we have previously created a pivot, just refresh the cache

Excel.PivotCache pvc = ThisApplication.ActiveWorkbook.PivotCaches().Item(1);

pvc.Refresh();

}

}

So I am creating the pivottable only once and I was assuming that using the pivot would treat the usedrange as something dynamic, which apparently it is not. I would like to either:

1. Tell the pivot to update the range somehow

2. Delete the pivot and create a new one but maintain any formatiing that I have done manually.

I know how to solve number 2 but I do not know how to maintain any formatting of the pivot.

Can anyone shed some light on this matter The pivot refreshes fine however I can't seem to find a way to tell it that the range has become larger than it was when the table was created.

Regards

Oh and I am developing for Excel 2003 using VSTO 2005 (not SE) but I doubt that makes a difference.



Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

Dennis Wallentin

Hi WorksheetMonkey,

See if the method RefreshTable of the Pivottable can update the Pivottable accordingly.






Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

WorksheetMonkey

Hi Dennis,

Thanks for the reply, I'll give it a try on monday however I believe I have tried it already.

I'm not sure what the difference is between refreshing the pivot cache and the table - I mean when I use the Pivottablewizard, it adds the datasource to the cache (or so I am assuming). I'll try refreshing both the cache and the table and see what happens.

By the way, is it possible to create a pivot table without using the PivotTableWizard I think I saw some old vba example where the datasource was added to the cache and then a table was added like:

WorkSheet.PivotCaches().Add(typeofdata, datasource).CreatePivoTable(somethingsomething)

but I couldnat get it to run in C# and vsto 2005.





Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

Dennis Wallentin

Hi,

I decided to create an example on how a) create a Pivottable without the wizard and b) to update the data range & to refresh to Pivotcache.

I'm a VB person (but learning C#!) so here comes the code for it:

Code Snippet

Private Sub Sheet2_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

Dim p_LastRow As Integer = Globals.Sheet1.Range("A65536").End(Excel.XlDirection.xlUp).Row
Dim p_SourceData As Excel.Range = Globals.Sheet1.Range("A1:B" & p_LastRow)
Dim p_ptCache As Excel.PivotCache
Dim p_rnStart As Excel.Range = Me.Range("A1")
Dim p_ptTable As Excel.PivotTable

Try
If Globals.ThisWorkbook.PivotCaches.Count <> 1 Then
p_ptCache = Globals.ThisWorkbook.PivotCaches.Add( _
SourceType:=Excel.XlPivotTableSourceType.xlDatabase, _
SourceData:=p_SourceData)

p_ptTable = p_ptCache.CreatePivotTable(TableDestination:=p_rnStart)

With p_ptTable
.ManualUpdate = False
.Name = "Test"
.AddFields(RowFields:="ShipCountry")
With .PivotFields("Freight")
.Orientation = Excel.XlPivotFieldOrientation.xlDataField
.Function = Excel.XlConsolidationFunction.xlSum
End With
.ManualUpdate = True
End With

Else
With Globals.ThisWorkbook.PivotCaches(1)
.SourceData = p_SourceData
.EnableRefresh = True
.Refresh()
End With
End If

Catch ex As Exception
MessageBox.Show(ex.Message.ToString)

Finally

End Try

End Sub






Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

WorksheetMonkey

Hi Dennis, thanks again for the help.

I can successfully create the pivottable, however an exception is thrown when I attempt to set the SourceData property.

The exception is just rubbish and doesn't give me any info. This is the source of my problem as I am unable to update the range as the new datasource.

There must eb a way to set the datasource! If not then I would like to clear the pivotcache and create a new one, but I haven't managed to do that either.

Here is your example in c# without the manually adding of fields

Code Snippet

// get the last used row number in the system.

int lLastRow = Globals.DataSheet.Range["A65536", missing].get_End(Excel.XlDirection.xlUp).Row;

System.Diagnostics.Debug.WriteLine("lastRow is " + lLastRow.ToString());

Excel.Range lSourceData = Globals.DataSheet.Range["A1", "U" + lLastRow.ToString()];

// the cache

Excel.PivotCache lPivotTableCache;

// starting range

Excel.Range lRangeStart = Globals.PivotSheet.Range["A1", missing];

Excel.PivotTable lPivotTable;

try

{

// if there are no pivotcaches

if (Globals.ThisWorkbook.PivotCaches().Count < 1)

{

lPivotTableCache = Globals.ThisWorkbook.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, lSourceData);

lPivotTable = lPivotTableCache.CreatePivotTable(lRangeStart, missing, missing, missing);

lPivotTable.ManualUpdate = false;

lPivotTable.Name = "Test";

}

else

{

Globals.ThisWorkbook.PivotCaches().Item(1).SourceData = lSourceData;

Globals.ThisWorkbook.PivotCaches().Item(1).EnableRefresh = true;

Globals.ThisWorkbook.PivotCaches().Item(1).Refresh();

}

}

catch(Exception ex)

{

MessageBox.Show(ex.Message.ToString());

}





Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

WorksheetMonkey

Hi Dennis, thanks again for the help.

I can successfully create the pivottable, however an exception is thrown when I attempt to set the SourceData property.

The exception is just rubbish and doesn't give me any info. This is the source of my problem as I am unable to update the range as the new datasource.

There must eb a way to set the datasource! If not then I would like to clear the pivotcache and create a new one, but I haven't managed to do that either.

Here is your example in c# without the manually adding of fields

Code Snippet

// get the last used row number in the system.

int lLastRow = Globals.DataSheet.Range["A65536", missing].get_End(Excel.XlDirection.xlUp).Row;

System.Diagnostics.Debug.WriteLine("lastRow is " + lLastRow.ToString());

Excel.Range lSourceData = Globals.DataSheet.Range["A1", "U" + lLastRow.ToString()];

// the cache

Excel.PivotCache lPivotTableCache;

// starting range

Excel.Range lRangeStart = Globals.PivotSheet.Range["A1", missing];

Excel.PivotTable lPivotTable;

try

{

// if there are no pivotcaches

if (Globals.ThisWorkbook.PivotCaches().Count < 1)

{

lPivotTableCache = Globals.ThisWorkbook.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, lSourceData);

lPivotTable = lPivotTableCache.CreatePivotTable(lRangeStart, missing, missing, missing);

lPivotTable.ManualUpdate = false;

lPivotTable.Name = "Test";

}

else

{

Globals.ThisWorkbook.PivotCaches().Item(1).SourceData = lSourceData;

Globals.ThisWorkbook.PivotCaches().Item(1).EnableRefresh = true;

Globals.ThisWorkbook.PivotCaches().Item(1).Refresh();

}

}

catch(Exception ex)

{

MessageBox.Show(ex.Message.ToString());

}





Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

Dennis Wallentin

Hi,

I'm not surprised as I've taken part of this scenario in other threads here, i e code works in VB but not in C#...

The following VB snippet shows how to delete pivotcache and pivottable. It's clumsy but at least it should work in C# as well.

Code Snippet

Globals.Sheet2.Cells.Delete()






Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

WorksheetMonkey

Hi Dennis,

Thanks for your patience Smile I might try porting to VB but I really shouldn't have to hehe.

The code you provided works, I have tested it previously. The problem is that it not only destroys the cache but the pivot table itself. It really wouldnt be a problem for me to destroy the pivottable and recreate it, I know this approach works, however there is one big problem with doing so. If the user has dragged fields into the pivottable, when the table is refreshed, the fields will disappear (as the table is destroyed) - so I would be looking for a way to copy the fields from my old table on to my new one.

In pseudo:

1. store the old table in a variable i.e. oldTable

2. Delete the contents of the sheet as you described

3. create a new cache and pivottable as you described

4. copy the fields from the old table on to the new one

The problem is that I cant seem to access the fields.I am assuming the Pivottable.PivotFields() should give me the fields added by the user (if he/she has added any) however I can't seem to assign the fields to the new table. Do you have any idea how this might be done

Cheers





Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

Topoly

I also had a similar problem, I have created my pivot table with data from the data sheet, but using the following for my data range - 'DataSheet'!$A: $F instead of specifying rows as part of the range

When a row is updated it successfully updates the pivot table. Which may or may not solve your problem.

My problem is that I am always left with a '(Blank)' entry for every column which causes problems when attempting to filter the data by date or time.

Does anybody have any ideas how I can remove this





Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

WorksheetMonkey

Could you give a code example of it I started off using the UsedRange property of the datasheet which gives me the rows and columns currently in use by a sheet - maybe you could try that. For me the range is not the problem, it is telling the cache/pivottable to use the updated range. Gonna hack away at work tomorrow, will post any results here. (Last resort is a VB rewrite)



Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

WorksheetMonkey

Ok so ind esperation I ported my code to VB. As expected I still get an exeption thrown when I try to change the source data

With Globals.ThisWorkbook.PivotCaches(1)

.SourceData = lSourceData

.EnableRefresh = True

.Refresh()

End With

I get the very informative message "Exception from hresult: 0x800A03EC" whenever I attempt to change the sourcedata.

Could this have something to do with running VSTo 2005 and Excel 2003 Dennis, are you positive that your example works

I am using an xml map to import data into a sheet - but that shouldn't make any difference as I am just using a range as a source should it





Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

Dennis Wallentin

Hi,


I might try porting to VB but I really shouldn't have to hehe.

Well, in my opinion there is no need to switch language. After all, VB and C# should be similar by all means. UsedRange can be used if the table starts in A1 and end somewhere else but so far it has not been an issue regarding this.

The solution works on my dev machine. I re-read the thread and noticed that we didn't check to use the RefreshTable.

The following works for me:

Code Snippet
Dim ptTable As Excel.PivotTable = Me.PivotTables(1)
MessageBox.Show(ptTable.DataBodyRange.Address.ToString)
ptTable.RefreshTable()
MessageBox.Show(ptTable.DataBodyRange.Address.ToString)

But the following does not work:

Code Snippet

Dim p_LastRow As Integer = Globals.Sheet1.Range("A65536").End(Excel.XlDirection.xlUp).Row
Dim p_SourceData As Excel.Range = Globals.Sheet1.Range("A1:B" & p_LastRow)
Dim ptTable As Excel.PivotTable = Me.PivotTables(1)

MessageBox.Show(ptTable.DataBodyRange.Address.ToString)
ptTable.SourceData = p_SourceData '<----- Stops here.
ptTable.RefreshTable()
MessageBox.Show(ptTable.DataBodyRange.Address.ToString)

As for iterating through the collection of fields the following approach can be applied:

Code Snippet

Dim ptTable As Excel.PivotTable = Me.PivotTables(1)
Dim ptFields As Excel.PivotFields = ptTable.PivotFields
Dim ptField As Excel.PivotField

For Each ptField In ptFields
MessageBox.Show(ptField.Name)
Next






Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

WorksheetMonkey

Hi Dennis,

The refreshtable does not work for me. Well, it does as long as the range does not get any bigger. This is exactly the problem I had in the first place. I will create a small example and perhaps we can see if it behaves differently on your machine.

Watch this space Smile





Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

Dennis Wallentin

Hi,

Yes please as it is interesting to learn more.






Re: Visual Studio Tools for Office PivotTable will not update with new range - please help

WorksheetMonkey

Ok here we go Wink

This small example illustrates the issue (hopefully). I have created a new Excel Workbook in VSTO and this is the code for the Workbook.

The code will bring up an actions pane with a button on startup. When the button is clicked, the app will attempt to create a pivot table from the data on Sheet1 starting from Cell A1 to C(whatever row). As you can see the datasource is dynamic (or is supposed to be).

So to illustrate the problem, take the following steps:

1. Run the program

2. Copy the following into cell A1 on sheet1

TESTING THIS APP
1 1 1
2 2 2
3 3 3

This should populate cells A1 to C3 with the text as headers.

3. Click the button. You will see some dialog messages displaying the current data range

4. Switch to Sheet2. A pivot table has now been created.

5. Drag the 3 fields into the pivot table so that you can see some data.

6. Go back to sheet1.

7. add a new row on sheet1 (e.g. 4 4 4)

8. Go back to sheet2 and click the button again

You will see that the range displayed in the dialog has now increased by one row, however the pivottable is unchanged!

9. Modify one of the first 3 rows and you will find that the pivottable is being refreshed, but it is only using the first 3 rows of data.

Code Snippet

Public Class ThisWorkbook

Dim WithEvents btnTest As Button

Private Sub ThisWorkbook_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

btnTest = New Button()

btnTest.Text = "CLICK ME"

' add the button to the pane

Me.ActionsPane.Controls.Add(btnTest)

End Sub

Private Sub btnLogin_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnTest.Click

CreateAndRefreshPivot()

End Sub

Private Sub ThisWorkbook_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown

End Sub

Public Sub CreateAndRefreshPivot()

' get the last used row number in the system.

Dim lLastRow As Integer = Globals.Sheet1.Range("A65536").End(Excel.XlDirection.xlUp).Row

'System.Diagnostics.Debug.WriteLine("lastRow is " + lLastRow.ToString())

Dim lSourceData As Excel.Range = Globals.Sheet1.Range("A1", "C" + lLastRow.ToString())

' starting range

Dim lRangeStart As Excel.Range = Globals.Sheet2.Range("A1")

MessageBox.Show("SourceData : " & lSourceData.Address.ToString())

If ThisApplication.ActiveWorkbook.PivotCaches().Count <> 1 Then

ThisApplication.ActiveWorkbook.PivotCaches.Add(SourceType:=Excel.XlPivotTableSourceType.xlDatabase, SourceData:=lSourceData.Address.ToString()).CreatePivotTable(TableDestination:="Sheet2!R1C1", TableName:= _

"PivotTable1", DefaultVersion:=Excel.XlPivotTableVersionList.xlPivotTableVersion10)

Else

Dim pt As Excel.PivotTable = Globals.Sheet2.PivotTables("PivotTable1")

MessageBox.Show(pt.DataBodyRange.Address.ToString)

pt.RefreshTable()

MessageBox.Show(pt.DataBodyRange.Address.ToString)

ThisApplication.ActiveWorkbook.PivotCaches().Item(1).Refresh()

End If

End Sub

End Class

I hope we can solve this, I have been struggling with this for a week now hehe. How do we get the pivot to realise that it should take the new range into account. Refreshing the cache doesn't work and neither does refreshing the table in this example.