VBA Using Sendkey to execute Excel Hotkeys

Question

I know that sendkeys are considered bad and dangerous but I'm struggling to figure out how to best deal with issues in VBA compared to front end Excel.

I wrote a personal macro for setting up an unknown pivot table to repeat all labels, set to tabular, sort ascending field list, and finally hide subtotals. Everything works fine except the subtotals requires a loop, and when there's a large amount of data this loop can take a long time. Oddly, if you were to just turn off subtotals the from the front end controls, it's instant. Hence where using Sendkey would be faster than actually looping. (Sendkey is doing the Hotkey presses to execute Turn off Subtotals)

Sub formatpivotTable()
Dim pivotName As Variant
Dim pf As pivotField

On Error Resume Next
pivotName = ActiveCell.PivotTable.Name
If pivotName = "" Then
    MsgBox "You did not select a pivot table"
    Exit Sub
End If
ActiveSheet.PivotTables("" & pivotName & "").ManualUpdate = True
With ActiveSheet.PivotTables("" & pivotName & "")
    .RepeatAllLabels (xlRepeatLabels)
    .RowAxisLayout (xlTabularRow)
    .FieldListSortAscending = True
    'For Each pf In .PivotFields
    '    pf.Subtotals(1) = True
    '    pf.Subtotals(1) = False
    'Next
End With
ActiveSheet.PivotTables("" & pivotName & "").ManualUpdate = False

'Remove the Loop and instead use the Front End Hotkey
ActiveSheet.Activate  'Normally using activate is bad, but maybe it's good here to ensure your sendkeys hit excel? not even sure this prevents it
Application.SendKeys "%", True
Application.SendKeys "{J}", True
Application.SendKeys "{Y}", True
Application.SendKeys "{T}", True
Application.SendKeys "{D}", True
End Sub

When it works, it works beautifully. The whole thing is done within less than a second. But I feel like there's still a danger that your sendkeys could overwrite pivot information with "JYTD" if something goes wrong. I have commented out my original loop, which just takes too long when dealing with large amounts of data.

Any thoughts? Am I just impatient using the loop method?

Rory Rory · Accepted Answer · 2016-02-10T16:38:53

No, you're not impatient! If you are using 2007 or later, use:

    Application.CommandBars.ExecuteMso "PivotTableSubtotalsDoNotShow"

VBA Using Sendkey to execute Excel Hotkeys

1 Answers