none
Cant write a macro for the life of me RRS feed

  • Question

  • Hi Folks,

    Have always recorded macros rather than writing them using VBA. However, changed job recently and new place is still using Office 2010 so I can no longer do this. Not something I now have to do regularly but could do with a hand for this one repetitive task. I have tried using snippets of other code to do this but keep breaking it.

    I need to transfer data from an Excel table into a table in PowerPoint. However the PPT table cant be an Excel Object. This part isn't an issue.

    Once the data is transferred I need to replace all line breaks with carriage returns, change them to bulleted text (using an en-dash as a bullet), with 0 indent and a space of 0.5cm from bullet to text.

    Lastly I need to format the table using banded rows (can amend the colours myself if I know where to put them in the code) along with potentially having a header row/column.

    Is there anyone out there can help me? Would save me an absolutely massive amount of time every week as I will have to do it manually multiple times every week otherwise.

    Thanks

    Peter

    Wednesday, April 26, 2017 10:58 AM

All replies

  • Hi Peter,
    You could iterate through characters in table cell and replace carriage returns character with an en-dash. I suggest you set row color via setting backcolor of cells in a row.
    Here is the example.

    'iterate through characters
    For i = 1 To TB.Rows.Count
    For Each cel In TB.Rows(i).Cells
      cel.Shape.Fill.BackColor.RGB = vbGreen
      Set txtRng = cel.Shape.TextFrame.TextRange
      If Len(txtRng.Text) > 0 Then
      txtRng.Text = Replace(txtRng.Text, Chr(13), " - ")
      End If
    Next cel
    Next i
    
    'Set Color
    For i = 1 To TB.Rows.Count
    For Each cel In TB.Rows(i).Cells
    cel.Shape.Fill.BackColor.RGB = vbGreen
    Next cel
    Next i
    

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 27, 2017 7:42 AM
    Moderator