Errors while copy-pasting Excel data (text with hyperlink) to a PowerPoint Slide

Question

I am writing some code that takes data from an Excel table (25 rows, 15 columns) and creates a new PowerPoint presentation from that data. Per row of data, one individual PowerPoint slide is created, so it should end up with 25 slides.

For most of the data, I wrote a macro that creates a slide, adds a textbox, then fills the textbox with text. But some of the Excel data are hyperlinks and since those apparently don't work with Textboxes, I wrote a macro that simply copies the cells, then pastes it on the slide.

Here's the problem: Everything was fine until I programmed the macro for the hyperlinks (see code). Now I randomly get runtime-errors, saying that the pasting method was unsuccessful, sometimes at row 13, sometimes at row 20. The presentation never gets fully created. Is the code for the hyperlinks wrong somehow?

One more interesting thing: If I only copy the hyperlinks from one column (column 12), there is no error. If I then add code for column 13, which also contains similar hyperlink data, now the errors occur.

I hope I described the problem well, if not, please tell me which part to elaborate on. Many thanks.

Sub UpdatePowerPoint()

Dim PPT As New PowerPoint.Application
PPT.Visible = True

Dim strPath As String
strPath = ThisWorkbook.Path
PPT.Presentations.Open (strPath & "\" & "ppt-basis.pptx")

PPT.ActiveWindow.Activate

Dim PPTPRES As PowerPoint.Presentation
Dim PPTSLIDE As PowerPoint.slide
Dim ppName As PowerPoint.Shape

Set PPTPRES = PPT.ActivePresentation

Dim n As Integer
Dim NumRows As Integer
Application.ScreenUpdating = False
NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
Range("A2").Select

For n = 1 To NumRows

Set PPTSLIDE = PPTPRES.Slides.Add(n + 2, ppLayoutBlank)

'there is some more code here, but this code does not cause issues

'code for hyperlinks in column 12        
Cells(n + 1, 12).Copy
PPTSLIDE.Shapes.Paste
Set ppName = PPTSLIDE.Shapes(PPTSLIDE.Shapes.Count)
ppName.Left = 675
ppName.Top = 325
ppName.Width = 250
ppName.Height = 25

Application.CutCopyMode = False

'code for hyperlinks in column 13            
Cells(n + 1, 13).Copy
PPTSLIDE.Shapes.Paste
Set ppName = PPTSLIDE.Shapes(PPTSLIDE.Shapes.Count)

ppName.Left = 675
ppName.Top = 350
ppName.Width = 250
ppName.Height = 25

Application.CutCopyMode = False


Next

Application.ScreenUpdating = True     

End Sub

John Korchok · Accepted Answer · 2019-08-29 15:28:34Z

0

Pasting cells into PowerPoint does not retain their characteristics as cells: they are transformed into shapes and lose their hyperlinks. You can test this by trying to do the operation manually: pasted Excel hyperlinks still look like hyperlinks, but don't operate as such.

Instead, you would have to iterate through each cell in your Excel selection, get hyperlinks and their location and store that information in an array. Then in PowerPoint, loop through every pasted shape and reassign the hyperlink from the array depending on position. To get a hyperlink from Excel:

HLink$ = Sheet1.range("A1").Hyperlinks(1).Address

To add a hyperlink to a shape in PowerPoint:

With ActivePresentation.Slides(1).Shapes(1).TextFrame.TextRange.ActionSettings(ppMouseClick)
  .Action = ppActionHyperlink
  .Hyperlink.SubAddress = HLink$
End With

answered Aug 29, 2019 at 15:28

John Korchok

4,9782 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Errors while copy-pasting Excel data (text with hyperlink) to a PowerPoint Slide

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related