Excel Crashes When Returning Large Dataset

Question

My team is creating a "Customer Dashboard" in Excel that displays metrics in the form of PivotTables on various tabs. We have created a couple of SQL Server connections to pull data that we need to populate the PivotTables. These connections use stored procs and the parameters for the stored procs are gathered from a few cells. The stored proc looks like this:

{CALL OPE.OPE.uspCSDashboard(?,?,?,?)}

The report has been running extremely well and our internal customer loves it. We just ran into an issue recently when they tried to pull a large system. The large system is about 56 columns by ~65,000 rows. The result is that Excel appears to crash or timeout. When I hit "run report" it acts like its working normally, then it greys out and says (not responding) at the top. Sometimes it will recover when pulling smaller datasets (56X28,000) but it doesn't seem to recover after waiting approximately five minutes.

When running the stored proc in SQL Server for the large system. It completed in about seven seconds.

Does anyone know why it takes so long? And what can I do to fix the crashing error when running for a large system? Below is all of the code:

Sub FilterPivotField(Field As PivotField, Value)
    Application.ScreenUpdating = False
    With Field
    On Error Resume Next
        If .Orientation = xlPageField Then
            .CurrentPage = Value
        ElseIf .Orientation = xlRowField Or .Orientation = xlColumnField Then
            Dim i As Long
            On Error Resume Next ' Needed to avoid getting errors when manipulating PivotItems that were deleted from the data source.
            ' Set first item to Visible to avoid getting no visible items while working
            .PivotItems(1).Visible = True
            For i = 2 To Field.PivotItems.Count
                If .PivotItems(i).Name = Value Then _
                    .PivotItems(i).Visible = True Else _
                    .PivotItems(i).Visible = False
            Next i
            If .PivotItems(1).Name = Value Then _
                .PivotItems(1).Visible = True Else _
                .PivotItems(1).Visible = False
        End If
    End With
    Application.ScreenUpdating = True
End Sub

Sub RunReport()

'Aliasing PivotTable Function

Dim pt As PivotTable

'Turn Screen Updates Off

Application.ScreenUpdating = False

'Unprotect Sheets
Worksheets("Hospital Dashboard").Unprotect ("escan")
Worksheets("Reports Summary").Unprotect ("escan")
Worksheets("Exclusion Report").Unprotect ("escan")
Worksheets("Billing Deadline Report").Unprotect ("escan")

'Unhide Certain Tabs

Sheets("DetailData").Visible = True
Sheets("HiddenPivotTables").Visible = True

'Refresh Tables

Application.Goto reference:="Table_Query_from_CustomerDashboard"
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Application.Goto reference:="Table_Query_from_CustomerDashboard_1"
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

'Refresh PT Tables

For Each Worksheet In ThisWorkbook.Worksheets
    For Each pt In Worksheet.PivotTables
        pt.PivotCache.Refresh
Next pt
Next

'call filter

FilterPivotField Worksheets("Hospital Dashboard").PivotTables("PivotTable7").PivotFields("IsCoded"), "0"
FilterPivotField Worksheets("Hospital Dashboard").PivotTables("PivotTable7").PivotFields("IsInvoiced"), "0"

FilterPivotField Worksheets("Hospital Dashboard").PivotTables("PivotTable8").PivotFields("IsCoded"), "0"
FilterPivotField Worksheets("Hospital Dashboard").PivotTables("PivotTable8").PivotFields("IsInvoiced"), "0"

FilterPivotField Worksheets("Billing Deadline Report").PivotTables("PivotTable1").PivotFields("IsCoded"), "0"
FilterPivotField Worksheets("Billing Deadline Report").PivotTables("PivotTable1").PivotFields("IsExcluded"), "0"

FilterPivotField Worksheets("HiddenPivotTables").PivotTables("PivotTable5").PivotFields("IsCoded"), "0"
FilterPivotField Worksheets("HiddenPivotTables").PivotTables("PivotTable5").PivotFields("IsExcluded"), "0"


'Hide Certain Tabs

Sheets("DetailData").Visible = False
Sheets("HiddenPivotTables").Visible = False

'Protect Sheets
Worksheets("Hospital Dashboard").Protect "escan", _
            DrawingObjects:=False, _
            Contents:=True, _
            Scenarios:=False, _
            userInterfaceOnly:=False, _
            AllowFormattingCells:=False, _
            AllowFormattingColumns:=False, _
            AllowFormattingRows:=False, _
            AllowInsertingColumns:=False, _
            AllowInsertingRows:=False, _
            AllowInsertingHyperlinks:=False, _
            AllowDeletingColumns:=False, _
            AllowDeletingRows:=False, _
            AllowSorting:=False, _
            AllowFiltering:=False, _
            AllowUsingPivotTables:=True
Worksheets("Reports Summary").Protect "escan", _
            DrawingObjects:=False, _
            Contents:=True, _
            Scenarios:=False, _
            userInterfaceOnly:=False, _
            AllowFormattingCells:=False, _
            AllowFormattingColumns:=False, _
            AllowFormattingRows:=False, _
            AllowInsertingColumns:=False, _
            AllowInsertingRows:=False, _
            AllowInsertingHyperlinks:=False, _
            AllowDeletingColumns:=False, _
            AllowDeletingRows:=False, _
            AllowSorting:=False, _
            AllowFiltering:=False, _
            AllowUsingPivotTables:=True
Worksheets("Exclusion Report").Protect "escan", _
            DrawingObjects:=False, _
            Contents:=True, _
            Scenarios:=False, _
            userInterfaceOnly:=False, _
            AllowFormattingCells:=False, _
            AllowFormattingColumns:=False, _
            AllowFormattingRows:=False, _
            AllowInsertingColumns:=False, _
            AllowInsertingRows:=False, _
            AllowInsertingHyperlinks:=False, _
            AllowDeletingColumns:=False, _
            AllowDeletingRows:=False, _
            AllowSorting:=False, _
            AllowFiltering:=False, _
            AllowUsingPivotTables:=True
Worksheets("Billing Deadline Report").Protect "escan", _
            DrawingObjects:=False, _
            Contents:=True, _
            Scenarios:=False, _
            userInterfaceOnly:=False, _
            AllowFormattingCells:=False, _
            AllowFormattingColumns:=False, _
            AllowFormattingRows:=False, _
            AllowInsertingColumns:=False, _
            AllowInsertingRows:=False, _
            AllowInsertingHyperlinks:=False, _
            AllowDeletingColumns:=False, _
            AllowDeletingRows:=False, _
            AllowSorting:=False, _
            AllowFiltering:=False, _
            AllowUsingPivotTables:=True

'Unhide Detail Data

Worksheets("DetailData").Activate
Rows("2:500000").Hidden = False

'Getting back to home sheet

Worksheets("Home").Select

'Setting data last update to value

Worksheets("home").Range("c6").Value = "=OFFSET(DetailData!aq8,0,0)"

'Message Box to let the CSR know data has been refreshed

Dim Done As String

Done = "Data is finished updating!"

MsgBox (Done)

End Sub

try putting some `Debug.Print` statements in there, or single-stepping through the code so you can narrow down the specific step where it's hanging. — aucuparia, Jun 02 '15 at 16:41
@aucuparia I stepped through my code and it took eight minutes and thirteen seconds to update for 32,000 rows. I did the same exercise again and it never got done updating. The exact line it gets held up on is: 'Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False'. Do you know why it's taking so long here? Any ideas what I can look into next? Thanks! — tlinton, Jun 02 '15 at 17:59
JMichael may well have it right that this is just data volume. I'd look to see if you really need all the detail you're pulling back - see if you can consolidate or filter on the server so less comes back. — aucuparia, Jun 03 '15 at 07:04
Have you checked [the Excel data model spec](https://support.office.com/en-us/article/Data-Model-specification-and-limits-19aa79f8-e6e8-45a8-9be2-b58778fd68ef) to see if you are over any of the limits set out there? — aucuparia, Jun 03 '15 at 07:20
@acuparia I discovered my problem after working with our DBAs. There was no evidence of parameter sniffing which is good. This could have been a size issue as we found the solution to be this: I was originally connecting Excel to SQL Server with the "SQL Server" driver, we changed the driver to "SQL Server Native Client 11.0" and now the data returns as fast as the query runs in SSMS. Looks like there is a large difference between the two types of drivers here. Thanks for all of the input! — tlinton, Jun 03 '15 at 13:28

score 0 · Accepted Answer · answered Jun 02 '15 at 20:02

This is not definite, but the issue is probably just the volume of data you're messing with. I can pretty regularly crash Excel with that amount of data, and if you're importing data with each run, my money is that you have hundreds of thousands of rows of data that have already been imported and massaged as needed.

Based off what's present in your code you can probably turn off calculations Application.Calculation = xlManual to help speed things up. You can, and should, also use the tables' names as opposed to selecting them (as a general rule, you should never need Selection. in your code). If you don't know what they pivot tables are named either record a macro while you refresh the tables to get the name, or click on one and look in the little window just above column A that normally shows which cell you have selected.

Those should help, but they may not fix the issue depending on the total amount of data (as well as the amount of charting, formatting, etc.) in the workbook.

If that's the case you may need to split the workbook up so that there's less stuff in any one (for example only have one pivot table in each, as well as any data & formulas that feed that pivot table).

[How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — aucuparia, Jun 03 '15 at 06:57
@jmichael Thanks for the input. I will take your suggestions and edit my code to be more correct. I really appreciate all the input! — tlinton, Jun 03 '15 at 13:31

Excel Crashes When Returning Large Dataset

1 Answers1