All geek questions in one place

VBA Using Sendkey to Launch Excel Shortcuts

I know sendkeys are considered bad and dangerous, but I'm struggling to figure out how best to solve problems in VBA compared to the Excel interface.

I wrote a personal macro to configure an unknown pivot table to repeat all the labels, set to a table, sort the list of ascending fields, and finally hide subtotals. Everything works fine, except that subtotals require a loop, and when there is a lot of data, this loop can take a lot of time. Oddly enough, if you just turned off subtotals, then from the controls on the front panel it is instant. Consequently, using Sendkey will be faster than actually looping. (Sendkey does a hotkey to execute the "Disable Project")

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 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

, . . , - , sendkeys "JYTD", - . , .

? , ?

+4
1

, ! 2007 , :

    Application.CommandBars.ExecuteMso "PivotTableSubtotalsDoNotShow"
+2

Source: https://habr.com/ru/post/1628162/

More articles:


All Articles