0

Some of my cells are formatted as #,##0;[Red](#,##0) which makes negative numbers show up in red. I want to copy the contents and formatting into PowerPoint. How can I identify the colour that a cell is displayed in, so that I can copy the visible appearance? I don't want to have to re-implement the logic of parsing the format and doing the negative decision if I don't have to, as not all my cells will be formatted that way.

If I can't do it by explicitly setting the colours in VBA, is it possible to use copy and paste methods to copy a cell from Excel into a cell in a table in PowerPoint? Actually this would be better than doing it longhand - I have tried but I can't work out at what object nesting level to do the paste.

I've found loads of references on how to paste Excel ranges in as a new table, but none on pasting individual cells with formatting into an existing PowerPoint table. Pasting at the TextRange level does not paste the colours, but it does paste other settings such as bold.

PhilHibbs
  • 859
  • 1
  • 13
  • 30
  • Cell(x,y).Font gives you a reference to the font properties of the text in Cell(x,y). That should get you pointed in the right direction. – Steve Rindsberg Jul 18 '14 at 14:44

1 Answers1

1

is it possible to use copy and paste methods to copy a cell from Excel into a cell in a table in PowerPoint?

I don't think Steve's suggestion works for Custom Number Formats like you have done. No matter what, when I query that cell's .Font.ColorIndex property (for example), the return value is 1, even if the number is negative and it appears red, which should yield 3.

Without parsing out the format conditions, I'm not sure this is possible. The number format rules seem not to transfer to PowerPoint, no matter what I have tried.

However, this may be an acceptable solution. Change your number format to:

#,##0;(#,##0)

Then instead of using custom format to do the color, apply a conditional format rule to make the cell's text red if the value is negative.

Example given for a single cell range:

Sub Test()
Dim r As Range
Dim ppt As Object
Dim pres As Object
Dim sld As Object
Dim tbl As Object
Dim tblCell As Object

Set r = [A3]

Set ppt = CreateObject("PowerPoint.Application")
Set pres = ppt.Presentations.Open("c:\presentation.pptx")
Set sld = pres.Slides(1)
Set tbl = sld.Shapes(1) 'Table in PowerPoint
Set tblCell = tbl.Table.Cell(1, 1).Shape 'Cell/shape you paste in to

r.Copy

tblCell.Select

ppt.CommandBars.ExecuteMso "PasteSourceFormatting"

End Sub

Alternatively, force the cell's color to red:

Set r = [A3]
If r.Value < 0 Then 
    r.Font.ColorIndex = 3
Else 
    r.Font.ColorIndex = 1
End If
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • This works. I removed the [Red] and used conditional formatting instead, and VBA to copy and paste. – PhilHibbs Jul 24 '14 at 10:35
  • Great to hear that @PhilHibbs. If this has solved your problem do consider marking the answer as "Accepted". This way the answer may help others with similar problems in the future! – David Zemens Jul 24 '14 at 13:20
  • @DavidZemens - ironic considering you offer bounties and then don't comment or award?? –  Jul 24 '14 at 14:40
  • @cirrusone [this](http://stackoverflow.com/questions/22787956/how-to-expose-a-c-sharp-class-to-a-vba-module-in-a-document-level-add-in)? I offered a bounty on that because it was an interesting question, but one that is beyond my ability to evaluate. Had the OP "accepted" your answer, I would've awarded the full bounty. But since he did not, you got the 50%. Like I said, I simply am not able to verify that your answer is correct, so you got a good +25 for contributing. Please don't be butthurt about that. – David Zemens Jul 24 '14 at 14:54
  • @cirrusone the only other bounty I have offered [here](http://stackoverflow.com/questions/16348189/vba-to-prevent-keyboard-input-while-a-package-object-xml-is-read-into-adodb-st) was awarded +100, and you will observe that I was engaged in the comments with *all* of the answerers. – David Zemens Jul 24 '14 at 14:57