PP 2016 VBA PasteExcelTableSourceFormatting does not respect ;;; numberformat RRS feed

  • Question

  • I posted this in the uservoice site but as I don't expect anything to happen there, I'll post it here FYI for anyone else who might come across the same problem.

     The code below when run in Excel earlier than 2016 creates a table in the slide with B1 and B2 contents hidden. In 2016, B1 is hidden by white font but B2 which uses the ;;; numberformat is not hidden.

    Option Explicit

    Sub testPPpaste()
    Dim oPP As Object, oPres As Object, oSlide As Object
    ' Launch powerpoint, same version as Excel
    Set oPP = CreateObject("Powerpoint.Application." & Int(Excel.Application.Version))
    Set oPres = oPP.presentations.Add
    Set oSlide = oPres.slides.Add(1, 1)

    [A1:C4].Value = "test"
    [B1].Font.Color = vbWhite ' hidden by white font
    [B2].NumberFormat = ";;;" ' hidden by number format

    ' copy and paste this range to PP and see what is still hidden
    oPP.CommandBars.ExecuteMso "PasteExcelTableSourceFormatting"

    End Sub

    Thursday, May 4, 2017 11:13 AM