'[vb.net][Visual Studio] Refreshing Excel pivot retuns an error
I use the code below to test the refresh speed of an excel sheet. The problem is, when I choose to refresh the pivot table, I get the error in the print screen attached. The weird thing is that I hit continue and it continues with no issue, and does what it's supposed to be (refreshes the pivot), and the error is not repeated untill the next first run.
Any ideas on how I can get rid of the error?
Private Sub btlCalculate_Click(sender As Object, e As EventArgs) Handles btlCalculate.Click
'https://codesnippets.fesslersoft.de/measure-execution-time-of-a-action/
'Dim time0 = Now()
Dim WB As Excel.Workbook = Globals.ThisAddIn.Application.ActiveWorkbook
Dim WS As Excel.Worksheet = Globals.ThisAddIn.Application.ActiveSheet
Dim AVG As Double, total As Double, I
Dim PT As Excel.PivotTable
Dim watch = Stopwatch.StartNew()
On Error Resume Next
If chkPivot.Checked Then
For Each PT In WS.PivotTables
PT.RefreshTable()
Next
Else
WS.Calculate()
End If
watch.Stop()
Dim intervalToMs = watch.Elapsed.TotalMilliseconds
With Me.DataGridView1
Dim rowI As String() = New String() { .RowCount, WS.Name, intervalToMs, TimeOfDay.ToString("HH:mm:ss")}
.Rows.Add(rowI)
For I = 0 To .Rows.Count - 2
total += CDbl(.Rows(I).Cells(2).Value)
Next
AVG = total / (.Rows.Count - 1)
End With
txtAvg.Text = AVG
On Error GoTo 0
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

